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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy