请求编写按月统计供应商采购订单在途金额的SQL脚本（原脚本由按周统计改为按月统计）

Got it, let's build this report script step by step. First, let's recap your core requirements to make sure we cover everything:

• Show monthly amounts for released inbound purchase orders, calculated as StockOnOrder * UnitPrice (no historical data carryover—we'll focus on the month each order line is tied to)
• Group results by supplierNumber and supplierName
• Output with monthly columns (January, February, etc.) plus a Total column, formatted with £ currency symbols

Key Assumptions (Adjust These to Your Schema!) #

The original request didn't specify how PurchaseOrder and Item tables are linked. I'm assuming there's a common identifier like ItemNumber in both tables to join them—replace ItemNumber with your actual join field (e.g., SKU, ProductID) if it's different.

SQL Script (T-SQL Example) #

WITH MonthlySupplierTotals AS (
    -- First, calculate raw monthly totals per supplier
    SELECT
        ISNULL(po.supplierNumber, 'N/A') AS supplierNumber,
        ISNULL(po.supplierName, 'Unknown Supplier') AS supplierName,
        -- Extract month name from the StockOnOrderByDate string
        DATENAME(MONTH, CAST(po.StockOnOrderByDate AS DATE)) AS OrderMonth,
        -- Calculate line amount, handle NULL values to avoid missing totals
        SUM(ISNULL(po.StockOnOrder, 0) * ISNULL(i.UnitPrice, 0)) AS MonthlyAmount
    FROM
        PurchaseOrder po
    JOIN
        Item i ON po.ItemNumber = i.ItemNumber -- Replace with your actual join condition
    WHERE
        -- Filter out any invalid dates (adjust if you need to limit to a specific date range)
        po.StockOnOrderByDate IS NOT NULL
        AND TRY_CAST(po.StockOnOrderByDate AS DATE) IS NOT NULL
        -- Add a filter here if you want to exclude "historical data" (e.g., only current year: YEAR(CAST(po.StockOnOrderByDate AS DATE)) = YEAR(GETDATE()))
    GROUP BY
        ISNULL(po.supplierNumber, 'N/A'),
        ISNULL(po.supplierName, 'Unknown Supplier'),
        DATENAME(MONTH, CAST(po.StockOnOrderByDate AS DATE)),
        -- Ensure months sort correctly by month number
        DATEPART(MONTH, CAST(po.StockOnOrderByDate AS DATE))
),
PivotedResults AS (
    -- Pivot monthly totals into separate columns
    SELECT
        supplierNumber,
        supplierName,
        -- Format each month's amount with £ symbol
        FORMAT(ISNULL(January, 0), 'C', 'en-GB') AS January,
        FORMAT(ISNULL(February, 0), 'C', 'en-GB') AS February,
        FORMAT(ISNULL(March, 0), 'C', 'en-GB') AS March,
        FORMAT(ISNULL(April, 0), 'C', 'en-GB') AS April,
        FORMAT(ISNULL(May, 0), 'C', 'en-GB') AS May,
        FORMAT(ISNULL(June, 0), 'C', 'en-GB') AS June,
        FORMAT(ISNULL(July, 0), 'C', 'en-GB') AS July,
        FORMAT(ISNULL(August, 0), 'C', 'en-GB') AS August,
        FORMAT(ISNULL(September, 0), 'C', 'en-GB') AS September,
        FORMAT(ISNULL(October, 0), 'C', 'en-GB') AS October,
        FORMAT(ISNULL(November, 0), 'C', 'en-GB') AS November,
        FORMAT(ISNULL(December, 0), 'C', 'en-GB') AS December,
        -- Calculate total across all months
        FORMAT(
            ISNULL(January, 0) + ISNULL(February, 0) + ISNULL(March, 0) +
            ISNULL(April, 0) + ISNULL(May, 0) + ISNULL(June, 0) +
            ISNULL(July, 0) + ISNULL(August, 0) + ISNULL(September, 0) +
            ISNULL(October, 0) + ISNULL(November, 0) + ISNULL(December, 0),
            'C', 'en-GB'
        ) AS Total
    FROM
        MonthlySupplierTotals
    PIVOT (
        SUM(MonthlyAmount)
        FOR OrderMonth IN (
            January, February, March, April, May, June,
            July, August, September, October, November, December
        )
    ) AS PivotTable
)
-- Final output, ordered by supplier number
SELECT * FROM PivotedResults ORDER BY supplierNumber;

What This Script Does #

1. CTE 1: MonthlySupplierTotals:

   • Cleans up NULL values for suppliers (replaces with 'N/A'/'Unknown Supplier')
   • Converts the string StockOnOrderByDate to a date, then extracts the month name/number
   • Calculates the total amount per supplier per month, handling NULLs in StockOnOrder or UnitPrice to avoid dropping rows
   • Filters out invalid date strings to prevent errors

2. CTE 2: PivotedResults:

   • Uses SQL's PIVOT function to turn monthly rows into individual columns
   • Formats each month's total with the £ currency symbol using FORMAT() (works in SQL Server 2012+)
   • Calculates the grand total by summing all monthly columns
   • Orders results by supplier number for readability

Adjustments You Might Need #

• Join Condition: Replace po.ItemNumber = i.ItemNumber with the actual field that links your PurchaseOrder and Item tables (e.g., po.SKU = i.SKU)
• Date Filter: If "no historical data" means only including the current year/quarter, add a WHERE clause like YEAR(CAST(po.StockOnOrderByDate AS DATE)) = YEAR(GETDATE())
• Dynamic Months: If you need the script to automatically include only months with data (instead of all 12), you'll need to use dynamic SQL to build the pivot columns—let me know if you want that version!
• Currency Format: If you're not using SQL Server, replace FORMAT() with your database's equivalent (e.g., TO_CHAR() in PostgreSQL/Oracle)

内容的提问来源于stack exchange，提问作者KeeganMcc