Selecting First Inv# Used for each Customer

[SonOfZeus]

I'm trying to write a select statement that lists the first Invoice sent out to each of our customers. The catch is that the lowest Invoice number with that customer ID may not be the first sent out to a customer.

i.e. We are invoicing a customer in two different systems and require a union query. Both files have common customer numbers and an invoice date

 

[SantaMufasa]

Zeus's Boy,

Here is code that will give you the earliest invoice for each customer from two different (UNIONed) tables:

Section 1 -- Sample data from two invoice tables:

select inv_no, to_char(inv_date,'dd-MON-yyyy hh24:mi:ss')inv_date,cust_no
from sys_a_invoices;

    INV_NO INV_DATE                CUST_NO
---------- -------------------- ----------
         1 12-FEB-2005 10:21:00         10
         4 14-FEB-2005 10:21:29         10
         3 13-FEB-2005 10:22:48         20

select inv_no, to_char(inv_date,'dd-MON-yyyy hh24:mi:ss')inv_date,cust_no
from sys_b_invoices;

    INV_NO INV_DATE                CUST_NO
---------- -------------------- ----------
         2 12-FEB-2005 22:25:09         20
         5 15-FEB-2005 10:25:26         20

Section 2 -- Code to find earliest invoice for each Customer:

select inv_no, to_char(inv_date,'dd-MON-yyyy hh24:mi:ss')inv_date,cust_no
from (select * from sys_a_invoices
      union
      select * from sys_b_invoices)
where (inv_date,cust_no) in
     (select min(inv_date),cust_no
     from (select * from sys_a_invoices
           union
           select * from sys_b_invoices)
     group by cust_no)
order by cust_no;

    INV_NO INV_DATE                CUST_NO
---------- -------------------- ----------
         1 12-FEB-2005 10:21:00         10
         2 12-FEB-2005 22:25:09         20

Let us know if this resolves your need.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[SonOfZeus]

This is the direction I ended up following, for something so simple, was painful to write. Thanks

 

[SantaMufasa]

Zeus's Boy,

You need to strive to become a masochist...This stuff is fun !

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.