Hi,
I have a problem while filling the missing records using last value for one of the trending report. I'm able to do most of the logic. I tried different ways to achieve the required output. But no luck. Let me give some history.
everyday we will capture case movement from one queue to another queue from hour 1 to 7. If we don't have any cases then we will carry forward the previous queue no and case count from previous day. (ex: see the output below lines 6,7). My problem is per a day if i have more than one queue and when i do carry forward the previous day last records. In this scenario the first set of records is coming correctly, For second set of records I'm getting wrongly.
please see the output coming wrongly.
Please see the data and query.
create or replace view vw_case as
select to_date(last_upd_tms,'dd-mon-rrrr')last_upd_tms,hour hr,previous_queue_id,current_queue_id,case_cnt cnt from(
SELECT '07-APR-12' last_upd_tms, 1 HOUR,0 previous_queue_id, 6 current_queue_id, 21 case_cnt FROM dual
UNION ALL
select '09-APR-12', 2, 0, 2, 4 from dual
UNION ALL
select '10-APR-12', 2, 0, 8, 8 from dual
UNION ALL
select '10-APR-12', 4 ,0 ,1, 9 FROM dual
UNION ALL
SELECT '10-APR-12', 5 ,8, 1, 11 FROM dual
UNION ALL
select '10-APR-12', 5 ,0, 8, 6 from dual
UNION ALL
SELECT '10-APR-12' ,7, 1, 8, 17 FROM dual
UNION ALL
SELECT '10-APR-12' ,7, 0, 1, 0 FROM dual)
;
create or replace view vw_dates as
SELECT current_queue_id, last_upd_tms,hr
from
(
SELECT DISTINCT current_queue_id,b.last_upd_tms
FROM vw_case a,( select trunc(sysdate-(rownum-1)) last_upd_tms from all_objects
WHERE ROWNUM <= ( SELECT to_number(trunc(SYSDATE)- MIN(trunc(last_upd_tms)))+1
FROM vw_case)
) b
WHERE b.last_upd_tms=A.last_upd_tms (+))A,
( SELECT ROWNUM hr FROM all_objects WHERE ROWNUM <= 6 ) b
ORDER BY last_upd_tms, b.hr;
--sql query to fill the records
select
rn,
last_upd_tms,
hr,
last_value(a.cnt ignore nulls) over (order by last_upd_tms, old_queue,hr)
new_case_cnt,
last_value(a.current_queue_id ignore nulls) over (order by last_upd_tms,
old_queue, hr) new_current_queue_id--,current_queue_id,old_queue
from
(
select distinct
rownum rn,
A.last_upd_tms,
a.hr,lag(cnt,1,0) over (order by a.last_upd_tms,a.hr) lead_cnt,
b.current_queue_id,
b.cnt,
A.current_queue_id old_queue
FROM vw_dates A,vw_case b
where a.last_upd_tms =b.last_upd_tms(+)
and a.hr =b.hr(+)
and a.current_queue_id= b.current_queue_id(+)
order by
last_upd_tms,
old_queue,
hr)A
order by
last_upd_tms,
old_queue,
hr
--input
LAST_UPD_TMS HR PREVIOUS_QUEUE_ID CURRENT_QUEUE_ID CNT
07-APR-12 00:00:00 1 0 6 21
09-APR-12 00:00:00 2 0 2 4
10-APR-12 00:00:00 4 0 1 9
10-APR-12 00:00:00 5 8 1 11
10-APR-12 00:00:00 7 0 1 0
10-APR-12 00:00:00 2 0 8 8
10-APR-12 00:00:00 5 0 8 6
10-APR-12 00:00:00 7 1 8 17
--output.
last_upd_tms hr case_cnt queue_id
--------------------------- --- ---------- --------------
07-APR-12 00:00:00 1 21 6
07-APR-12 00:00:00 2 21 6
07-APR-12 00:00:00 3 21 6
07-APR-12 00:00:00 4 21 6
07-APR-12 00:00:00 5 21 6
07-APR-12 00:00:00 6 21 6 -- line 6
08-APR-12 00:00:00 1 21 6 -- line 7
08-APR-12 00:00:00 2 21 6
08-APR-12 00:00:00 3 21 6
08-APR-12 00:00:00 4 21 6
08-APR-12 00:00:00 5 21 6
08-APR-12 00:00:00 6 21 6
09-APR-12 00:00:00 1 21 6
09-APR-12 00:00:00 2 4 2
09-APR-12 00:00:00 3 4 2
09-APR-12 00:00:00 4 4 2
09-APR-12 00:00:00 5 4 2
09-APR-12 00:00:00 6 4 2 -- this is previous day data
10-APR-12 00:00:00 1 4 2 -- this first queue of data for 10.apr.2012
10-APR-12 00:00:00 2 4 2
10-APR-12 00:00:00 3 4 2
10-APR-12 00:00:00 4 9 1
10-APR-12 00:00:00 5 11 1
10-APR-12 00:00:00 6 11 1
10-APR-12 00:00:00 1 11 1 -- suppose to be 1 4 2 --second set of data
10-APR-12 00:00:00 2 8 8
10-APR-12 00:00:00 3 8 8
10-APR-12 00:00:00 4 8 8
10-APR-12 00:00:00 5 6 8
10-APR-12 00:00:00 6 6 8
Please help me out how to correct this logic. In advance thanks a lot.
Regards,
Venkat.