Post reply

Calculate a sum based on time periods in CASE statement

  • May 19, 2023 at 6:32 pm

    #4193227

    Hello everyone,

    I have a situation where I am trying to calculate SUM(History Quantity) based on MIN Posted Date + specific number of weeks. For example, how many quantities were sold within the start date of '2022-05-22' + 8 weeks

    The code that I have now, but need to understand how to make CASE statement work because it is giving the same result right now.

    -- Calculate a sum based on time periods in CASE statement
    SELECT [U_CHAINNAME] AS 'CHAIN NAME', DMDUNIT, MIN(DMDPostDate) AS 'DMDPostDate', 

    CASE WHEN MIN(DMDPostDate) BETWEEN MIN(DMDPostDate) AND DATEADD(DAY, +56,DATEDIFF(DAY,0,MIN(DMDPostDate))) THEN SUM([HistoryQuantity]) END AS 'History Quantity for 8 weeks based on MIN DMDPost Date'

    ,CASE WHEN MIN(DMDPostDate) BETWEEN MIN(DMDPostDate) AND DATEADD(DAY, +70,DATEDIFF(DAY,0,MIN(DMDPostDate))) THEN SUM([HistoryQuantity]) END AS 'History Quantity for 10 weeks based on MIN DMDPost Date'

    ,CASE WHEN MIN(DMDPostDate) BETWEEN MIN(DMDPostDate) AND DATEADD(DAY, +91,DATEDIFF(DAY,0,MIN(DMDPostDate))) THEN SUM([HistoryQuantity]) END AS 'History Quantity for 13 weeks based on MIN DMDPost Date'

    ,CASE WHEN MIN(DMDPostDate) BETWEEN MIN(DMDPostDate) AND DATEADD(DAY, +182,DATEDIFF(DAY,0,MIN(DMDPostDate))) THEN SUM([HistoryQuantity]) END AS 'History Quantity for 26 weeks based on MIN DMDPost Date'

    FROM table_hist HIST
    LEFT JOIN table_loc LOC
    ON HIST.LOC = LOC.LOC

    where [U_CHAINNAME]='WALMART' and DMDUNIT='9781728239682'
    GROUP BY U_CHAINNAME, DMDUNIT

     

    Issue1

     

    Please let me know if you have any ideas.

     

    Thank you.

  • May 19, 2023 at 7:46 pm

    #4193244

    Since you didn't put aliases on the column names, I (we) have NO idea which table each column comes from; therefore, I had to use xx. as a dummy/generic alias.

    I also can't test the code in any way since you didn't post any usable sample data.

    ;WITH cte_MINDMPostDate AS (
        SELECT U_CHAINNAME, DMDUNIT, MIN(DMPostDate) AS [MINDMPostDate]
        FROM table_hist HIST
        LEFT JOIN table_loc LOC
        ON HIST.LOC = LOC.LOC
        GROUP BY U_CHAINNAME, DMDUNIT
    )

    SELECT xx.[U_CHAINNAME] AS 'CHAIN NAME', xx.DMDUNIT, MIN(ctemin.MINDMDPostDate) AS 'DMDPostDate', 
    SUM(CASE WHEN DMDPostDate BETWEEN ctemin.MINDMDPostDate AND DATEADD(DAY, +56,DATEDIFF(DAY,0,ctemin.MINDMDPostDate)) THEN [HistoryQuantity] END) AS 'History Quantity for 8 weeks based on MIN DMDPost Date'
    ,SUM(CASE WHEN DMDPostDate BETWEEN ctemin.MINDMDPostDate AND DATEADD(DAY, +70,DATEDIFF(DAY,0,ctemin.MINDMDPostDate)) THEN [HistoryQuantity] END) AS 'History Quantity for 10 weeks based on MIN DMDPost Date'
    ,SUM(CASE WHEN DMDPostDate BETWEEN ctemin.MINDMDPostDate AND DATEADD(DAY, +91,DATEDIFF(DAY,0,ctemin.MINDMDPostDate)) THEN [HistoryQuantity] END) AS 'History Quantity for 13 weeks based on MIN DMDPost Date'
    ,SUM(CASE WHEN DMDPostDate BETWEEN ctemin.MINDMDPostDate AND DATEADD(DAY, +182,DATEDIFF(DAY,0,ctemin.MINDMDPostDate)) THEN [HistoryQuantity] END) AS 'History Quantity for 26 weeks based on MIN DMDPost Date'

    FROM table_hist HIST
    LEFT JOIN table_loc LOC
    ON HIST.LOC = LOC.LOC

    INNER JOIN cte_MINDMPostDate ctemin ON ctemin.U_CHAIN_NAME = xx.U_CHAINNAME AND ctemin.DMDUNIT = xx.DMDUNIT

    WHERE [U_CHAINNAME]='WALMART' and DMDUNIT='9781728239682'
    GROUP BY U_CHAINNAME, DMDUNIT

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • May 20, 2023 at 6:33 am

    #4193485

    Scott, it is good. Thank you!

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

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