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