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

  • 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.

    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 1 week ago by  JeremyU.
  • 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];
  • @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

  • 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

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

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