Help with Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help with Query

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-08-22 : 14:39:54

How would I write a query to say if COS are the same then make that title called concurr but showing the max filedate of the Cos.

This is what I want I'm trying to achieve:


Doc   cos     filedate     Title
s23  088652    2011-04-28   Concurr
s23  001257    2011-05-28   Concurr
s23  054896    2011-01-02     T2
s23  023689    2011-04-08    T16
s23  001256    2011-06-28     T2

Here's the tabel info if you run it you will see the duplicates COS of the two concurr.


CREATE TABLE [dbo].[T2dibtest2](
[DOC] [varchar](3) NULL,
[COS] [varchar](6) NULL,
[Filedate] [datetime] NULL,
[Title] [varchar](3) NULL)
 
Insert into T2dibtest2
Select 's23', '088652',  '2011-04-28', 'T2' union all
Select 's23', '088652',  '2010-06-28', 'T16' union all
Select 's23', '001257',  '2011-05-28', 'T2' union all
Select 's23', '001257',  '2010-02-12', 'T16' union all
Select 's23', '054896',  '2011-01-02', 'T2' union all
Select 's23', '023689',  '2011-04-08', 'T16' union all
Select 's23', '001256',  '2011-06-28', 'T2'

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-08-22 : 15:01:12

[code]
Select
DOC,
COS,
max(FileDate),
Title = case when min(Filedate) <> max(Filedate) then 'Concurr' else min(Title) end
From dbo.T2dibtest2
Group By DOC, COS
[/code]

EDIT: Of course... if you can't trust the fileDate on the 'duplicated COS' rows to differ, then you will have to do something else. Will the Title always differ for 'duplicated' COS rows?

Corey

I Has Returned!!

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-08-22 : 15:22:32

Thanks this is perfect.

Could you please explain why you did this:

Title = case when min(Filedate) <> max(Filedate) then 'Concurr' else min(Title) end

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-08-22 : 15:37:48

I'm trying to get the age of the and added this:


Select 
	DOC, 
	COS,
	max(FileDate) as Filedate,Datediff(day,filedate, getdate()) as age,
	Title = case when min(Filedate) <> max(Filedate) then 'Concurr' else min(Title) end
From dbo.T2dibtest2
Group By DOC, COS, filedate

It's not giving me the concurr I'm getting all records. What am I doing wrong?

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-08-22 : 15:58:05

One last thing yes the title will diff if there are dup Cos'

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-08-22 : 16:14:52

quote:

Could you please explain why you did this:

Title = case when min(Filedate) <> max(Filedate) then 'Concurr' else min(Title) end

basically, if the min() and max() values are different, it implies that there is at least more than 1 row involved... so we know to use 'Concurr'.
Otherwise, if the min() and max() values are equal, then there is either exactly 1 row (or more than 1 row all with the same value). So we use min(Title) as it would be the same as Title.

If you know Title will always be different if there are dups, then I would use the below query instead of the first.

Also, your 'age' wasn't working because you needed to include an aggregate function on 'filedate'...


Declare @T2dibtest2 TABLE (
[DOC] [varchar](3) NULL,
[COS] [varchar](6) NULL,
[Filedate] [datetime] NULL,
[Title] [varchar](3) NULL)
 
Insert into @T2dibtest2
Select 's23', '088652',  '2011-04-28', 'T2' union all
Select 's23', '088652',  '2010-06-28', 'T16' union all
Select 's23', '001257',  '2011-05-28', 'T2' union all
Select 's23', '001257',  '2010-02-12', 'T16' union all
Select 's23', '054896',  '2011-01-02', 'T2' union all
Select 's23', '023689',  '2011-04-08', 'T16' union all
Select 's23', '001256',  '2011-06-28', 'T2'

Select 
	DOC, 
	COS,
	FileDate = max(FileDate),
	Age = Datediff(day,max(filedate), getdate()),
	Title = case when min(Title) <> max(Title) then 'Concurr' else min(Title) end
From @T2dibtest2
Group By DOC, COS

Corey

I Has Returned!!

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-08-22 : 16:29:58

Great thanks so much!!! That worked!

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-08-22 : 21:56:03

Thanks for explaining all so I can use this elsewhere!

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-08-22 : 22:33:13

one last question...

Title = case when min(Title) <> max(Title) then 'Concurr' else min(Title) end

I don't understand how you are getting the duplicates if you are using Title? How are you matching the COS' up?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-23 : 00:04:24

that statement looks for min amd max values of date for each of DOC, COS groups which will be different only if there's more than 1 record for the group. if there's multiple, its returning the string Concurr else its returning the only one Titale present

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-08-23 : 10:24:19

Thanks for explaining that!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-23 : 11:14:11

np

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources