I have the following SQL which works just fine:
-- Supply for each week
SUM(CASE
WHEN ReceiptORIssueIndicator = '+'
THEN NetQuantity
ELSE 0
END) AS SupplyQuantity,
Basically, all receipts of materials are a '+' while consumption of materials are a '-'. This is why I need the case statement to sum only '+'s. The problem is when I try making this a running total. In this instance, each week the suppliers will commit to how much they will supply us for each week. It is a bit confusing but basically, each week they tell us what they are committing to supply for every remaining week of the year. but for this example, lets pretend SnapshotWeek is another group by column like MaterialNumber and the WeekSort column is the one we want to use for a running total.
-- Supply Running Total
SUM(CASE
WHEN ReceiptORIssueIndicator = '+'
THEN SUM(NetQuantity)
ELSE 0
END) OVER (PARTITION BY SnapshotWeek, Plant, MaterialNumber ORDER BY Calendar.WeekSort) AS CumulativeSupplyQuantity,
The data is private otherwise I would provide a sample. I am hoping someone will be able to point out my issue just by looking at the code.
Full Query provided below:
/*
get mrpdata snapshot for each week of the current year.
We add a few pre filters:
Plant: all indianapolis manufacturing plants
MRPc: exclude indy MAKE mrp codes(G*)
ProcurementType: only buy parts
*/
SELECT
MRPDATA.*,
Calendar.WeekSort AS SnapshotWeek
INTO
#weekly_mrpdata_snapshots
FROM
ValueStreamMetrics.MRPDATA
INNER JOIN (
SELECT
metadata_id,
extracted
FROM
ValueStreamMetrics.MRPDATA_metadata
WHERE
CAST(extracted AS DATE) IN (
/* the earliest extracted date for each week */
SELECT
MIN(CAST(extracted AS DATE))
FROM ValueStreamMetrics.MRPDATA_metadata
LEFT JOIN ValueStreamMetrics.Calendar ON
CAST(extracted AS DATE) = Calendar.[Date]
WHERE
Calendar.WeekSort IN (
/* all weeks in the current year */
SELECT DISTINCT WeekSort
FROM ValueStreamMetrics.Calendar
WHERE DATEPART(YEAR, [Date]) = DATEPART(YEAR, GETDATE())
)
GROUP BY WeekSort
)
) AS mrpdata_metadata ON
MRPDATA.metadata_id = mrpdata_metadata.metadata_id
LEFT JOIN ValueStreamMetrics.Calendar ON
CAST(extracted AS DATE) = Calendar.[Date]
LEFT JOIN (
SELECT DISTINCT Plant, MaterialNumber, ProcurementType
FROM ValueStreamMetrics.GMATDATA
) AS gmat ON
MRPDATA.Plant = gmat.Plant
AND MRPDATA.MaterialNumber = gmat.MaterialNumber
WHERE
MRPDATA.Plant IN ('Plant_1', 'Plant_2', 'Plant_3', 'Plant_4', 'Plant_5')
AND Mrpc NOT LIKE 'G__'
AND gmat.ProcurementType = 'F';
SELECT
base_query.CommitWeek,
base_query.SnapshotWeek,
base_query.Plant,
base_query.MaterialNumber,
weekly_commits.SupplyQuantity,
weekly_commits.DemandQuantity
-- Build a list with all join columns(material, plant, etc) for each week of this year
FROM (
SELECT DISTINCT
MaterialNumber,
Plant,
snapshot_week_list.WeekSort AS SnapshotWeek,
commit_week_list.WeekSort AS CommitWeek
FROM #weekly_mrpdata_snapshots
CROSS JOIN (
SELECT DISTINCT WeekSort
FROM ValueStreamMetrics.Calendar
WHERE DATEPART(YEAR, [Date]) = DATEPART(YEAR, GETDATE())
) AS snapshot_week_list
-- yay, recursion
CROSS JOIN (
SELECT DISTINCT WeekSort
FROM ValueStreamMetrics.Calendar
WHERE DATEPART(YEAR, [Date]) = DATEPART(YEAR, GETDATE())
) AS commit_week_list
) AS base_query
/*
Join in Supply and Demand Commits
*/
LEFT JOIN (
SELECT
Plant,
MaterialNumber,
-- Supply for each week
SUM(CASE
WHEN ReceiptORIssueIndicator = '+'
THEN NetQuantity
ELSE 0
END) AS SupplyQuantity,
/*-- Supply Running Total
SUM(CASE
WHEN ReceiptORIssueIndicator = '+'
THEN SUM(NetQuantity)
ELSE 0
END) OVER (PARTITION BY SnapshotWeek, Plant, MaterialNumber, ReceiptORIssueIndicator ORDER BY Calendar.WeekSort) AS CumulativeSupplyQuantity,*/
-- Demand for each week
SUM(CASE
WHEN ReceiptORIssueIndicator = '-'
THEN ABS(NetQuantity)
ELSE 0
END) AS DemandQuantity,
Calendar.WeekSort AS CommitWeek,
SnapshotWeek
FROM
#weekly_mrpdata_snapshots
LEFT JOIN ValueStreamMetrics.Calendar ON
ScheduledReceiptDate = Calendar.[Date]
WHERE
ReceiptORIssueIndicator IN ('+', '-')
-- This makes sure the line doesn't try to start from before
-- the snapshot was taken
AND Calendar.WeekSort >= SnapshotWeek
AND ABS(NetQuantity) > 0
GROUP BY Plant, MaterialNumber, Calendar.WeekSort, SnapshotWeek
) AS weekly_commits ON
base_query.SnapshotWeek = weekly_commits.SnapshotWeek
AND base_query.CommitWeek = weekly_commits.CommitWeek
AND base_query.MaterialNumber = weekly_commits.MaterialNumber
AND base_query.Plant = weekly_commits.Plant
-- make sure we actually pulled a value for something
WHERE
(weekly_commits.SupplyQuantity IS NOT NULL
OR weekly_commits.DemandQuantity IS NOT NULL
)
ORDER BY
Plant,
MaterialNumber,
SnapshotWeek,
CommitWeek;