Re: How to query for total days?

In article <Mhjr9.167514$U7.45873199_at_twister.socal.rr.com>, "Richard Kuhler" <noone_at_nowhere.com> wrote:

> member_id number
> insurer_id number
> eff_dt date -- no time component just the day
> term_dt date -- no time
> component just the day
>
> Q. How many days was each member covered for the month of September
> 2002?
>

Offhand I'd say you should create a table of days and count how many of them are between and eff_dt and a term_dt for some row belonging to the member.

create table sept_days( dt date )

...insert 30 rows...

select member_id, count(*)
from the_first_table i, sept_days d
where d.dt between i.eff_dt and i.term_dt group by member_id Received on Wed Oct 16 2002 - 19:48:44 CDT