sql-server - 如何在sql中按客户的天数计算金额

Question

我想从发票表中按天计算客户总数，因为我在下面的查询中使用了：我想要什么

第一个表名称：MstCustomer
第二个表名称：Job_Details
第三个表名称：发票

select distinct Cust.Cust_Name,
            case when DATEDIFF(dd,INV.Bill_Submit_Date,GETDATE()) > 0 and DATEDIFF(dd,INV.Bill_Submit_Date,GETDATE()) < 31
            then sum(INV.Bill_Amount) end  AS '1-30 DAYS',
            case when DATEDIFF(dd,INV.Bill_Submit_Date,GETDATE()) > 30 and DATEDIFF(dd,INV.Bill_Submit_Date,GETDATE()) < 46
            then sum(INV.Bill_Amount) end AS '31-45 DAYS',
            case when DATEDIFF(dd,Bill_Submit_Date,GETDATE()) > 45 
            then sum(INV.Bill_Amount) end AS 'ABOVE 45 DAYS',
            Balance =sum(INV.Bill_Amount) - sum(INV.Advance_Amount)

            from Invoice INV 
                               inner join Job_Details JD on JD.Job_ID= INV.Job_ID
                               inner join MstCustomer cust ON cust.Cust_ID= JD.Cust_ID

                               group by Cust.Cust_Name,Bill_Submit_Date,Bill_Amount,Advance_Amount

预期结果

标签： sql-server

Answer 1

您的查询看起来已经完成了 95%，只是您的sum功能放错了位置：

select Cust.Cust_Name
      ,sum(case when DATEDIFF(dd,INV.Bill_Submit_Date,GETDATE()) < 31
                then INV.Bill_Amount
                else 0
                end
          )  AS [1-30 DAYS]
      ,sum(case when DATEDIFF(dd,INV.Bill_Submit_Date,GETDATE()) between 31 and 45
                then INV.Bill_Amount
                else 0
                end
          ) AS [31-45 DAYS]
      ,sum(case when DATEDIFF(dd,Bill_Submit_Date,GETDATE()) > 45 
                then INV.Bill_Amount
                else 0
                end
          ) AS [ABOVE 45 DAY]
      ,sum(INV.Bill_Amount) - sum(INV.Advance_Amount) as Balance

from Invoice INV 
    inner join Job_Details JD
        on JD.Job_ID= INV.Job_ID
    inner join MstCustomer cust
        on cust.Cust_ID= JD.Cust_ID

group by Cust.Cust_Name