Re: How to query for total days?

On Wed, 16 Oct 2002 19:41:32 GMT, Richard Kuhler <noone_at_nowhere.com> wrote:

>I have the following problem to solve using Oracle 9.2:
>
>Assume a table exists with the following columns (and about 30 million
>rows):
>
>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?

Disclaimer: Can't see Oracle right now, and I'm not about to gen 30 million rows of test data. Since you didn't post DDL and INSERT statements, I don't feel bad about that. <g>

Counting is dead simple if you can transform a date range from this

 1 2002-01-01 2002-01-03

to this

 1  2002-01-01
 1  2002-01-02
 1  2002-01-03

A calendar table is really useful for stuff like this. Here's an example covering just the month of January 2002.

CREATE TABLE Test (
  member_id integer,
  eff_dt date,
  term_dt date
);

insert into test values (1, date '2002-01-01', date '2002-01-03');
insert into test values (1, date '2002-01-05', date '2002-01-07');
insert into test values (1, date '2002-01-06', date '2002-01-08');
insert into test values (2, date '2002-01-15', date '2002-01-30');
insert into test values (3, date '2002-01-03', date '2002-01-09');
insert into test values (3, date '2002-01-28', date '2002-01-31');

• Outside the range of the calendar table. insert into test values (99, date '2002-02-15', date '2002-02-15');

--In production code, guarantee no gaps. CREATE TABLE Calendar(
  Date DATE NOT NULL PRIMARY KEY
);

INSERT INTO Calendar VALUES (date '2002-01-01');
INSERT INTO Calendar VALUES (date '2002-01-02');
INSERT INTO Calendar VALUES (date '2002-01-03');
INSERT INTO Calendar VALUES (date '2002-01-04');
INSERT INTO Calendar VALUES (date '2002-01-05');
INSERT INTO Calendar VALUES (date '2002-01-06');
INSERT INTO Calendar VALUES (date '2002-01-07');
INSERT INTO Calendar VALUES (date '2002-01-08');
INSERT INTO Calendar VALUES (date '2002-01-09');
INSERT INTO Calendar VALUES (date '2002-01-10');
INSERT INTO Calendar VALUES (date '2002-01-11');
INSERT INTO Calendar VALUES (date '2002-01-12');
INSERT INTO Calendar VALUES (date '2002-01-13');
INSERT INTO Calendar VALUES (date '2002-01-14');
INSERT INTO Calendar VALUES (date '2002-01-15');
INSERT INTO Calendar VALUES (date '2002-01-16');
INSERT INTO Calendar VALUES (date '2002-01-17');
INSERT INTO Calendar VALUES (date '2002-01-18');
INSERT INTO Calendar VALUES (date '2002-01-19');
INSERT INTO Calendar VALUES (date '2002-01-20');
INSERT INTO Calendar VALUES (date '2002-01-21');
INSERT INTO Calendar VALUES (date '2002-01-22');
INSERT INTO Calendar VALUES (date '2002-01-23');
INSERT INTO Calendar VALUES (date '2002-01-24');
INSERT INTO Calendar VALUES (date '2002-01-25');
INSERT INTO Calendar VALUES (date '2002-01-26');
INSERT INTO Calendar VALUES (date '2002-01-27');
INSERT INTO Calendar VALUES (date '2002-01-28');
INSERT INTO Calendar VALUES (date '2002-01-29');
INSERT INTO Calendar VALUES (date '2002-01-30');
INSERT INTO Calendar VALUES (date '2002-01-31');

This view transforms the date range into a table of member id numbers and dates. As I wrote it, it's restricted to the range of the calendar table.

CREATE VIEW Dates_Covered AS
SELECT DISTINCT T1.member_id, T2.Date
FROM TEST AS T1
JOIN Calendar AS T2 ON (T2.Date BETWEEN T1.eff_dt AND t1.term_dt);

Now you can count by

SELECT Member_ID, Count(*)
FROM Dates_Covered
GROUP BY Member_ID;

I'm curious to see how that performs under Oracle.

-- 
Mike Sherrill
Information Management Systems