Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Time Span?

712463Oct 15 2009 — edited Oct 15 2009
I am trying to get the net days for a time span, starting from first step through to the last step. I have a few issues that I have to account for:
• The problem is overlap time. Some steps overlap; with their start days coming before the end of the previous step.
• There could be gaps from end of 1 step till start of the next.

I know I could min and max step days or count total days from table but that does not take the overlap into consideration. I have provided an example below.
CREATE TABLE claims_step_table (  
table_id       INTEGER PRIMARY KEY, 
record_id      NUMBER,  
step_code      VARCHAR2(20 Byte),
start_date     DATE,
end_date       DATE,
days           NUMBER
) 


truncate table claims_step_table;
INSERT INTO claims_step_table VALUES (1,  123456,'P96','01-JAN-09','10-JAN-09',10);
INSERT INTO claims_step_table VALUES (2,  123456,'L61','10-JAN-09','15-JAN-09',5);
INSERT INTO claims_step_table VALUES (3,  123456,'H45','12-JAN-09','20-JAN-09',8);
INSERT INTO claims_step_table VALUES (4,  123456,'O87','10-FEB-09','15-FEB-09',5);
COMMIT;


P96      |----------- 10 --------|
L61                              |-----5-----|
H45                                   |--------8----------|
O87                                                                      |-----5-----|
So the net should be 25 days because there is a 3 day overlap between step L61 and H45. Thanks for your help ahead of time.

Rich


GOOD CATCH, my fault record #4 should have been in FEB, cut and paste error.

Edited by: LostOne on Oct 15, 2009 8:19 AM
This post has been answered by Hoek on Oct 15 2009
Jump to Answer

Comments

Mtshepana

Why is there so much ephasis on JET not being a framework?

1 - 1
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Nov 12 2009
Added on Oct 15 2009
13 comments
3,845 views