Re: How to query for total days?

Richard,  

> I think you've assumed that the eff_dt/term_dt ranges are all
> adjacent/overlapping which they aren't. What if the data is:

You are correct. I did make that assumption, which means that my query won't work.  

You could modify the query slightly to get the number of days for each term as follows:

SELECT member_id, GREATEST(TO_DATE('09/01/2002','MM/DD/YYYY'),eff_dt) AS start_date,
LEAST(TO_DATE('09/30/2002','MM/DD/YYYY'),term_dt)- GREATEST(TO_DATE('09/01/2002','MM/DD/YYYY'),eff_dt) AS num_days FROM table;

But then, you'd have to check if terms are "connected" or overlap. You can do this with PL/SQL, or as others have suggested, with some temporary tables.

HTH,
Brian Received on Thu Oct 17 2002 - 09:27:19 CDT