January 22, 2009 at 10:06 am
Is there any valid way to write a CASE statement like this?
Select distinct d.Field1, Field2,
count (CASE when Field3 = 1 and (DateField between select getdate() -2 and select getdate() - 0) Then Field3 END) As [Total]
This isn't the entire query but if I can find a way to put a dynamic date range in CASE for this then I can apply it to others as needed.
Thanks
January 22, 2009 at 10:14 am
is this what you looking for :
Select distinct d.Field1, Field2,
count (CASE
when Field3 = 1 and (DateField between DATEADD(dd,-1getdate()) and getdate())
Then Field3
END) As [Total]
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
January 22, 2009 at 11:35 am
Chris thanks for the fast reply. I get a syntax error near 'getdate'.
I thought maybe it was due to a space needed between -1 and the first getdate but that wasn't it...
Select distinct d.Field1, Field2,
count (CASE when Field3 = 1 and (DateField between DATEADD(dd,-1 getdate()) and getdate()) Then Field3 END) As [Total]
Any suggestion?
January 22, 2009 at 11:54 am
you need a comma where you put that space, i.e.
DATEADD(dd,-1 , getdate())
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 22, 2009 at 12:02 pm
Much thanks to you both and Matt your tag line is one of my favorite sayings!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply