CODEWITHSUNDEEP
sqlt-sqlcasewhere-clause
aLearningLady
aLearningLady

Reputation: 2088

Case Statement in Where Clause for datediff

I'm trying to have a report only show records where the INVOICE date is the previous day unless today is Monday, in which case it would pull if the INVOICE date is Friday.

As the title implies my attempt at accomplishing this is a case statement in my where clause. 

WHERE
(case when datepart(dw,GETDATE()) <> 2 
then (INVOICE >= dateadd(day,datediff(day,1,GETDATE()),0)) 
else INVOICE >= dateadd(day,datediff(day,3,GETDATE()),0) end)
and (case when datepart(dw,GETDATE()) <> 2 
then (INVOICE < dateadd(day,datediff(day,0,GETDATE()),0)) 
else INVOICE <dateadd(day,datediff(day,2,GETDATE()),0) end)

As you can likely tell from the above code, I'm a complete noob when it comes to SQL queries and it's riddled with syntax errors. What needs to be corrected above to get this to work? Or am I looking at this entirely wrong?

Upvotes: 0

Views: 681

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270573

How about something simpler:

where (datepart(dw, GETDATE()) <> 2 and
       cast(invoice as date) = cast(dateadd(day, -1, getdate()) as date)
      ) or
      (datepart(dw, GETDATE()) = 2 and
       cast(invoice as date) = cast(dateadd(day, -3, getdate()) as date)
      )

In general, functions in the where clause prevent the use of indexes. One exception (the only exception?) is casting datetime values to dates. Here is a blog on the subject.

Upvotes: 2

Related Questions