Solved

Customer payment report

Posted on 2011-03-03
6
153 Views
Last Modified: 2012-05-11
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
Comment
Question by:tips54
  • 5
6 Comments
 
LVL 18

Accepted Solution

by:
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

by:tips54
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

by:tips54
ID: 35028889
Incorrect syntax near '7'.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

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

Author Comment

by:tips54
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

by:tips54
ID: 37247574
just to close
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

912 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now