-4

I am trying to create a script where the on Mondays it pulls back information for Friday, Saturday and Sunday. For Tuesday - Friday, the script will pull back the previous day. Example Tuesday pulls Monday, Wednesday pulls Tuesday, ect.

However, I am having I am having a bit of trouble writing the case. 

DECLARE @start DATETIME 
DECLARE @end DATETIME

SET @start='2016-05-30'
SET @end=dateadd(dd,1,@start)

Where
orderdate.CreatedDt BETWEEN @start and @end

is what I have.

I have found other questions similar, but I do not know how to apply the information. ex: SQL WHERE depending on day of week

isabella
  • 151
  • 4
  • 15
  • What is the goal of that `where` clause? It's not attached to anything. – Aaron Jun 28 '18 at 17:26
  • Without knowing exactly what you're asking, my best guess is to look into sql's `DATEPART()` function - it can give you a day of the week – Aaron Jun 28 '18 at 17:31
  • Do you want to update a table or what do you want to achieve? – Daniel Bürckner Jun 28 '18 at 18:12
  • This is what happen if you don't know how to ask question. You end up with more questions instead of answer. Read this. https://stackoverflow.com/help/how-to-ask – Eric Jun 28 '18 at 18:22
  • I know how to ask questions. I left out all the information in the where because the only line needed was the createdt. that is all I am filtering on. If you are not trying to help then there is no reason to comment. – isabella Jun 28 '18 at 23:49

1 Answers1

1

You can do this logic based on the day of the week of the current date:

SELECT * 
FROM yourtable
WHERE
    yourtable.date BETWEEN
        CASE WHEN datepart(dw, getDate) = 2 THEN GetDate() - 3 ELSE GetDate() - 1 END
        AND
        GETDATE() - 1
JNevill
  • 46,980
  • 4
  • 38
  • 63