Hello everyone...
I am fairly new to DB programming and am working on some examples which I have picked up from a couple of places. The database version is 10g R2.
Let me setup the data over here and I can explain my requirement.
create table table_1
(product_id varchar2(25),
region_id number,
event_id number
event_date date,
event_status number(1))
/
Now the data for this is ->
insert into table_1 values ('Prod-1',10, null, to_date('01-feb-2014','dd-mon-yyyy'),null)
/
insert into table_1 values('Prod-1',10, 1001, to_date('10-mar-2014','dd-mon-yyyy'), 1)
/
insert into table_1 values('Prod-1',10, 1001, to_date('20-mar-2014','dd-mon-yyyy'), 3)
/
insert into table_1 values('Prod-1',10,1002, to_date('01-apr-2014','dd-mon-yyyy'), 1)
/
insert into table_1 values('Prod-1',10, 1002, to_date('10-apr-2014','dd-mon-yyyy'), 3)
/
commit
/
So the table now holds the following data
select * from table_1;
PRODUCT_ID REGION_ID EVENT ID EVENT_DAT EVENT_STATUS
------------------------- ---------- -------- --------- ------------
Prod-1 10 01-FEB-14
Prod-1 10 1001 10-MAR-14 1
Prod-1 10 1001 20-MAR-14 3
Prod-1 10 1002 01-APR-14 1
Prod-1 10 1002 10-APR-14 3
Now, the requirement is as follows:
The above are start and end dates for events on a product at a specified region. The event_status column identifies the start and end dates. Event_status = 1, for the start date and event_status=3, for the end date.
Now a new event is coming in starting from 21st mar and ending on 31st mar.
The required output is for a product id / region; if there are events that are ending and then starting on subsequent days, for example, 1001 finishes on 20th march, but now the new event is starting on 21st march.. and the new event is finishing on 31st mar and event 1002 is starting on 01-apr.... and so on.
The required output to this is as follows:
PRODUCT_ID REGION_ID EVENT_MIN_DATE EVENT_MAX_DATE
------------------------- ---------- -------------- --------------
Prod-1 10 10-MAR-14 10-APR-14
The output should give for a product ID / region, when events are immediately following each other, output the min start date and the max end date for all such "back to back" events.
Now I have written code for this but it goes into a purely "for loop" implementation in PLSQL... But my lead tells me that though the results are correct; the PLSQL implementation is not the most effective and efficient way.
Can someone help me with forming the query? I have tried using min/max analytical functions but it gives me the start and end dates even if my events are not "back to back" or preceeding/succeeding each other... so my query output is not entirely correct.
Am reading up on the MODEL clause but would appreciate if someone could help me with this query... or any other better way to implement this event if PLSQL can be used. The database version is 10g R2.
Thanks
K
P.S - The number of such back to back events is restricted to 4 and the events could be created in any order. But if someone could help me with the above scenario; am sure I could make the query cater to any change in sequence. :-)