Customer Invoice Receipts Average Days Late
Posted: Wed Apr 19, 2017 8:42 pm
Had a request to get the average # of days late for each customer for last year and this year so I used the code below. Not 100% if the tables are correct so I'm having to manually verify but I used Cash Receipts Detail in SQL Profiler to capture the tables required.
Code: Select all
;With LastYearRes AS (
select
IH.Customer, C.Name AS 'Customer Name'
, AVG(Cast(DATEDIFF(Day, IH.Due_Date, R.Receipt_Date) as float)) AS 'Difference in Days'
, COUNT(DISTINCT IH.Document) AS 'Invoices'
From Invoice_Receipt IR
Inner Join Invoice_Header IH ON IH.Document = IR.Invoice
Inner Join Receipt R ON IR.Receipt = R.Receipt
Inner Join Customer C ON C.Customer = IH.Customer
WHERE Year(IH.Due_Date) = Year(GetDate()) - 1
Group By IH.Customer, C.Name
),
CurrentYearRes AS (
select
IH.Customer, C.Name AS 'Customer Name'
, AVG(Cast(DATEDIFF(Day, IH.Due_Date, R.Receipt_Date) as float)) AS 'Difference in Days'
, COUNT(DISTINCT IH.Document) AS 'Invoices'
From Invoice_Receipt IR
Inner Join Invoice_Header IH ON IH.Document = IR.Invoice
Inner Join Receipt R ON IR.Receipt = R.Receipt
Inner Join Customer C ON C.Customer = IH.Customer
WHERE Year(IH.Due_Date) = Year(GetDate())
Group By IH.Customer, C.Name
)
Select C.Name
, LYR.[Difference in Days] AS '2016 Average Days Past Due Date'
, CYR.[Difference in Days] AS '2017 Average Days Past Due Date'
, LYR.Invoices AS '# of 2016 Invoices'
, CYR.Invoices AS '# of 2017 Invoices'
From Customer C
Left Join LastYearRes LYR ON LYR.Customer = C.Customer
Left Join CurrentYearRes CYR ON CYR.Customer = C.Customer
WHERE NOT COALESCE(LYR.Customer, CYR.Customer, NULL) IS NULL
Order By C.Name