JuniorDev JuniorDev - 1 month ago 13
SQL Question

SELECT statement based on a day value SQL SERVER

i am trying to create a view by selecting some columns from a different table based on a previous date in one column. This is working fine, here is my code

ALTER VIEW [Orders_By_User]
AS
(


SELECT
[Userid]
,[Region]
,[Country]
,[Order Number]
,[Order Entry Date]
,[Customer Name]

FROM BACKLOG
WHERE ([Order Entry Date] >= dateadd(day,datediff(day,1,GETDATE()),0)
AND [Order Entry Date] < dateadd(day,datediff(day,0,GETDATE()),0))
AND [Userid] IN ( 'dzuza', 'tmol', 'jmichal')

GROUP BY
[Userid]
,[Region]
,[Country]
,[Order Number]
,[Order Entry Date]
,[Customer Name]
)


I want to add a condition in the same view that if the day is Monday so It selects the data from Friday not the previous day which is Sunday. I was trying to do it this way

SELECT
CASE
WHEN datename(dw, getdate()) in ('Tuesday','Wednesday','Thursday','Friday') THEN ...
ELSE ...


But i didn't know how to write it correctly. It shows me error when i use SELECT CASE then another SELECT.

Any suggestions please ? Thank you very much.

Answer

You can include that logic concerning week days into your where condition, and it can be significantly simplified:

WHERE     
    [Order Entry Date] >= dateadd(dd, 
                                  case 
                                       when datename(dw, getdate()) = 'Monday' then -3 
                                       else -1  
                                   end, 
                                   cast(getdate() as date))
    AND [Order Entry Date] < cast(getdate() as date)
    AND [Userid] IN ( 'dzuza', 'tmol', 'jmichal')
Comments