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!

Analytic Question with lag and lead

oakstream1024Oct 26 2011 — edited Oct 29 2011
Hello,
I'm working on tracking a package and the number of times it was recorded in an office. I want to see the start and end dates along with number of occurrences (or records) during the start/end dates. I'm pretty confident I can get the start end date correct but it is the number of occurences that is the issue.

Essentially, I want to build a time line start and end_dates and the number of times the package was recorded in the office.
I am fumbling around with using the lag and lead analytic to build start/end dates along with the count of occurrences during that period.
I've been using analytics lag and lead feature and can pretty much get the start and end dates setup but having difficulty determining count ---count(*) within the analytic. (I think I can do it outside of the analytic with a self join but performance will suffer). I have millions of records in this table.

I've been playing with the windowing using RANGE and INTERVAL days but to no avail. When I try this and count(*) (over partition by package_ID, location_office_id order by event_date range ......) I can calculate the interval correctly by subtracting the lead date - current date, however,
the count is off because when I partition the values by package_id, location_office_id I get the third group of package 12 partitioned with the first group of package 12 (or in same window) because they are at the same office. However, I want to treat these separately because the package has gone to a different office in be-tween.

I've attached the DDL/DML to create my test case. Any help would be appreciated.

--Current
package_id, location_office_ID. event_date
12 1 20010101
12 1 20010102
12 1 20010103
13 5 20010102
13 5 20010104
13 5 20010105
13 6 20010106
13 6 20010111
12 2 20010108
12 2 20010110
12 1 20010111
12 1 20010112
12 1 20010113
12 1 20010114


--Needs to look like
package_id location_office_id start_date end_date count
12 1 20010101 20010103 3
12 2 20010108 20010110 2
12 1 20010111 20010114 4
13 5 20010102 20010105 3
13 6 20010106 20010111 2




create table test (package_id number, location_office_id number,event_date date);

insert into test values (12,1,to_date('20010101','YYYYMMDD'));
insert into test values (12,1,to_date('20010102','YYYYMMDD'));
insert into test values (12,1,to_date('20010103','YYYYMMDD'));
insert into test values (13,5,to_date('20010102','YYYYMMDD'));
insert into test values (13,5,to_date('20010104','YYYYMMDD'));
insert into test values (13,5,to_date('20010105','YYYYMMDD'));
insert into test values (13,6,to_date('20010106','YYYYMMDD'));
insert into test values (13,6,to_date('20010111','YYYYMMDD'));
insert into test values (12,2,to_date('20010108','YYYYMMDD'));
insert into test values (12,2,to_date('20010110','YYYYMMDD'));
insert into test values (12,1,to_date('20010111','YYYYMMDD'));
insert into test values (12,1,to_date('20010112','YYYYMMDD'));
insert into test values (12,1,to_date('20010113','YYYYMMDD'));
insert into test values (12,1,to_date('20010114','YYYYMMDD'));
commit;

--I'm trying something like

select package_id, location_office_id, event_date,
lead(event_date) over (partition by package_id, location_office_id order by event_date) lead_event,
count(*) over (partition by package_id, location_office_id order by event_date) rcount -- When I do this it merges the window together for package 12 and location 1 so I get the total, However, I want to keep them separate because the package moved to another office in between).

Appreciate your help,
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Nov 23 2011
Added on Oct 26 2011
2 comments
1,816 views