Group By, MAX() Most recent date 1

[dustyteter]

Here is my scenario

1 BUSINESS_ID can have multiple associated LICENSE_ID's. Both of these identifiers live in the same table as well as the CLOSURE_DT for each LICENSE_ID.

DATA EXAMPLE

BUS_ID LIC_CNTRL_ID LIC_CLSD_DATE
7 12165 04/05/1989
7 20494 09/30/1993
7 84565
14 40117 08/31/1995
14 42841 11/21/1996

what I need is to return bus_id's that would be considered closed due to the fact that all associated LIC_CNTRL_ID's have a LIC_CLSD_DATE
In the example above I would only return BUS_ID '14'

I also need to select the LIC_CNTRL_ID with the most recent LIC_CLSD_DATE

Does this make any sense?
Thanks for any help!!

 

[carp]

Try:

SELECT bus_id, lic_cntrl_id, lic_clsd_date
  FROM my_table
 WHERE (bus_id, lic_clsd_date) IN
    (SELECT bus_id, max(lic_clsd_date)
       FROM my_table
      WHERE bus_id NOT IN (SELECT bus_id
                             FROM my_table
                            WHERE lic_clsd_date IS NULL)
      GROUP BY bus_id);

The innermost query returns the businesses that are NOT closed. The middle query returns businesses and latest date for closed businesses. The outer query will get all of the information you are asking for.

Since this involves visiting your table three times, I hope your table isn't too large.

 

[r937]

try this:

select BUS_ID
     , max(LIC_CLSD_DATE) as latest_closedate
  from licences
group 
    by BUS_ID
having max(iif(isnull(LIC_CLSD_DATE),1,0)) = 0
   and max(LIC_CLSD_DATE) < date()

rudy

http://r937.com/

SQL Consulting

 

[dustyteter]

Thank you both for your replies!!

CARP, I tried your example and returned '0' rows. I'm looking into it more cause I think this may be close.

R937,
Your's looks like it could do something close to what I'm looking for as well but I'm using Oracle. I guess I should have mentioned that. Any other ideas?

Again, thanks to both of you

 

[r937]

oops, my bad, sorry

i gave you microsoft access syntax (which is what i use for quick tests -- if access can do it, just about every other database will, too)

still, i should have translated it back to ANSI SQL

does your level of oracle do CASE expressions?

select BUS_ID
     , max(LIC_CLSD_DATE) as latest_closedate
  from licences
group 
    by BUS_ID
having max(
        case when LIC_CLSD_DATE is null
             then 1 else 0 end
           ) = 0
   and max(LIC_CLSD_DATE) < current_date

for oracle, i believe you use SYSDATE instead of CURRENT_DATE

rudy

http://r937.com/

SQL Consulting

 

[dustyteter]

Thanks for you help Rudy!! That worked great! I really appreciate your help!

Have a great day!