[ad_1]
I have a table with arrivals and exits from a system with its time period. I also have a forecast of arrivals and exits. I would like to compute the starting and ending count of heads for the incomplete forecasted periods (ideally using merge and connect by).
Data to reproduce:
create table HEAD_COUNT
(
PERIOD_START DATE,
HEAD_COUNT_START NUMBER ,
HEAD_COUNT_END NUMBER ,
ARRIVAL NUMBER ,
EXITS NUMBER
);
Insert into HEAD_COUNT values ('01-DEC-18',0,0,13,275);
Insert into HEAD_COUNT values ('01-NOV-18',0,0,0,46);
Insert into HEAD_COUNT values ('01-OCT-18',0,0,6,61);
Insert into HEAD_COUNT values ('01-SEP-18',0,0,275,1292);
Insert into HEAD_COUNT values ('01-AUG-18',0,0,46,1790);
Insert into HEAD_COUNT values ('01-JUL-18',0,0,61,17);
Insert into HEAD_COUNT values ('01-JUN-18',0,0,1292,3);
Insert into HEAD_COUNT values ('01-MAY-18',0,0,1790,15);
Insert into HEAD_COUNT values ('01-APR-18',0,0,17,158);
Insert into HEAD_COUNT values ('01-MAR-18',0,0,3,9);
Insert into HEAD_COUNT values ('01-FEB-18',0,0,15,0);
Insert into HEAD_COUNT values ('01-JAN-18',0,0,158,4);
Insert into HEAD_COUNT values ('01-DEC-17',0,0,9,179);
Insert into HEAD_COUNT values ('01-NOV-17',250,186,0,64);
Insert into HEAD_COUNT values ('01-OCT-17',276,250,4,30);
Insert into HEAD_COUNT values ('01-SEP-17',638,276,179,541);
- Ending Headcount of a period = starting head count + arrivals -exits.
- Starting headcount of a period = ending headcount of the previous period
My initial attempt:
merge INTO head_count h USING
(SELECT period_start,
head_count_start,
head_count_end,
arrival,
exits
FROM head_count
CONNECT BY prior period_start = add_months(period_start, -1)
START WITH period_start = TRUNC(sysdate, 'Month')
)
src ON (h.period_start = src.period_start)
WHEN matched THEN
UPDATE
SET h.head_count_start = src.head_count_end,
h.head_count_end = h.head_count_start + h.arrival - h.exits
However, it is not producing the right results. Appreciate any help!
[ad_2]
لینک منبع
