CASE statement that will mark data \'1 week before\'

Question

CASE statement that will mark data \'1 week before\'

JeremyU

SSChasing Mays

Points: 640
More actions
January 14, 2022 at 5:50 am

#3973013
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.
```
SELECT [DMDUNIT]
  ,[LOC]
  ,[MODEL]
  ,[JOBID]
  ,[USERID]
  ,[FCSTDATE]
  ,[STARTDATE] FROM [BYIntegration].[SCPOMGR].[FCSTPERFSTATIC]
```
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.
- This topic was modified 2 years, 11 months ago by JeremyU.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

DesNorton SSC-Insane Points: 24401 More actions · Answer 1

Assuming that the data type of [STARTDATE] is date or datetime, and that your "WEEK" is defined as Monday to Sunday, the following SQL should work

SELECT  [DMDUNIT]
      , [LOC]
      , [MODEL]
      , [JOBID]
      , [USERID]
      , [FCSTDATE]
      , [STARTDATE]
      , [Relevance] = CASE WHEN [STARTDATE] >= CAST(DATEADD( dd, 0 -DATEDIFF(dd, 0, GETDATE()) % 7 - 7, GETDATE()) AS date) -- Last Monday
                            AND [STARTDATE] <  CAST(DATEADD( dd, 0 -DATEDIFF(dd, 0, GETDATE()) %7     , GETDATE()) AS date) -- This Monday
                           THEN '1 week before'
                           ELSE 'Not relevant'
                      END
FROM    [BYIntegration].[SCPOMGR].[FCSTPERFSTATIC];

How to post data/code on a forum to get the best help.

JeremyU SSChasing Mays Points: 640 More actions · Answer 2

@desnorton thank you. The only thing it find the last Sunday (1/09) I need to find the Sunday prior, namely 1/02. Do you know what I need to change in a code. So when it is 1/02 it should be '1 week before' everything else should be not relevant.

Question

DesNorton SSC-Insane Points: 24401 More actions · Answer 3

Since you are the one that will be supporting the code and answering if/when it breaks, you really need to try and understand what the code is doing.

The following 2 posts should help you to get a better understanding of how the calculations work.

https://www.sqlservercentral.com/Forums/FindPost1869980.aspx

https://www.sqlservercentral.com/Forums/FindPost1882546.aspx

Based on your description, it appears that my understanding of your requirement is off by 1 week, I would try the following code

      , [Relevance] = CASE WHEN [STARTDATE] >= CAST(DATEADD( dd, 0 -DATEDIFF(dd, 0, GETDATE()) %7 - 14, GETDATE()) AS DATE) -- Prev Monday
                            AND [STARTDATE] <  CAST(DATEADD( dd, 0 -DATEDIFF(dd, 0, GETDATE()) %7 - 7 , GETDATE()) AS DATE) -- Last Monday

How to post data/code on a forum to get the best help.