CrazyCoder CrazyCoder - 9 months ago 46
SQL Question

case statement depending on day of week within where clause

I am trying to write a case statement within my where clause. Below is my query. I want to execute the query based on the weekday. Basically if its monday it needs to be datecreated = getdate()-3
if its tuesday it needs to be a range datecreated between getdate()-3 and getdate()-1
if its wed-friday then it needs to be datecreated = getdate()-1

SELECT Count(dispute_reference_no)AS reg_inc
FROM mytable
WHERE reasoncd IN ('b2', 'b3')
AND datecreated = (
WHEN Datename(weekday,Getdate()) = 'Monday' THEN dbo.Fn_getdateonly(Getdate()-3))
WHEN datename(weekday,getdate()) = 'tuesday' THEN (dbo.fn_getdateonly(getdate()-3)
AND dbo.fn_getdateonly(getdate()))
ELSE dbo.fn_getdateonly(getdate()-1)

the above code is not working. It wont let me add a range within case statement.


You can't dynamically rewrite your where clause like this, but you could get the desired behavior by using a series of logical operators:

SELECT COUNT(Dispute_Reference_no) AS reg_inc
FROM   mytable 
WHERE  reasoncd in ('b2', 'b3') AND
       ((DATENAME(weekday, GETDATE()) = 'monday' AND 
         datecreated = dbo.fn_getdateonly(GETDATE()-3)) OR
        (DATENAME(weekday, GETDATE()) = 'tuesday' AND 
         datecreated BETWEEN dbo.fn_getdateonly(GETDATE()-3) AND 
                             dbo.fn_getdateonly(GETDATE())) OR
        (atecreated = dbo.fn_getdateonly(GETDATE()-1)