Solved

# Customer payment report

Posted on 2011-03-03
152 Views
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
Question by:tips54
• 5

LVL 18

Accepted Solution

lludden earned 400 total points
ID: 35028166
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

Author Comment

ID: 35028884
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

Author Comment

ID: 35028889
Incorrect syntax near '7'.
0

Author Comment

ID: 35029063
the syntax issue was with the column name, Can't start with a number
0

Author Comment

ID: 35198935
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

Author Closing Comment

ID: 37247574
just to close
0

## Featured Post

### Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backupâ€¦
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed