Saya memiliki SQL berikut yang berfungsi dengan baik:

-- Supply for each week
SUM(CASE
    WHEN ReceiptORIssueIndicator = '+'
        THEN NetQuantity
    ELSE 0
END) AS SupplyQuantity,

Pada dasarnya, semua penerimaan bahan adalah '+' sedangkan konsumsi bahan adalah '-'. Inilah sebabnya mengapa saya memerlukan pernyataan kasus untuk menjumlahkan hanya '+. Masalahnya adalah ketika saya mencoba membuat ini berjalan total. Dalam hal ini, setiap minggu pemasok akan berkomitmen untuk berapa banyak mereka akan memasok kami untuk setiap minggu. Ini agak membingungkan tetapi pada dasarnya, setiap minggu mereka memberi tahu kami apa yang mereka berkomitmen untuk memasok untuk setiap minggu yang tersisa dalam setahun. tetapi untuk contoh ini, mari kita berpura-pura SnapshotWeek adalah grup lain dengan kolom seperti MaterialNumber dan kolom WeekSort adalah yang ingin kita gunakan untuk total berjalan.

-- 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,

Data bersifat pribadi jika tidak saya akan memberikan sampel. Saya berharap seseorang dapat menunjukkan masalah saya hanya dengan melihat kodenya.

Query lengkap disediakan di bawah ini:

/*
    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
Maxwell 18 Mei 2021, 15:20

1 menjawab

Jawaban Terbaik

Kecuali Anda memiliki kueri agregasi (yang tidak Anda miliki), ini akan berfungsi:

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

Agak sulit untuk mengetahui apa yang Anda sebenarnya inginkan. Kueri luar Anda tidak memiliki agregasi, jadi tidak jelas bahwa agregasi diperlukan. Dalam kueri dengan agregasi:

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

Ini mengasumsikan bahwa semua kolom PARTITION BY dan ORDER BY adalah kunci GROUP BY. Jika tidak, Anda perlu menggunakan fungsi agregasi untuk mereka.

0
Gordon Linoff 18 Mei 2021, 12:46