SQL按日期条件计算两列累积求和的问题求助

Let's break down what's wrong with your current approach and fix it to match your requirements.

Why Your Current Query Fails #

Your existing SQL uses ID for ordering in the window function, which doesn't align with the date-based logic you need. The conditional CASE statement also doesn't correctly isolate the RecAmt values from records where RecDate is earlier than the current row's SubDate.

Correct Approach #

For each record, we need to:

• Take the current row's SubAmt
• Add the sum of RecAmt from all records in the same Class where RecDate is earlier than the current row's SubDate

We also need to handle date formatting inconsistencies (some dates use 2-digit years, others 4-digit) by converting them to proper date types for accurate comparison.

Corrected SQL Query #

SELECT 
    t1.ID,
    t1.Class,
    t1.SubDate,
    t1.RecDate,
    t1.SubAmt,
    t1.RecAmt,
    t1.SubAmt + COALESCE(
        (SELECT SUM(t2.RecAmt) 
         FROM TableName t2 
         WHERE t2.Class = t1.Class 
           AND CONVERT(date, t2.RecDate, 3) < CONVERT(date, t1.SubDate, 3)),
        0
    ) AS Cumulative
FROM TableName t1
ORDER BY t1.Class, CONVERT(date, t1.SubDate, 3);

How This Works #

1. Date Conversion: CONVERT(date, [DateColumn], 3) converts your dd/mm/yy or dd/mm/yyyy strings to valid date types, ensuring accurate chronological comparison.
2. Correlated Subquery: For each row t1, the subquery finds all matching Class records where RecDate is earlier than t1.SubDate, sums their RecAmt.
3. COALESCE: Handles cases where there are no matching earlier records (like ID 1) by replacing NULL with 0, so we just use the current SubAmt.
4. Ordering: Results are sorted by Class and SubDate to make it easier to verify cumulative values.

Example Verification #

• ID 1: No RecDate values are earlier than 23/08/15, so Cumulative = 12710 + 0 = 12710 ✔️
• ID 3: RecDate from ID 1 (15/10/2015) is earlier than 23/10/15, so Cumulative = 2096 + 10613 = 12710 ✔️
• ID 8: RecDate values from ID 1 and ID 6 are earlier than 23/03/16, so Cumulative = 217168 + (10613 + 78416) = 306197 ✔️

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