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 - 2012-01-17 : 09:17:13

I have this query which works:



Select p.DOC,
       p.Fo, 
       p.CLM,
		
Age = Datediff(day,max(p.filedate), getdate()),
	FileDate = max(FileDate),
Min(ddsrpt) AS DDSReceipt,
	DDSAge = Datediff(day,min(p.ddsrpt), getdate()),
	Title = case when min(p.Title) <> max(p.Title) then 'Concurr' else min(p.Title) end
From testtable p
where p.doc ='s09'
Group By  p.doc, p.fo,p.DOC, p.CLM
order by age desc

This produces this table


Doc     FO      CLM     Age     FileDate   DDSReceipt  DDSAge   Title
S09	267	052582	277	4/15/2011 4/15/2011     277   Concurr
S09	267	084813	259	5/3/2011   5/3/2011     259	T16
S09	029	116632	257	5/5/2011  5/5/2011      257  T2
S09	268	116632	257	5/5/2011  5/5/2011      257  T16
S09	C80	028703	251	5/11/2011 5/11/2011     251	T16

How do I set it up so Concurr can show for clm 116632 As you see both T2 and T16 are showing up but it should say concurr as well.

The FO numbers are different so how could I make it come out this way?


Doc     FO      CLM     Age     FileDate   DDSReceipt  DDSAge   Title
S09	267	052582	277	4/15/2011 4/15/2011     277   Concurr
S09	267	084813	259	5/3/2011   5/3/2011     259	T16
S09	029	116632	257	5/5/2011  5/5/2011      257  Concurr
S09	C80	028703	251	5/11/2011 5/11/2011     251	T16

I would need to add someting that if min(title) <> max(title) then Concurr and if min(title) <> max(title) and FO <> FO then select one record and make it concurr. Is this possible?

JJ297
Aged Yak Warrior

940 Posts

Posted - 2012-01-17 : 09:26:53

Sorry here's the table info:



CREATE TABLE [dbo].[testtable](
	[doc] [varchar](3) NULL,
	[fo] [varchar](3) NULL,
	[clm] [char] (6) NULL,
	[cos] [char](6) NULL,
	[Filedate] [char](30) NULL,
	[ddsrpt] [char](30) NULL,
	[title] [varchar] (3) NULL,
	) ON [PRIMARY]

insert into testtable
select 'S09', 'C80', '028703', '028703', '5/11/2011', '5/11/2011', 'T16' union all
select 'S09', '267', '052582', '052582', '4/15/2011', '4/15/2011', 'T16' union all
select 'S09', '267', '052582', '052582', '4/15/2011',  '4/15/2011', 'T2' union all
select 'S09', '267', '084813', '084813', '5/3/2011', '5/3/2011', 'T16' union all
select 'S09', '029', '116632', '116632', '5/5/2011',  '5/5/2011', 'T2' union all
select 'S09', '268', '116632', '116632', '5/5/2011', '5/5/2011', 'T16'