CODEWITHSUNDEEP
sqlsql-servert-sql
JuniorDev
JuniorDev

Reputation: 453

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.

Upvotes: 0

Views: 77

Answers (3)

Andrey Korneyev
Andrey Korneyev

Reputation: 26846

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')

Upvotes: 2

JohnHC
JohnHC

Reputation: 11195

Just use an or (my need adjusting for your day field)

where [Userid] IN ( 'dzuza', 'tmol', 'jmichal')
and 
  (
      (
      datename(dw,getdate()) in ('Tuesday','Wednesday','Thursday','Friday')
      and [Order Entry Date] = dateadd(d,-1,getdate())
      )
  or 
      (
      datename(dw,getdate()) = 'Monday'
      and [Order Entry Date] = dateadd(d,-3,getdate())
      )
  )

Upvotes: 1

encryptoferia
encryptoferia

Reputation: 247

Try this for the CASE WHEN

...
CASE WHEN datename(dw, getdate()) = 'Tuesday' OR
          datename(dw, getdate()) = 'Wednesday' OR
          datename(dw, getdate()) = 'Thursday' OR
          datename(dw, getdate()) = 'Friday' THEN ...

Upvotes: 0

Related Questions