Thread: Help with sql statment please
12-02-08, 19:10 #1Registered User
- Join Date
- Sep 2008
Unanswered: Help with sql statment please
I have two tables in the same database. One is a table containing info about current invoices, and the other has info about past invoices. All info in the second table relates to invoices that have been paid. the info in the first table relates to invoices that may have been completely paid, may have a partial payment, or may not have had a payment at all. Both tables have an invoice_date column and a date_paid column whether the invoice has been paid or not (it's = to the invoice date if no payments have been received)
I'd like a sql statement that will give me the average days to pay the invoice, grouped by customer.
So far, I can almost get this by using three seperate sql statments that do the following...
subtract the days(invoice_date) from the days(date_paid) on paid invoices (in table 2)
subtract the days(invoice_date) from the days(date_paid) on paid invoices (in table 1)
subtract the days(invoice_date) from the days(current_date) on unpaid invoices (in table 1)
The problem with 3 seperate sql statmenets is that I am unable to sort the results.
Can anyone offer some help with the sql statement needed?
12-02-08, 20:12 #2Registered User
- Join Date
- May 2003
I would probably try to do a UNION ALL with a separate query on each table (basically the same query). But it is hard to say exactly without seeing your DDL.M. A. Feldman
IBM Certified DBA on DB2 for Linux, UNIX, and Windows
IBM Certified DBA on DB2 for z/OS and OS/390
12-02-08, 22:25 #3Registered User
But it is hard to say exactly without seeing your DDL.
- Join Date
- Feb 2008
Anyhow, with the information currently available from OP, (and using my guess) I want to try like this:
(I assumed that date_paid IS NULL if not paid.)
SELECT customer_id AS customer , AVG(CAST(days(COALESCE(date_paid, current_date)) - days(invoice_date) AS DEC(7,2) ) ) AS "avg. days to pay the invoice" FROM (SELECT customer_id , invoice_date , date_paid FROM current_invoice UNION ALL SELECT customer_id , invoice_date , date_paid FROM past_invoice ) Q GROUP BY customer_id ;
SELECT COALESCE(ci.customer_id, pi.customer_id) AS customer , AVG(CAST(days(COALESCE(ci.date_paid, pi.date_paid, current_date)) - days(COALESCE(ci.invoice_date, pi.invoice_date)) AS DEC(7,2) ) ) AS "avg. days to pay the invoice" FROM current_invoice ci FULL OUTER JOIN past_invoice pi ON ci.customer_id = pi.customer_id AND ci.invoice_id = pi.invoice_id GROUP BY COALESCE(ci.customer_id, pi.customer_id) ;