SQL Server：按年月统计支付方式使用量，识别停用支付方式

To figure out which payment methods are still active and which have been phased out, we’ll need to aggregate your billing data by year-month for each payment mode—including zeros for months where a mode wasn’t used, so we can clearly track trends over time.

Step-by-Step Approach #

• Map IDs to Names: First, create a reference list linking numeric PAY_MODE_ID values to their human-readable payment method names.
• Generate Time Periods: Extract all distinct year-month combinations from your BILL_INFO table to ensure we don’t miss any time periods in our analysis.
• Combine & Aggregate: Cross join payment modes with year-month periods, then left join to aggregated billing counts. This ensures every payment mode appears for every month, even if it wasn’t used (showing a count of 0).

Full SQL Query #

WITH PaymentModes AS (
    -- Reference table linking PAY_MODE_ID to payment names
    SELECT 1 AS PAY_MODE_ID, 'Cash' AS PaymentMode UNION ALL
    SELECT 2, 'Cheque' UNION ALL
    SELECT 3, 'Debit' UNION ALL
    SELECT 4, 'Free' UNION ALL
    SELECT 5, 'Credit Card' UNION ALL
    SELECT 6, 'Advanced Cash' UNION ALL
    SELECT 7, 'Cash Card' UNION ALL
    SELECT 8, 'Visa Electron' UNION ALL
    SELECT 9, 'e-Pay' UNION ALL
    SELECT 10, 'Bank Transfer' UNION ALL
    SELECT 11, 'Third-Party Collections'
),
YearMonths AS (
    -- Get all distinct year-month periods from the billing data
    SELECT DISTINCT
        DATEFROMPARTS(YEAR(BILL_DATE), MONTH(BILL_DATE), 1) AS YearMonth
    FROM [dbo].[BILL_INFO]
)
SELECT
    ym.YearMonth,
    pm.PaymentMode,
    COUNT(bi.PAY_MODE_ID) AS UsageCount
FROM YearMonths ym
CROSS JOIN PaymentModes pm
LEFT JOIN [dbo].[BILL_INFO] bi
    ON ym.YearMonth = DATEFROMPARTS(YEAR(bi.BILL_DATE), MONTH(bi.BILL_DATE), 1)
    AND pm.PAY_MODE_ID = bi.PAY_MODE_ID
GROUP BY ym.YearMonth, pm.PaymentMode
ORDER BY ym.YearMonth DESC, pm.PaymentMode;

How to Interpret the Results #

• Discontinued Methods: Look for payment modes where UsageCount drops to 0 and stays at 0 in all subsequent months—these are likely phased-out methods.
• Declining Trends: For modes still in use, watch if their UsageCount steadily decreases over time—this could signal they’re being replaced and may be discontinued soon.
• Active Methods: Modes with consistent or increasing UsageCount are your current preferred payment options.

Quick Notes #

• If BILL_DATE has NULL values, add WHERE bi.BILL_DATE IS NOT NULL to exclude those from counts.
• Adjust the ORDER BY clause to sort by PaymentMode first if you want to view each mode’s trend consecutively.

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