Solved

Customer payment report

Posted on 2011-03-03
6
154 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
string fuctions 4 25
Can > be used for a Text field 6 35
MS SQL BCP Extra Lines Between Records 2 14
sql server service accounts 4 19
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
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…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

813 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

10 Experts available now in Live!

Get 1:1 Help Now