• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 168
  • Last Modified:

Customer payment report

I need to some help with writing a query against a couple of tables to create a report that shows statically what time frames customers normally make payments to us. basically see how many was paid 7 days early, On time, or 7 days late etc.
 Table1 has: Invoice#, CutomerID, Net_due_date
 Table2 has: Invoice#, Receipt#, Payment_date

Lets say for 2010 customer X 50 invoices paid. what days
Expected output:
Cust#          Average#Days      7DaysEarly          On-time            7DaysLate       14DaysLate
1001            12                          0                           2                      8                      10
I need to be able to get the output by customers or for the whole company.

0
tips54
Asked:
tips54
  • 5
1 Solution
 
lluddenCommented:
SELECT T1.CustomerID,
   AVG(DATEDIFF(DAY,T1.Net_Due_Date,T2.Payment_Date)) AS Average_Days,
   SUM(CASE WHEN DATEDIFF(DAY,T1.Net_Due_Date,T2.Payment_Date) BETWEEN -7 AND -1 THEN 1 ELSE 0 END) AS 7DaysEarly,
   SUM(CASE WHEN DATEDIFF(DAY,T1.Net_Due_Date,T2.Payment_Date) = 0 THEN 1 ELSE 0 END) AS OnTime,
   SUM(CASE WHEN DATEDIFF(DAY,T1.Net_Due_Date,T2.Payment_Date) BETWEEN 1 AND 7 THEN 1 ELSE 0 END) AS 7DaysLate,
   SUM(CASE WHEN DATEDIFF(DAY,T1.Net_Due_Date,T2.Payment_Date) >=4 THEN 1 ELSE 0 END) AS 14DaysLate
FROM Table1 T1
 INNER JOIN Table2 T2 ON T1.CustomerID = T2.CustomerID
GROUP BY T1.CustomerID

You can change the betweens and stuff to give the numbers you want.

0
 
tips54Author Commented:
Thanks lludden.
I'm getting a syntax error in the first case statement.
also would this give me a number of invoices paid in each case statement?
0
 
tips54Author Commented:
Incorrect syntax near '7'.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
tips54Author Commented:
the syntax issue was with the column name, Can't start with a number
0
 
tips54Author Commented:
The whole purpose of this report is to see what are the probability of a customer paying invoices base on there previous pattern.
Now the second part of this report is to apply those percentage against existing invoices to see when and what to expect.
I have a table with CustomerID, AvgDayspaidOver1yr, #invoices,  %paid7DaysEarly, %paidonTime, %paid7DaysLate , %Paid14DaysLate.
I also have a table with Open invoice data: CustomerID, invoice#, InvoiceDate,  NetDuedate, TotalAmt
 I need help with a query that shows probability of Customer X paying the 3 invoices that are due in various days (1 is 3 days pass do, 2 are due in 21 days).
0
 
tips54Author Commented:
just to close
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now