按指定时间段创建客户到期金额SQL透视表的技术咨询

Got it, let's tackle this pivot requirement. The key here is to first aggregate your data correctly to avoid duplicate rows per AccountID and DueDate, then use conditional aggregation to calculate the totals for each of your four time periods.

Here's the complete query that should give you the desired result:

WITH AggregatedDueAmounts AS (
    -- First, get the total remaining amount per AccountID and DueDate
    SELECT 
        [ADD].AccountID,
        ADDN.DueDate,
        SUM(CONVERT(money, ADDN.Amount - ISNULL(ANC.CollectedValue, 0))) AS TotalRestAmount
    FROM [Accounting].[AccDocumentDetailsNotes] ADDN
    INNER JOIN Accounting.AccDocumentDetails [ADD] 
        ON ADDN.AccDocumentDetailID = [ADD].ID
    INNER JOIN Accounting.AccDocumentHeader ADH 
        ON ADH.ID = [ADD].AccDocumentHeaderID
    INNER JOIN [Accounting].[AccNotesCollectors] ANC 
        ON ANC.NoteID = ADDN.ID
    INNER JOIN Accounting.AccAccounts AA 
        ON AA.ID = [ADD].AccountID
    WHERE 
        [CodeTypePart] = 'NR' 
        AND CONVERT(money, ADDN.Amount - ISNULL(ANC.CollectedValue, 0)) > 0
    GROUP BY [ADD].AccountID, ADDN.DueDate
)
SELECT 
    AccountID,
    -- Total due today (same calendar day, ignoring time)
    SUM(CASE 
            WHEN CAST(DueDate AS DATE) = CAST(GETDATE() AS DATE) 
            THEN TotalRestAmount 
            ELSE 0 
        END) AS [Today's Total Due],
    -- Total due from today to next 30 days
    SUM(CASE 
            WHEN DueDate BETWEEN GETDATE() AND DATEADD(DAY, 30, GETDATE()) 
            THEN TotalRestAmount 
            ELSE 0 
        END) AS [Next 30 Days Total Due],
    -- Total due from 31st to 60th day from today
    SUM(CASE 
            WHEN DueDate BETWEEN DATEADD(DAY, 31, GETDATE()) AND DATEADD(DAY, 60, GETDATE()) 
            THEN TotalRestAmount 
            ELSE 0 
        END) AS [31-60 Days Total Due],
    -- Total due from 61st to 90th day from today
    SUM(CASE 
            WHEN DueDate BETWEEN DATEADD(DAY, 61, GETDATE()) AND DATEADD(DAY, 90, GETDATE()) 
            THEN TotalRestAmount 
            ELSE 0 
        END) AS [61-90 Days Total Due]
FROM AggregatedDueAmounts
GROUP BY AccountID
ORDER BY AccountID;

Let me break down what this does: #

• CTE (Common Table Expression) - AggregatedDueAmounts:

  • This step cleans up your original data by grouping only on AccountID and DueDate, eliminating duplicate rows for the same customer and due date (like the two entries for AccountID 25 in your sample).
  • We calculate the total remaining amount for each unique AccountID + DueDate pair, and move your filters to the WHERE clause (more efficient than using HAVING since we filter before aggregation).

• Conditional Aggregation for Pivoting:

  • Instead of the rigid PIVOT operator, we use SUM(CASE ...) to flexibly compute totals for each time period:
    • Today's Total Due: Ignores time components by casting dates to DATE type, ensuring we only count amounts due on the current calendar day.
    • Next 30 Days: Includes all dates from right now up to exactly 30 days in the future.
    • 31-60 Days: Covers the period starting the day after the first 30-day window, up to 60 days from today.
    • 61-90 Days: Covers the final window from day 61 to day 90 from today.

• Final Grouping:

  • We group by AccountID to get one row per customer, with all four time period totals as separate columns.

If you need to adjust the date boundaries (e.g., exclude the exact end date of a window), you can swap BETWEEN for >/< operators. For example, to make "next 30 days" exclude the 30th day itself, change that condition to DueDate >= GETDATE() AND DueDate < DATEADD(DAY, 30, GETDATE()).

内容的提问来源于stack exchange，提问作者Mümin