SUB QUIVERY 1

[cristi22]

Hello All –

Please help!

I need to select members with APP = F and app not in SFHS or SFHF

Input Data:

Empl      app       marit
9990003   F           S    
9990003   SFHS   M    
9990003   SFHS   S    
9990003   SFHS   S

My Statement (does not work)

SELECT   employer , period,
(case app when  'F' then (case MARIT when 'M'  then 'STANDARD_FAMILY' else 'STANDARD_SINGLE' end) else '' end) plandesc,
Count(distinct ssn) tot_cnt FROM umass_contr
        WHERE
period = date('2005-07-01') and  APP IN ('F' ) [COLOR=red]and ssn not in ( select  distinct ssn from umass_contr  where app in ('SFHS','SFHF')  and period = date('2005-07-01')   )                              [/color] GROUP BY employer,  period, (case app when   'F' then (case MARIT when 'M'  then 'STANDARD_FAMILY' else 'STANDARD_SINGLE' end) else ''end)

It should be some sort of an inner join may be?

This takes care of SFHS & SFHS records

SELECT   employer , period, (case app when 'SFHS' then 'HIGH_SINGLE' when 'SFHF' then 'HIGH_FAMILY'  else '' end) plandesc, Count(distinct ssn) tot_cnt 
FROM umass_contr         WHERE period = date('2005-07-01') and  APP IN ('SFHS', 'SFHF'  ) GROUP BY employer,  period, 
(case app when 'SFHS' then 'HIGH_SINGLE'
when 'SFHF' then 'HIGH_FAMILY' else '' end)

THANKS FOR YOUR HELP!
Cristi

 

[cristi22]

I apologize, it works, but the response time is very slow

 

[ddiamond]

When using sub-queries, I often get better response times from exists clauses instead of in clauses. You may want to try the following:

SELECT   employer , period,
(case app when  'F' then (case MARIT when 'M'  then 'STANDARD_FAMILY' else 'STANDARD_SINGLE' end) else '' end) plandesc,
Count(distinct ssn) tot_cnt FROM umass_contr
        WHERE
period = date('2005-07-01') and  APP IN ('F' ) 
[blue]and not exists (
  select *
  from umass_contr as umass_contr2
  where umass_contr.ssn = umass_contr2.ssn 
    and umass_contr2.period = date('2005-07-01') 
    and umass_contr2.app in ('SFHS','SFHF'))[/blue]
GROUP BY employer,  period, (case app when   'F' then (case MARIT when 'M'  then 'STANDARD_FAMILY' else 'STANDARD_SINGLE' end) else ''end)

This assumes that you have an index on either SSN or period. Otherwise it won't make a difference.

 

[cristi22]

it's a bit faster, i do have indexes on both
thanks a lot for your help!