Help with sql statment please

curtmorehouse · #1 (**permalink**) 12-02-08, 19:10

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)
and
subtract the days(invoice_date) from the days(date_paid) on paid invoices (in table 1)
or
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?

Marcus_A · #2 (**permalink**) 12-02-08, 20:12

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.

tonkuma · #3 (**permalink**) 12-02-08, 22:25

Quote:

But it is hard to say exactly without seeing your DDL.

Yes, I agree.

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.)

Code:

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
;

Or, this:

Code:

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)
;

Internet Services

Web Development

Digital Marketing

Consumer Tech