Re: How to query for total days?

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:

member_id = 100
insurer_id = 200
eff_dt = 09/01/2002
term_dt = 09/02/2002

member_id = 100
insurer_id = 201
eff_dt = 09/29/2002
term_dt = 09/30/2002

I think your proposed solution would give 30 days when there are only 4 days of coverage.

Richard

Brian Peasland wrote:
>
> How about something like the following:
>
> SELECT member_id,
> 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
> GROUP BY member_id;
>
> HTH,
> Brian
>
> Richard Kuhler 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?
> >
> > Note that the same member could be covered by multiple insurers and the
> > coverages could overlap each other. However, each day should only be
> > counted once. For example, if member_id = 100 was covered by insurer_id
> > = 200 from the 1st to the 10th and and insurer_id = 300 from the 6th to
> > the 15th then that member was covered for 15 days (not 10 + 10 = 20
> > days). You can use any features you like but the final result must come
> > back from a single query with each member_id and the number of days.
> >
> > background information ...
> >
> > My current solution uses a pl/sql function to produce a bitmap mask for
> > each row in the table representing the days covered for the given
> > month. Then I use a user-defined aggregate function (data cartridge) to
> > produce the bitwise or of the masks grouped by member_id. Finally, a
> > pl/sql function is used to count the number of bits that are set in the
> > final aggregated mask.
> >
> > This all works as expected but it is VERY slow. The vast majority of
> > the time is spent processing the custom aggregate function. For
> > example, a MAX on the table by member_id only takes 5 minutes but my
> > custom aggregate takes 270 minutes. This seems to be a fundamental
> > problem with user-defined aggregates. There's nothing intensive about
> > the bitwise or [in ODCIAggregateIterate: mask = mask - bitand(mask,
> > value) + value]. I've used native compilation on the type and function
> > but that had no affect on performance. I also tried parallel execution
> > (yes I implemented ODCIAggregateMerge with 'parllel_enable') but that's
> > actually slightly slower on our 8 processor machine (the timing for MAX
> > was not parallel).
> >
> > Any ideas?
> >
> > Thanks,
> > Richard
Received on Wed Oct 16 2002 - 16:29:18 CDT