SQL SUM a CASE statement with an OVER clause

Viewed 1095

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;
1 Answers

Unless you have an aggregation query (which you don't), this should work:

SUM(CASE WHEN ReceiptORIssueIndicator = '+'
         THEN NetQuantity ELSE 0
    END) OVER (PARTITION BY SnapshotWeek, Plant, MaterialNumber ORDER BY Calendar.WeekSort) AS CumulativeSupplyQuantity,

It is a bit hard to figure out what you really want. Your outer query has no aggregation, so it is not clear that aggregation is needed. In a query with aggregation:

SUM(SUM(CASE WHEN ReceiptORIssueIndicator = '+'
             THEN NetQuantity ELSE 0
        END)
   ) OVER (PARTITION BY SnapshotWeek, Plant, MaterialNumber ORDER BY Calendar.WeekSort) AS CumulativeSupplyQuantity,

This assumes that all the PARTITION BY and ORDER BY columns are GROUP BY keys. Otherwise, you need to use aggregation functions for them.

Related