Comment calculer le montant par jour d'un client en SQL ?

Question

Je veux calculer le montant total du client par jour à partir du tableau des factures. Pour cela, j'ai utilisé la requête suivante : ce que je veux.

1er nom de table : MstCustomer
2ème nom de table : Job_Details
Nom du troisième tableau : Facture

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

Résultat attendu

Answer 1

Votre requête semble avoir fait 95% du chemin, il ne vous reste plus qu'à faire votre choix. sum au mauvais endroit :

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

Answer 2

En effectuant le DATEDIFF dans une sous-requête, vous pouvez simplifier les calculs dans la requête externe.

Et vous voudriez faire la SOMME de la CASE, au lieu d'utiliser la SOMME dans une CASE.

Par exemple :

select 
 cust.Cust_Name as "Customer Name",
 SUM(CASE WHEN q.Days_Submitted BETWEEN 1 AND 30 THEN q.Bill_Amount END) AS "1-30 Days Amount",
 SUM(CASE WHEN q.Days_Submitted BETWEEN 31 AND 45 THEN q.Bill_Amount END) AS "31-45 Days Amount",
 SUM(CASE WHEN q.Days_Submitted > 45 THEN q.Bill_Amount END) AS "Above 45 Days Amount",
 SUM(q.Bill_Amount - q.Advance_Amount) AS Balance
from 
(
   select 
    JD.Cust_ID,
    DATEDIFF(dd, INV.Bill_Submit_Date, GetDate()) as Days_Submitted,
    SUM(INV.Bill_Amount) as Bill_Amount,
    SUM(INV.Advance_Amount) as Advance_Amount
   from Invoice INV
   join Job_Details JD on (JD.Job_ID = INV.Job_ID)
   where DATEDIFF(dd, INV.Bill_Submit_Date, GetDate()) > 0
   group by JD.Cust_ID, DATEDIFF(dd, INV.Bill_Submit_Date, GetDate())
) as q
join MstCustomer cust on (cust.Cust_ID = q.Cust_ID)
group by cust.Cust_Name
order by cust.Cust_Name;

Test Snippet :

--
-- Sample Data
--
declare @MstCustomer table (Cust_ID int primary key, Cust_Name varchar(30));
declare @Job_Details  table (Job_ID int primary key, Cust_ID int);
declare @Invoice table (Invoice_ID int identity(1,1) primary key, Job_ID int, Invoice_No varchar(30), Bill_Amount int, Advance_Amount int, Bill_Date date, Bill_Submit_Date date);
insert into @MstCustomer (Cust_ID, Cust_Name) values (1,'ABC'), (2,'DEF'), (3,'GHI'), (4,'JKL');
insert into @Job_Details (Job_ID, Cust_ID) values (1,1), (2,2), (3,3), (4,4);
insert into @Invoice (Job_ID, Invoice_No, Bill_Amount, Advance_Amount, Bill_Date, Bill_Submit_Date) values 
 (1,'7/18-19',300,100,GetDate(),GetDate())
,(1,'6/18-19',6000,1000,GetDate(),GetDate()-46)
,(1,'5/18-19',5000,0,GetDate(),GetDate()-46)
,(1,'4/18-19',4000,0,GetDate(),GetDate()-32)
,(1,'3/18-19',3000,0,GetDate(),GetDate()-32)
,(1,'2/18-19',2000,500,GetDate(),GetDate()-1)
,(1,'1/18-19',1000,500,GetDate(),GetDate()-1)

;

--
-- Query
--
select 
 cust.Cust_Name as "Customer Name",
 SUM(CASE WHEN q.Days_Submitted BETWEEN 1 AND 30 THEN q.Bill_Amount END) AS "1-30 Days Amount",
 SUM(CASE WHEN q.Days_Submitted BETWEEN 31 AND 45 THEN q.Bill_Amount END) AS "31-45 Days Amount",
 SUM(CASE WHEN q.Days_Submitted > 45 THEN q.Bill_Amount END) AS "Above 45 Days Amount",
 SUM(q.Bill_Amount - q.Advance_Amount) AS Balance
from 
(
   select 
    JD.Cust_ID,
    DATEDIFF(dd, INV.Bill_Submit_Date, GetDate()) as Days_Submitted,
    SUM(INV.Bill_Amount) as Bill_Amount,
    SUM(INV.Advance_Amount) as Advance_Amount
   from @Invoice INV
   join @Job_Details JD on (JD.Job_ID = INV.Job_ID)
   where DATEDIFF(dd, INV.Bill_Submit_Date, GetDate()) > 0
   group by JD.Cust_ID, DATEDIFF(dd, INV.Bill_Submit_Date, GetDate())
) as q
join @MstCustomer cust on (cust.Cust_ID = q.Cust_ID)
group by cust.Cust_Name
order by cust.Cust_Name;

Résultat :

Customer Name 1-30 Days Amount 31-45 Days Amount Above 45 Days Amount Balance
------------- ---------------- ----------------- -------------------- -------
ABC                       3000              7000                11000   19000