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.

tips54Asked:
Who is Participating?
 
lluddenConnect With a Mentor Commented:
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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.