Re: How to query for total days?

Hi,
I have done only limited testing on this due to the excessive amount of typing, but I believe there is a solution, using the DECODE and SIGN statement (quite intensively :-) ). The query will be long, but simple and fast (I hope) to execute. Here is the detailed "how-to":
1) As you may already know, the statement "if( a<=b ) then show 1, otherwise show 0" be written as:

    decode( sign( b-a), -1, 0, 1)
2) To make it a bit more complex "if( a<=b<=c ) then show 1, otherwise show 0" is:

    decode( sign( b-a), -1, 0, decode( sign( c-b ), -1, 0, 1)) 3) Now, let's imagine that "b" is a day in September, say TO_DATE('09/10/2002','MM/DD/YYYY'), "a" is the eff_dt, and "b" is term_dt. Then the decode from 2) will return 1 for each row (grouping by member is assumed) that covers September 10 4) Let's wrap 3) in a sum() - this will give us a count of coverages overlapping over Sep. 10. Applying the sign() around the sum will give us 1 if there are ANY intervals covering Sept. 10 and 0 if there are none. 5) This step is easy to guess - build the "sign(sum(decode..." statement for each day in September and use "+" to add them up. 6) You are done! Here is a piece of the complete query: select member_id,

sign( sum(decode( sign(to_date( '2002-09-01', 'YYYY-MM-DD') - eff_dt ),

-1, 0, decode( sign( term_dt - to_date( '2002-09-01', 'YYYY-MM-DD') ),

-1, 0, 1) ) ) ) +

sign( sum(decode( sign(to_date( '2002-09-02', 'YYYY-MM-DD') - eff_dt ),

-1, 0, decode( sign( term_dt - to_date( '2002-09-02', 'YYYY-MM-DD') ),

-1, 0, 1) ) ) ) +

. . .

sign( sum(decode( sign(to_date( '2002-09-30', 'YYYY-MM-DD') - eff_dt ),

-1, 0, decode( sign( term_dt - to_date( '2002-09-30', 'YYYY-MM-DD') ),

-1, 0, 1) ) ) ) days_covered

from test_1

• Note: you may want to include the where clause on the eff_dt <= Sept 30 and term_dt >= Sept 1 to limit the number of rows. Otherwise you are looking at a full table scan.

group by member_id;

Good luck!
A. Fox

"Richard Kuhler" <noone_at_nowhere.com> wrote in message news:Mhjr9.167514$U7.45873199_at_twister.socal.rr.com...
> 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 - 20:31:03 CDT