请求实现：按月统计数据量并生成MON/流水号格式凭证号

Got it, let's tackle your two requirements with a polished SQL solution that builds on your initial query:

1. Generate the Formatted Voucher Number (MON/Serial_No) #

To create the voucher number in your desired format, we'll combine the 3-letter month abbreviation with a sequential number that resets each month. We'll use window functions to handle the per-month sequencing reliably.

2. Show Monthly Record Counts #

We can either attach the total number of records per month to every entry in that month, or create a separate summary of monthly totals—both options are covered below.

Final Query (With Per-Entry Monthly Counts) #

This query will return each payment record with its formatted voucher number, payment date, and total number of records for that month:

SELECT
    -- Build the voucher number: Month Abbreviation / Sequential Number
    CONCAT(
        UPPER(LEFT(DATENAME(MONTH, Cust_Payment.Date_Of_Payment), 3)),
        '/',
        ROW_NUMBER() OVER (
            PARTITION BY YEAR(Cust_Payment.Date_Of_Payment), MONTH(Cust_Payment.Date_Of_Payment)
            ORDER BY Cust_Payment.Date_Of_Payment -- Replace with a unique ID column if you have one for stricter ordering
        )
    ) AS 'Voucher_No',
    Cust_Payment.Date_Of_Payment AS 'Voucher_Date',
    -- Show total records in the current month/year
    COUNT(*) OVER (
        PARTITION BY YEAR(Cust_Payment.Date_Of_Payment), MONTH(Cust_Payment.Date_Of_Payment)
    ) AS 'Monthly_Total_Records'
FROM Customer_Payment;

Key Notes: #

• Year + Month Partition: We include YEAR() in the partition to ensure January 2023 and January 2024 get separate sequential numbering (no cross-year count continuity).
• Ordering for Sequential Numbers: The ORDER BY inside ROW_NUMBER() ensures your serial numbers follow a consistent order—using Date_Of_Payment works, but a unique payment ID is more reliable if you have one.
• Monthly Count: The COUNT(*) OVER (...) calculates the total entries for the same month/year as the current row, so every record in a month will show the same total count.

Example Output: #

Separate Monthly Summary Query #

If you only need a high-level count of records per month (not attached to individual entries), use this aggregate query:

SELECT
    UPPER(LEFT(DATENAME(MONTH, Date_Of_Payment), 3)) AS 'Month_Abbreviation',
    YEAR(Date_Of_Payment) AS 'Year',
    COUNT(*) AS 'Total_Records'
FROM Customer_Payment
GROUP BY YEAR(Date_Of_Payment), MONTH(Date_Of_Payment), UPPER(LEFT(DATENAME(MONTH, Date_Of_Payment), 3))
ORDER BY YEAR(Date_Of_Payment), MONTH(Date_Of_Payment);

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