Finding specific cases in one query

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

Finding specific cases in one query

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2012-03-05 : 11:15:19

I want to be able to search for over 300, over 350 and over 400
( Datediff(DAY, filedate, Getdate()) > 300 )
( Datediff(DAY, filedate, Getdate()) > 350 )
( Datediff(DAY, filedate, Getdate()) > 400 )

in the query below. How do I add that to this query? Do I need to declare a new variable?





if @doc in ('V01')
Begin 
Select DOC, 
       Reg,
		FO,
	  CLMS,
       
	Age = Datediff(day,max(filedate), getdate()),
FileDate = max(FileDate),
o.mailingaddressstate as DDS,
o.ddsofficecode as DDSCode,
Min(ddsrcpt) AS DDSReceipt,
	DDSAge = Datediff(day,min(ddsrcpt), getdate()),
	Title = case when min(p.Title) <> max(p.Title) then 'Concurr' else min(p.Title) end
From 
(select *, row_number() over (Partition by ddsrcpt order by ddsrcpt) as rownum 
from pendingdds) p
join offices.dbo.doorsinfo o
on o.officecode = p.fo
where doc in ('V01', 'S67', 'S41', 'S66') 
Group By FO, Reg, DOC, CLMSSN, o.mailingaddressstate, o.ddsofficecode
order by p.fo, age , DDSReceipt desc
End

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-07 : 20:30:23

sounds like what you need a case statement



CASE WHEN Datediff(DAY, filedate, Getdate()) > 300  THEN ...
 WHEN Datediff(DAY, filedate, Getdate()) > 350   THEN ...
 WHEN Datediff(DAY, filedate, Getdate()) > 400  THEN ...
 ELSE ...
END AS [ColumnName]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources