I have a query wherein I am using LEAD function to find out time difference. Here is the query:-
SELECT
ast.sgs_first_name,
ast.new_sga_msg_req_guid,
SUM
(CASE
WHEN ast.ttr_name = ‘ALLOCATION’
THEN ( EXTRACT (DAY FROM ast.assigned_ttr_time)
* 24
* 60
* 60
* 1000
)
+ ( EXTRACT
(HOUR FROM ast.assigned_ttr_time)
* 60
* 60
* 1000
)
+ ( EXTRACT
(MINUTE FROM ast.assigned_ttr_time)
* 60
* 1000
)
+ ( EXTRACT
(SECOND FROM ast.assigned_ttr_time)
* 1000
)
ELSE 0
END
) sum_assigned_ttr_time_msecs
FROM (WITH this_info_capture AS
(SELECT sbic.ic_field_3, sbic.ic_type, sbic.ic_msg_req_guid,
sbic.ic_msg_guid
FROM sbdba0.sb_info_capture sbic
WHERE sbic.ic_cap_dt >=
TO_DATE (’09/27/2008 03:00 AM’,
‘MM/DD/YYYY HH:MI AM’
)
AND sbic.ic_cap_dt <
TO_DATE ('09/28/2008 03:00 AM',
'MM/DD/YYYY HH:MI AM'
)
AND (sbic.ic_type IN (5, 12, 14, 15))
AND sbic.ic_id IN
('DIRECTCONNECT',
'DIRECTCONNECTFAIL',
'DC_SUCCESS_CLIENT',
'DC_FAIL_CLIENT'
)
)
SELECT old_asa.asa_tlm_dt allocation_start_dt,
LEAD (old_asa.asa_tlm_dt,
1,
old_asa.asa_tlm_dt
) OVER (PARTITION BY old_asa.asa_a_uid, ttr.ttr_name ORDER BY old_asa.asa_tlm_dt ASC)
allocation_end_dt,
LEAD (old_asa.asa_tlm_dt,
1,
old_asa.asa_tlm_dt
) OVER (PARTITION BY old_asa.asa_a_uid, ttr.ttr_name ORDER BY old_asa.asa_tlm_dt ASC)
- old_asa.asa_tlm_dt assigned_ttr_time,
sgs.sgs_first_name,
ast.new_sga_msg_req_guid
CASE
WHEN curstate.as_name =
'IDLE_CONNECTED'
THEN old_asa.asa_duration_ms
ELSE 0
END idle_connected_time,
CASE
WHEN curstate.as_name =
'IDLE_DISCONNECTED'
AND ttr.ttr_name = 'STATE'
THEN old_asa.asa_duration_ms
ELSE 0
END idle_disconnected_time,
CASE
WHEN curstate.as_name = 'BUSY'
AND ( this_info_capture.ic_field_3 = 'DC_SUCCESS'
OR this_info_capture.ic_type = 14
)
THEN old_asa.asa_duration_ms
ELSE 0
END dc_talk_time,
CASE
WHEN curstate.as_name = 'HANDOFF'
AND ( this_info_capture.ic_field_3 = 'DC_SUCCESS'
OR this_info_capture.ic_type = 14
)
THEN old_asa.asa_duration_ms
ELSE 0
END whisper_time,
CASE
WHEN curstate.as_name = 'ACW'
THEN old_asa.asa_duration_ms
ELSE 0
END acw_time
FROM sbdba0.sb_agent ag JOIN sbdba0.sb_agent_state_activity old_asa
ON (ag.a_sg_uid = 4815019740 AND old_asa.asa_a_uid = ag.a_uid
)
JOIN sbdba0.sb_transition_type_ref ttr
ON (old_asa.asa_transition_type = ttr.ttr_uid)
JOIN sbdba0.sb_agent_state_record old_asr
ON old_asr.asr_uid = old_asa.asa_old_asr_uid
JOIN sbdba0.sb_agent_state_record new_asr
ON new_asr.asr_uid = old_asa.asa_new_asr_uid
JOIN sbdba0.sb_agent_state_ref curstate
ON old_asr.asr_as_uid = curstate.as_uid
JOIN sbdba0.sb_skill_group_site_allocation old_sgsa
ON old_asr.asr_sgsa_uid = old_sgsa.sgsa_uid
JOIN sbdba0.sb_skill_group_site_allocation new_sgsa
ON new_asr.asr_sgsa_uid = new_sgsa.sgsa_uid
JOIN sbdba0.sb_skill_group_allocation old_sga
ON old_sgsa.sgsa_sga_uid = old_sga.sga_uid
JOIN sbdba0.sb_skill_group_allocation new_sga
ON new_sgsa.sgsa_sga_uid = new_sga.sga_uid
LEFT OUTER JOIN this_info_capture
ON ( this_info_capture.ic_msg_guid = old_asr.asr_msg_guid
AND this_info_capture.ic_msg_req_guid =
new_sga.sga_msg_req_guid
)
JOIN sbdba0.sb_device de ON ag.a_device_uid = de.d_device_uid
JOIN sbdba0.sb_skill_group_site sgs
ON ( sgs.sgs_sg_uid = ag.a_sg_uid
AND sgs.sgs_device_uid = ag.a_device_uid
)
-- where sgs_first_name ='Leslie'
-- AND sgs_last_name = 'Ray'
-- AND sga_msg_req_guid ='0A140A5B-31EC-07D8-00FC-112E0E020F7B'
) ast
WHERE
ast.new_sga_msg_req_guid IS NOT NULL
GROUP BY sgs_first_name,
new_sga_msg_req_guid
whenever I uncomment where clause in the inner query. it gives me results for 'sum_assigned_ttr_time_msecs' column. And when I comment where claue in inner query. Column becomes 0.
Any help would be greatly appreciated.
Thanks,
Preeti
