CASE statement that will mark data '1 week before'

Question

Hello I need a CASE statement that will be marking data that was one week before. CASE statement that will mark data '1 week before'. it should basically mark rows where 'STARTDATE' is equals to one week before. If today (1/13/2021) it should look at the data '1/2/2021' and return 'Data one week before'. This column has only Sunday dates namely 1/02, 1/09 etc. I need this column to return value 'Data one week before' if it equals to 1/02 for example today. This query should be dynamic and readjust itself each week.

enter image description here

SELECT [DMDUNIT]
  ,[LOC]
  ,[MODEL]
  ,[JOBID]
  ,[USERID]
  ,[FCSTDATE]
  ,[STARTDATE] FROM [BYIntegration].[SCPOMGR].[FCSTPERFSTATIC]

Please let me know if someone knows how to do it.

@Christophe I cannot make it dynamic that's why I ask. Obviously CASE WHEN [STARTDATE] = '1-02-2022' THEN '1 week before' ELSE 'Not relevant' END AS 'Date from prior week' but this is not right because I need that '1-02-2022' to change every week. — Stephansko, Jan 14 '22 at 06:51
See if you can work it out from the answer on this question. https://stackoverflow.com/questions/42635297/sql-query-where-date-today-minus-7-days — Nick.Mc, Jan 14 '22 at 06:53
@Nick.McDermaid Thank you, i know about DATEADD(WEEK,-1,GETDATE()) but I need to have a dynamic calculation in a sense that today we have Thursday, 1/13 but it needs to return not - 7 days but -11 days. At the same time, tomorrow it should return - 12 days. Next Sunday, 1/16/2021 it will be - 7 days. I need a dynamic calculation that will keep in mind that it needs to return Sunday of the prior week. I am not sure there is smth like that in that post — Stephansko, Jan 14 '22 at 07:11
You're right that post does not have that logic. I suggest you post some clear sample data in a table rather than explaining in words. There are solutions to this on stackoverflow, which boil down to: work out what todays weekday is, and add that that the the offset days — Nick.Mc, Jan 14 '22 at 07:15
Like this https://stackoverflow.com/questions/12422248/previous-monday-previous-sundays-date-based-on-todays-date — Nick.Mc, Jan 14 '22 at 07:16
My google search was "t-sql find prior sunday" and that was the first thing that came up — Nick.Mc, Jan 14 '22 at 07:16

Deep in the Code · Answer 1 · 2022-01-14T18:43:44.753

If I understand what you're asking, I think something like this would work for you.

DECLARE @BeginningOfWeek VARCHAR(25) = 'Sunday'
DECLARE @Today DATE = GETDATE()
DECLARE @DaysToBeginningOfThisWeek INT = 0
DECLARE @BeginningOfInterval INT

WHILE @BeginningOfWeek <> DATENAME(WEEKDAY,DATEADD(DAY,-@DaysToBeginningOfThisWeek,@Today))
BEGIN
    SET @DaysToBeginningOfThisWeek = @DaysToBeginningOfThisWeek + 1
END

SET @BeginningOfInterval = 7 + @DaysToBeginningOfThisWeek

SELECT [DMDUNIT]
,[LOC]
,[MODEL]
,[JOBID]
,[USERID]
,[FCSTDATE]
,[STARTDATE]
,CASE WHEN DATEADD(DAY,0,DATEDIFF(DAY,0,[STARTDATE])) 
<= DATEADD(DAY,-7,DATEDIFF(DAY,0,@Today)) 
AND 
DATEADD(DAY,0,DATEDIFF(DAY,0,[STARTDATE])) 
> DATEADD(DAY,-@BeginningOfInterval,DATEDIFF(DAY,0,@Today))
THEN 'One week before'
ELSE NULL END AS WeekBefore
FROM [BYIntegration].[SCPOMGR].[FCSTPERFSTATIC]

This assumes that STARTDATE includes a time. If not, the DATEDIFF part of the expression can be eliminated. That is, instead of "DATEADD(DAY,-7,DATEDIFF(DAY,0,[STARTDATE]))", you would have "DATEADD(DAY,-7,[STARTDATE])". Like this:

SELECT [DMDUNIT]
,[LOC]
,[MODEL]
,[JOBID]
,[USERID]
,[FCSTDATE]
,[STARTDATE]
,CASE WHEN [STARTDATE]
<= DATEADD(DAY,-7,@Today) 
AND 
[STARTDATE] 
> DATEADD(DAY,-@BeginningOfInterval,@Today)
THEN 'One week before'
ELSE NULL END AS WeekBefore
FROM [BYIntegration].[SCPOMGR].[FCSTPERFSTATIC]

CASE statement that will mark data '1 week before'

1 Answers1