Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2008
    Posts
    34

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

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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)
    ;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •