Have a question about getting max() or null from date and more.

[DarkWorlds]

My first questions involves union

I am joining months of tables together all with the same fields. I am assuming union would be the best shot? But I need something that is quick.

Secondly when this is all said and done (all 12 tables into one) I need to get the max inactive date, and max active date. Well active date is no issue.

But the inactive date may have a date or it may be null. So how do I select the max date but if its null select that instead?

Sample

Client Name | Active Date | Inactive Date

Bob|5/12/2007|6/15/2007
Bob|7/18/2007|null
Sam|4/28/2007|5/20/2007
Sam|9/30/2007|10/15/2007

So the query should pull up

Bob|7/18/2007|null
Sam|9/30/2007|10/15/2007

 

[hoinz]

For getting 12 tables into one, I think UNION is a good choice.
(Not short in typing, but fast in executing)

For your second question, a bit more of explanation is needed:
What are your criteria for finding max(inactive date) ?
Or in other words: Why is null greater than 6/15/2007 ?
One guess could be:
For each name, you want to get the whole line with the latest active date, no matter what inactive date is.
Is this guess correct? If so, it should give you an idea what to try.

hope this helps

 

[DarkWorlds]

Well I guess im having a brain fart of epic magnatude today, but i guess finding the one with the most current active date would be fine. How would I do this?

Like i said, big brain fart today (i need vaca, lol)

 

[SantaMufasa]

Frankly, Dark, you can have what you originally requested. Here are a confirmation of your data and the code you wanted:

col client_name heading "Client|Name" format a7
col Active_Date heading "Active|Date"
col Inactive_Date heading "Inactive|Date"
select * from dark;

Client  Active    Inactive
Name    Date      Date
------- --------- ---------
Bob     12-MAY-07 15-JUN-07
Bob     18-JUL-07
Sam     28-APR-07 20-MAY-07
Sam     30-SEP-07 15-OCT-07

select *
  from dark
 where (client_name,nvl(inactive_date,trunc(sysdate)+10000)) in
       (select client_name
              ,max(nvl(inactive_date,trunc(sysdate)+10000))
          from dark
         group by client_name)
/

Client  Active    Inactive
Name    Date      Date
------- --------- ---------
Bob     18-JUL-07
Sam     30-SEP-07 15-OCT-07

Let us know if this is what you wanted.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[DarkWorlds]

Thanks this worked as I needed it. Now time to post a new thread for help. Thanks all.