創建老化報告(>0),(0-30),(31-60)

我正在嘗試在 SQL Server 上撰寫一份賬齡報告，該報告顯示根據逾期天數落在不同列中的逾期發票總額（稍后我將不得不扣除信用票據）。即（>0）、（0-30）、（31-60）、（61-90）等。

這是我到目前為止寫的查詢的一部分，主要是查看這個論壇中的舊帖子，但它給了我很多重復，即使對于沒有到期余額的帳戶也是如此。

知道我做錯了什么嗎？

SELECT O.cardcode      AS [Account],
       O.cardname      AS [Name],
       O.u_creditlimit AS [Credit Limit],
       O.u_onhold      AS [On Hold],
       O.balance,
       Isnull(CASE
                WHEN Datediff(day, INV.docduedate, Getdate()) >= 0 AND Datediff(day, INV.docduedate, Getdate()) < 30 
                THEN (
                       SELECT Sum(doctotal)
                       FROM   oinv
                       WHERE cardcode = INV.cardcode)
              END, 0)  AS [0 to 30 Days],
       Isnull(CASE
                WHEN Datediff(day, INV.docduedate, Getdate()) >= 31 AND Datediff(day, INV.docduedate, Getdate()) < 60 
                THEN (
                      SELECT Sum(doctotal)
                      FROM   oinv
                      WHERE cardcode = INV.cardcode)
              END, 0)  AS [31 to 60 Days],
       Isnull(CASE
                WHEN Datediff(day, INV.docduedate, Getdate()) >= 61 AND Datediff(day, INV.docduedate, Getdate()) < 90 
                THEN (
                      SELECT Sum(doctotal)
                      FROM   oinv
                      WHERE cardcode = INV.cardcode)
              END, 0)  AS [61 to 90 Days],
       Isnull(CASE
                WHEN Datediff(day, INV.docduedate, Getdate()) >= 91 AND Datediff(day, INV.docduedate, Getdate()) < 120 
                THEN (
                      SELECT Sum(doctotal)
                      FROM   oinv
                      WHERE  cardcode = INV.cardcode)
              END, 0)  AS [91 to 120 Days],
       Isnull(CASE
                WHEN Datediff(day, INV.docduedate, Getdate()) >= 121 
                THEN(
                      SELECT Sum(doctotal)
                      FROM oinv
                      WHERE cardcode = INV.cardcode)
              END, 0)  AS [121  Days]
FROM   ocrd O
       INNER JOIN oinv INV
               ON O.cardcode = INV.cardcode
WHERE  territory = 3
       AND INV.docstatus = 'O' 

非常感謝。

uj5u.com熱心網友回復：

你可以清理一下

第一的。使用 CROSS APPLY 計算 Days-Past-Due 一次，然后對最終結果進行條件聚合

示例（未經測驗）

  Select O.cardcode
        ,O.cardname  
        ,[Credit Limit]   = max(O.u_creditlimit)
        ,[On Hold]        = max(O.u_onhold)
        ,[0 to 30 Days]   = sum( case when DPD between  0 and  30 then doctotal else 0 end)
        ,[31 to 60 Days]  = sum( case when DPD between 31 and  60 then doctotal else 0 end)
        ,[61 to 90 Days]  = sum( case when DPD between 61 and  90 then doctotal else 0 end)
        ,[91 to 120 Days] = sum( case when DPD between 91 and 120 then doctotal else 0 end)
        ,[121  Days ]     = sum( case when DPD >=121              then doctotal else 0 end)
   From  ocrd O
   Join  oinv INV on O.cardcode = INV.cardcode
   Cross Apply (values ( Datediff(day, INV.docduedate, Getdate()) ) ) P(DPD)
   Where territory = 3
     and INV.docstatus = 'O' 
     and DPD >= 0
  Group By O.cardcode
          ,O.cardname  

uj5u.com熱心網友回復：

在提問時提供演示資料是一個好主意，而將其作為一個我們可以輕松重新創建的物件來提供是一個更好的主意：

DECLARE @table TABLE (RecordID INT IDENTITY, CardCode INT, CardName NVARCHAR(100), u_CreditLimit DECIMAL(10,2), u_onhold DECIMAL(10,2), balance DECIMAL(10,2))
INSERT INTO @table (CardCode, CardName, u_CreditLimit, u_onhold, balance) VALUES (1, 'John Smith', 10000, 0, 200),
(1, 'John Smith', 10000, 0, 400),
(1, 'John Smith', 10000, 0, 200)

這將允許某人只運行 TSQL 來創建和填充物件。

現在使用該物件，我們可以撰寫類似

SELECT RecordID, CardCode, CardName, U_CreditLimit, U_OnHold, Balance, COALESCE(LAG(Balance,1) OVER (PARTITION BY CardCode ORDER BY RecordID) - Balance,Balance) AS RunningTotal
  FROM @table

這個偽代碼，可能需要一些調整才能得到你正在尋找的東西。

關于 LAG 及其伙伴 LEAD 的簡短介紹。您指定列和行中的偏移量。LAG 向后看，LEAD 向前看。它們都使用 over 子句，就像任何其他視窗函式一樣。

標籤：sql服务器

上一篇：如何查詢一組數字是否在同一張表上的SQLServer中重復？

下一篇：如何使用前10條記錄中的模式更新表格行（類似于excel中的填充句柄）