Calculate a sum based on time periods in CASE statement
-
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
Please let me know if you have any ideas.
Thank you.
-
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, DMDUNITSQL 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".
-
Scott, it is good. Thank you!
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply