如何在SQL查询中获取期初与期末余额？附现有查询及结果

Hey there! I notice your current query is summarizing debit/credit amounts per voucher, which makes sense since each voucher should balance (hence your equal Dr/Cr amounts). To get opening and closing balances, we need to shift focus to account-level totals and factor in a date range—since balances are time-based. Let's walk through this step by step.

First, Let's Recap Your Current Query #

Here's your existing SQL formatted for readability:

SELECT 
    [Voucher].[TransactionCode] AS [VoucherNo],
    SUM([Detail].[DrAmount]) AS [DrAmount],
    SUM([Detail].[CrAmount]) AS [CrAmount]
FROM 
    [FICO].[tbl_TransactionMaster] [Voucher],
    [FICO].[tbl_TransactionDetail] [Detail]
WHERE 
    [Detail].[TransactionCode] = [Voucher].[ID]
GROUP BY 
    [Voucher].[TransactionCode]

And your current results look like this (abbreviated):

VoucherNo DrAmount CrAmount
FMS-CRV-1-1-Doc--18 12 12
FMS-CRV-2-1-Doc--18 999 999
FMS-CRV-3-... ... ...

How to Add Opening & Closing Balances #

To calculate these balances, we need to:

• Define a reporting period (e.g., all transactions in a specific month)
• Calculate the opening balance for each account (total balance before the reporting period starts)
• Calculate period activity (debits minus credits during the period)
• Derive the closing balance (opening balance + period activity)

Assuming your tables have these key fields (adjust if your schema differs):

• tbl_TransactionMaster.TransactionDate: The date of the voucher
• tbl_TransactionDetail.AccountCode: The account associated with each debit/credit line item

Here's an adjusted query that computes these balances:

WITH AccountPeriodActivity AS (
    -- Step 1: Calculate total debits and credits per account for your reporting period
    SELECT 
        [Detail].[AccountCode],
        SUM([Detail].[DrAmount]) AS PeriodTotalDebits,
        SUM([Detail].[CrAmount]) AS PeriodTotalCredits
    FROM 
        [FICO].[tbl_TransactionMaster] [Voucher]
        JOIN [FICO].[tbl_TransactionDetail] [Detail] 
            ON [Detail].[TransactionCode] = [Voucher].[ID]
    WHERE 
        -- Replace with your desired reporting period
        [Voucher].[TransactionDate] BETWEEN '2024-01-01' AND '2024-01-31'
    GROUP BY 
        [Detail].[AccountCode]
),
AccountOpeningBalances AS (
    -- Step 2: Calculate opening balance (balance before the reporting period)
    SELECT 
        [Detail].[AccountCode],
        -- Opening balance = total debits minus total credits before the period starts
        SUM([Detail].[DrAmount]) - SUM([Detail].[CrAmount]) AS OpeningBalance
    FROM 
        [FICO].[tbl_TransactionMaster] [Voucher]
        JOIN [FICO].[tbl_TransactionDetail] [Detail] 
            ON [Detail].[TransactionCode] = [Voucher].[ID]
    WHERE 
        [Voucher].[TransactionDate] < '2024-01-01'
    GROUP BY 
        [Detail].[AccountCode]
)
-- Step 3: Combine opening balance with period activity to get closing balance
SELECT 
    COALESCE(oba.AccountCode, apa.AccountCode) AS AccountCode,
    COALESCE(oba.OpeningBalance, 0) AS OpeningBalance,
    COALESCE(apa.PeriodTotalDebits, 0) AS PeriodDebits,
    COALESCE(apa.PeriodTotalCredits, 0) AS PeriodCredits,
    -- Closing balance = opening + (debits - credits)
    COALESCE(oba.OpeningBalance, 0) + (COALESCE(apa.PeriodTotalDebits, 0) - COALESCE(apa.PeriodTotalCredits, 0)) AS ClosingBalance
FROM 
    AccountPeriodActivity apa
    FULL OUTER JOIN AccountOpeningBalances oba 
        ON apa.AccountCode = oba.AccountCode
ORDER BY 
    AccountCode;

Key Notes: #

• Adjust Date Ranges: Replace the 2024-01-01 and 2024-01-31 values with your actual reporting period.
• FULL OUTER JOIN: Ensures we include accounts that have opening balances but no activity in the period, and vice versa.
• COALESCE: Handles NULL values (e.g., accounts with no opening balance start at 0).
• Account Focus: If you did want voucher-level balances (though that's less common since vouchers are balanced), you'd need to track the running balance of the account through each voucher—but that's a different use case.

If your schema has different field names (e.g., AccountID instead of AccountCode, or PostingDate instead of TransactionDate), just swap those out to match your database.

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