SQL Server 创建帐龄报告(>0)，(0-30)，(31-60)

k5hmc34c 于 2022-11-21 发布在其他

关注(0)|答案(2)|浏览(89)

我试图在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'

非常感谢您的光临。

sql-server

来源：https://stackoverflow.com/questions/74364146/creating-ageing-report-0-0-30-31-60

2条答案

按热度按时间

2mbi3lxu1#

你可以清理一下
首先，使用CROSS APPLY计算一次过期天数，然后对最终结果进行条件聚合

示例（未测试）

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

编辑-贷方票据

在没有示例数据或结构的情况下，这里是我的猜测

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 - isnull(creditnotes,0) else 0 end)
        ,[31 to 60 Days]  = sum( case when DPD between 31 and  60 then doctotal - isnull(creditnotes,0) else 0 end)
        ,[61 to 90 Days]  = sum( case when DPD between 61 and  90 then doctotal - isnull(creditnotes,0) else 0 end)
        ,[91 to 120 Days] = sum( case when DPD between 91 and 120 then doctotal - isnull(creditnotes,0) else 0 end)
        ,[121+ Days ]     = sum( case when DPD >=121              then doctotal - isnull(creditnotes,0) else 0 end)
   From  ocrd O
   Join  oinv INV on O.cardcode = INV.cardcode
   LEFT JOIN CREDITNOTESTable CN ON O.cardcode = CN.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

赞(0）回复(0）举报 2022-11-21

cnwbcb6i2#

当提出问题时，提供演示数据是一个很好的主意，而将其作为一个对象提供则是一个更好的主意，我们可以轻松地重新创建：

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子句。

赞(0）回复(0）举报 2022-11-21

我来回答

SQL Server 创建帐龄报告(>0)，(0-30)，(31-60)

2条答案

相关问题

热门标签

最新问答