Unable to execute StoreProcedure in QlikView.

Report Inappropriate Content · ‎2012-06-12

Hi ,

I have a stored proc in SQL. I have attached the SP below. This SP is executing data in SQL but when i try to Execute the same in QlikView , I do not get any Columns.Please assist.

---- Temporary table for Incident FCR calculation.

DECLARE @TMP_INCFCR TABLE

(

ifcr_month datetime

, IFCR_Organization varchar(150)

, IFCR_SupportDeskID int

, IFCR_SupportDesk varchar(75)

, IFCR_Leader varchar(50)

, IFCR_Client varchar(100)

, IFCR_Level_0 int

, IFCR_Incident_Tickets_Closed int

, IFCR_Incd_Res int

,IFCR_Incident_Tickets_Created int

)

INSERT INTO @TMP_INCFCR

SELECT

month

, s.SD_Organization

, s.supportdeskID

, s.SupportDesk

, d.Leader

, d.client_name

, CASE WHEN Incd_Res IS NULL OR Incident_Tickets_Closed IS NULL THEN NULL ELSE Level_0 END as Level_0

, CASE WHEN Incd_Res IS NULL OR Incident_Tickets_Closed IS NULL THEN NULL ELSE Incident_Tickets_Closed END as Closed

, CASE WHEN Incd_Res IS NULL OR Incident_Tickets_Closed IS NULL THEN NULL ELSE Incd_Res END as Resolved

, CASE WHEN Incident_Tickets_Created IS NULL THEN NULL ELSE Incident_Tickets_Created END as Created

FROM GYR_Summary as d

INNER JOIN xref_SupportDesks as s on s.SupportDeskID = d.SupportDeskID

----------------------------------------------Check2----------------------------------------------

-- Temporary table to hold calculated Incident FCR

DECLARE @TMP_INCFCR_B TABLE

(

ifcr_B_month datetime

, IFCR_B_Organization varchar(150)

, IFCR_B_SupportDeskID int

, IFCR_B_SupportDesk varchar(75)

, IFCR_B_Leader varchar(50)

, IFCR_B_Client varchar(100)

, IFCR_B_Level_0 int

, IFCR_B_Incident_Tickets_Closed int

, IFCR_B_Incd_Res int

,IFCR_B_Incident_Tickets_Created int

)

INSERT INTO @TMP_INCFCR_B

SELECT

ifcr_month

, IFCR_Organization

, IFCR_SupportDeskID

, IFCR_SupportDesk

, IFCR_Leader

, IFCR_Client

, SUM(IFCR_Level_0)

, SUM(IFCR_Incident_Tickets_Closed)

, SUM(IFCR_Incd_Res)

,SUM(IFCR_Incident_Tickets_Created)

FROM @TMP_INCFCR

GROUP BY

ifcr_month

, IFCR_Organization

, IFCR_SupportDeskID

, IFCR_SupportDesk

, IFCR_Leader

, IFCR_Client

-------------------------------------------Check3-------------------------------------------

-- Temporary table to hold calculated Incident FCR

DECLARE @TMP_INCFCR_C TABLE

(

ifcr_C_month datetime

, IFCR_C_Organization varchar(150)

, IFCR_C_SupportDeskID int

, IFCR_C_SupportDesk varchar(75)

, IFCR_C_Leader varchar(50)

, IFCR_C_Client varchar(100)

, IFCR_C_Level_0 int

, IFCR_C_Incident_Tickets_Closed int

, IFCR_C_Incd_Res int

,IFCR_C_Incident_Tickets_Created int

)

INSERT INTO @TMP_INCFCR_C

SELECT

ifcr_B_month

, IFCR_B_Organization

, IFCR_B_SupportDeskID

, IFCR_B_SupportDesk

, IFCR_B_Leader

, IFCR_B_Client

, CASE WHEN MAX(IFCR_B_Incident_Tickets_Closed) IS NULL OR MAX(IFCR_B_Incd_Res) IS NULL THEN 0

WHEN (MAX(IFCR_B_Level_0) + MAX(IFCR_B_Incident_Tickets_Closed) ) = 0 THEN 0

ELSE MAX(IFCR_B_Level_0)

END

, CASE WHEN MAX(IFCR_B_Incident_Tickets_Closed) IS NULL OR MAX(IFCR_B_Incd_Res) IS NULL THEN 0

WHEN (MAX(IFCR_B_Level_0) + MAX(IFCR_B_Incident_Tickets_Closed) ) = 0 THEN 0

ELSE MAX(IFCR_B_Incident_Tickets_Closed)

END

, CASE WHEN MAX(IFCR_B_Incident_Tickets_Closed) IS NULL OR MAX(IFCR_B_Incd_Res) IS NULL THEN 0

WHEN (MAX(IFCR_B_Level_0) + MAX(IFCR_B_Incident_Tickets_Closed) ) = 0 THEN 0

ELSE MAX(IFCR_B_Incd_Res)

END

, CASE WHEN MAX(IFCR_B_Incident_Tickets_Created) IS NULL THEN 0

ELSE MAX(IFCR_B_Incident_Tickets_Created)

END

FROM @TMP_INCFCR_B

GROUP BY

ifcr_B_month

, IFCR_B_Organization

, IFCR_B_SupportDeskID

, IFCR_B_SupportDesk

, IFCR_B_Leader

, IFCR_B_Client

--SELECT * FROM @TMP_INCFCR_C

-------------------------------------------Check 4 -------------------------------------------

-- Temporary table to hold calculated Incident FCR

DECLARE @TMP_INCFCR_Final TABLE

(

ifcr_f_month datetime

, IFCR_F_Organization varchar(150)

, IFCR_F_SupportDeskID int

, IFCR_F_SupportDesk varchar(75)

, IFCR_F_Level_0 int

, IFCR_F_Incident_Tickets_Closed int

, IFCR_F_Incd_Res int

,IFCR_F_Incident_Tickets_Created int

)

INSERT INTO @TMP_INCFCR_Final

SELECT

ifcr_C_month

, IFCR_C_Organization

, IFCR_C_SupportDeskID

, IFCR_C_SupportDesk

, SUM(IFCR_C_Level_0)

, sum(IFCR_C_Incident_Tickets_Closed)

, sum(IFCR_C_Incd_Res)

,SUM(IFCR_C_Incident_Tickets_Created)

FROM @TMP_INCFCR_C

GROUP BY

ifcr_C_month

, IFCR_C_Organization

, IFCR_C_SupportDeskID

, IFCR_C_SupportDesk

--select * from @TMP_INCFCR_Final

-- All Metrics

SELECT

[month] as [Report Month]

, case when s.sd_organization like ('%BestShore%') then 'BestShore' else 'OnShore' end as Shore

, s.SD_Organization as Organization

, s.SupportDesk

, sum(level_0) as Level_0

,case when AVG(WorkoutActual_NEW)is null or AVG(WorkoutActual_NEW)=0 then NULL

else AVG(WorkoutActual_NEW) END as WorkoutActual

,case when AVG(TimeWorkedActual_NEW)is null or AVG(TimeWorkedActual_NEW) =0 then NULL

else AVG(TimeWorkedActual_NEW) END as TimeWorkedActual

,sum(a.AgentUtilization) as AgentUtilization_AOM

,sum(a.Total_Productive_Handling_Duration) as TPHD_AOM

, sum(isnull(Agent_Calls_Handled,0) +

isnull(Emails_Fax_Handled,0) +

isnull(Voice_Mails_Handled,0) +

isnull(Web_Enabled_Handled,0) + ISNULL(Chat_Handled,0)) as Contacts_Handled

, SUM(isnull(Incident_Tickets_Created,0) + isnull(IMAC_Tickets_Created,0)) as Tickets_Handled

, isnull(h.agents,0) as TotalAgents

, Case When isnull(h.agents,0)<>0 or isnull(Level_1_5_Service_Desk_Agent_Ext_RDM,0)<>0 Then (case when (SUM(isnull(Incident_Tickets_Created,0) + isnull(IMAC_Tickets_Created,0)) / (isnull(h.agents,0) + isnull(Level_1_5_Service_Desk_Agent_Ext_RDM,0)) ) <

304 Then

(SUM(isnull(Incident_Tickets_Created,0) + isnull(IMAC_Tickets_Created,0)) / (isnull(h.agents,0) + isnull(Level_1_5_Service_Desk_Agent_Ext_RDM,0)) ) * SUM(isnull(Incident_Tickets_Created,0) + isnull(IMAC_Tickets_Created,0))

Else 304 * SUM(isnull(Incident_Tickets_Created,0) + isnull(IMAC_Tickets_Created,0)) END) ElSE 0 END as bic

, Case When isnull(h.agents,0)<>0 or isnull(Level_1_5_Service_Desk_Agent_Ext_RDM,0)<>0 Then (case when (SUM(isnull(Incident_Tickets_Created,0) + isnull(IMAC_Tickets_Created,0)) / (isnull(h.agents,0) + isnull(Level_1_5_Service_Desk_Agent_Ext_RDM,0)) ) <

304 Then

(isnull(h.agents,0) + isnull(Level_1_5_Service_Desk_Agent_Ext_RDM,0)) else 0 END) ELSE 0 END as bic_Agents

,sum(isnull(tkt_res_1,0) + isnull(tkt_res_2,0) + isnull(tkt_res_3,0) + isnull(tkt_res_4,0)) as FCR_SLA_Resolved

,sum(isnull(tkt_1,0) + isnull(tkt_2,0) + isnull(tkt_3,0) + isnull(tkt_4,0)) as FCR_SLA_Tickets

, case when sum(isnull(Agents,0) + isnull(Escalation_Agents,0)+ isnull(Level_1_5_Service_Desk_Agent_Ext_RDM,0)+isnull(Agents_in_Training,0)) = 0

then 0 else

cast(sum(isnull(Admin,0) + isnull(BIM_FSS_Analysts,0) + isnull(Black_Belts_Business_Process,0) +

isnull(Business_Ops_Business_Planning,0) + isnull(Incident_Lifecycle_Cordinator,0) +

isnull(Managers_Delivery_Support_SDDMs,0) + isnull(Mentors_SMEs,0) + isnull(Outsourcing_Solution_Consultants,0) +

isnull(Problem_Analysts,0) + isnull(Project_Program_Mgrs,0) + isnull(Quality_Analysts_TCE,0) +

isnull(Team_Leads_Supervisors,0) + isnull(Trainers_Knowledge_Learning_resources,0) +

isnull(Transition_Mgrs,0) + isnull(Other,0) + isnull(Workforce_Intraday_Analyst,0)+

isnull(Service_Delivery_Analyst,0)+isnull(Service_Desk_Delivery_Support_Manager,0)+

isnull(Knowledge_Content_Analyst,0)+

isnull(Voice_Coach_Soft_Skills_Trainer,0)

) as float) /

cast(sum(isnull(Admin,0) + isnull(BIM_FSS_Analysts,0) + isnull(Black_Belts_Business_Process,0) +

isnull(Business_Ops_Business_Planning,0) + isnull(Incident_Lifecycle_Cordinator,0) +

isnull(Managers_Delivery_Support_SDDMs,0) + isnull(Mentors_SMEs,0) + isnull(Outsourcing_Solution_Consultants,0) +

isnull(Problem_Analysts,0) + isnull(Project_Program_Mgrs,0) + isnull(Quality_Analysts_TCE,0) +

isnull(Team_Leads_Supervisors,0) + isnull(Trainers_Knowledge_Learning_resources,0) +

isnull(Transition_Mgrs,0) + isnull(Other,0) + isnull(Workforce_Intraday_Analyst,0)+

isnull(Service_Delivery_Analyst,0)+isnull(Service_Desk_Delivery_Support_Manager,0)+

isnull(Knowledge_Content_Analyst,0)+

isnull(Voice_Coach_Soft_Skills_Trainer,0)

+isnull(Agents,0) + isnull(Escalation_Agents,0)+

isnull(Level_1_5_Service_Desk_Agent_Ext_RDM,0)+isnull(Agents_in_Training,0))as float) end as Delivery_Ratio

, SUM(CASE WHEN Answer_Rate_Exp IS NOT NULL AND Answer_Rate IS NOT NULL AND Answer_Rate_Min IS NOT NULL AND Answer_Rate >= Answer_Rate_Min THEN 1 ELSE 0 END +

CASE WHEN AbandonRateExp1 IS NOT NULL AND AbandonRate1 IS NOT NULL AND AbandonRateMin1 IS NOT NULL AND AbandonRate1 <= AbandonRateMin1 THEN 1 ELSE 0 END +

CASE WHEN AbandonRateExp2 IS NOT NULL AND AbandonRate2 IS NOT NULL AND AbandonRateMin2 IS NOT NULL AND AbandonRate2 <= AbandonRateMin2 THEN 1 ELSE 0 END +

CASE WHEN ASA_Exp IS NOT NULL AND Speed_to_Answer IS NOT NULL AND ASA_Min IS NOT NULL AND Speed_to_Answer <= ASA_Min THEN 1 ELSE 0 END +

CASE WHEN ASAExp2 IS NOT NULL AND Speed_to_Answer2 IS NOT NULL AND ASAMin2 IS NOT NULL AND Speed_to_Answer2 <= ASAMin2 THEN 1 ELSE 0 END +

CASE WHEN LOS_Exp IS NOT NULL AND LOS IS NOT NULL AND LOS_Min IS NOT NULL AND LOS >= LOS_Min THEN 1 ELSE 0 END +

CASE WHEN LOS_Exp_2 IS NOT NULL AND LOS_2 IS NOT NULL AND LOS_Min_2 IS NOT NULL AND LOS_2 >= LOS_Min_2 THEN 1 ELSE 0 END +

CASE WHEN Alt_Chnl_Resp_Time_Exp IS NOT NULL AND Alt_Chnl_Resp_Time IS NOT NULL AND Alt_Chnl_Resp_Time_Min IS NOT NULL AND Alt_Chnl_Resp_Time <= Alt_Chnl_Resp_Time_Min THEN 1 ELSE 0 END +

CASE WHEN Alt_Chnl_Resp_Time_Exp_2 IS NOT NULL AND Alt_Chnl_Resp_Time_2 IS NOT NULL AND Alt_Chnl_Resp_Time_Min_2 IS NOT NULL AND Alt_Chnl_Resp_Time_Percent_2 >= Alt_Chnl_Resp_Time_Min_2 THEN 1 ELSE 0 END +

CASE WHEN Alt_Chnl_Resp_Time_Exp_3 IS NOT NULL AND Alt_Chnl_Resp_Time_3 IS NOT NULL AND Alt_Chnl_Resp_Time_Min_3 IS NOT NULL AND Alt_Chnl_Resp_Time_Percent_3 >= Alt_Chnl_Resp_Time_Min_3 THEN 1 ELSE 0 END +

CASE WHEN Tkt_FCR_Exp_1 IS NOT NULL AND Tkt_FCR_1 IS NOT NULL AND Tkt_FCR_Min_1 IS NOT NULL AND Tkt_FCR_1 >= Tkt_FCR_Min_1 THEN 1 ELSE 0 END +

CASE WHEN Tkt_FCR_Exp_2 IS NOT NULL AND Tkt_FCR_2 IS NOT NULL AND Tkt_FCR_Min_2 IS NOT NULL AND Tkt_FCR_2 >= Tkt_FCR_Min_2 THEN 1 ELSE 0 END +

CASE WHEN Tkt_FCR_Exp_3 IS NOT NULL AND Tkt_FCR_3 IS NOT NULL AND Tkt_FCR_Min_3 IS NOT NULL AND Tkt_FCR_3 >= Tkt_FCR_Min_3 THEN 1 ELSE 0 END +

CASE WHEN Tkt_FCR_Exp_4 IS NOT NULL AND Tkt_FCR_4 IS NOT NULL AND Tkt_FCR_Min_4 IS NOT NULL AND Tkt_FCR_4 >= Tkt_FCR_Min_4 THEN 1 ELSE 0 END +

CASE WHEN Incd_FCR_Exp IS NOT NULL AND Incd_FCR IS NOT NULL AND Incd_FCR_Min IS NOT NULL AND Incd_FCR >= Incd_FCR_Min THEN 1 ELSE 0 END +

CASE WHEN IMAC_Exp IS NOT NULL AND IMAC_FCR IS NOT NULL AND IMAC_Min IS NOT NULL AND IMAC_FCR >= IMAC_Min THEN 1 ELSE 0 END +

CASE WHEN CSI_Exp IS NOT NULL AND CSI IS NOT NULL AND CSI_Min IS NOT NULL AND CSI >= CSI_Min THEN 1 ELSE 0 END +

CASE WHEN CSI_Exp2 IS NOT NULL AND CSI2 IS NOT NULL AND CSI_Min2 IS NOT NULL AND CSI2 >= CSI_Min2 THEN 1 ELSE 0 END +

CASE WHEN Comp_in_Spec_Time >= CWST_Target AND Comp_in_Spec_Time > 0 AND Comp_in_Spec_Time IS NOT NULL AND CWST_Target IS NOT NULL THEN 1 ELSE 0 END +

CASE WHEN TRR1_Target_Exp IS NOT NULL AND TRR1 IS NOT NULL AND TRR1_Target_Min IS NOT NULL AND TRR1 >= TRR1_Target_Min THEN 1 ELSE 0 END +

CASE WHEN TRR2_Target_Exp IS NOT NULL AND TRR2 IS NOT NULL AND TRR2_Target_Min IS NOT NULL AND TRR2 >= TRR2_Target_Min THEN 1 ELSE 0 END +

CASE WHEN TRR3_Target_Exp IS NOT NULL AND TRR3 IS NOT NULL AND TRR3_Target_Min IS NOT NULL AND TRR3 >= TRR3_Target_Min THEN 1 ELSE 0 END +

CASE WHEN TRR4_Target_Exp IS NOT NULL AND TRR4 IS NOT NULL AND TRR4_Target_Min IS NOT NULL AND TRR4 >= TRR4_Target_Min THEN 1 ELSE 0 END +

CASE WHEN TRR5_Target_Exp IS NOT NULL AND TRR5 IS NOT NULL AND TRR5_Target_Min IS NOT NULL AND TRR5 >= TRR5_Target_Min THEN 1 ELSE 0 END +

CASE WHEN TRR6_Target_Exp IS NOT NULL AND TRR6 IS NOT NULL AND TRR6_Target_Min IS NOT NULL AND TRR6 >= TRR6_Target_Min THEN 1 ELSE 0 END +

CASE WHEN TRR7_Target_Exp IS NOT NULL AND TRR7 IS NOT NULL AND TRR7_Target_Min IS NOT NULL AND TRR7 >= TRR7_Target_Min THEN 1 ELSE 0 END +

CASE WHEN TRR8_Target_Exp IS NOT NULL AND TRR8 IS NOT NULL AND TRR8_Target_Min IS NOT NULL AND TRR8 >= TRR8_Target_Min THEN 1 ELSE 0 END +

CASE WHEN TRR9_Target_Exp IS NOT NULL AND TRR9 IS NOT NULL AND TRR9_Target_Min IS NOT NULL AND TRR9 >= TRR9_Target_Min THEN 1 ELSE 0 END +

CASE WHEN TRR10_Target_Exp IS NOT NULL AND TRR10 IS NOT NULL AND TRR10_Target_Min IS NOT NULL AND TRR10 >= TRR10_Target_Min THEN 1 ELSE 0 END +

CASE WHEN TRR11_Target_Exp IS NOT NULL AND TRR11 IS NOT NULL AND TRR11_Target_Min IS NOT NULL AND TRR11 >= TRR11_Target_Min THEN 1 ELSE 0 END +

CASE WHEN TRR12_Target_Exp IS NOT NULL AND TRR12 IS NOT NULL AND TRR12_Target_Min IS NOT NULL AND TRR12 >= TRR12_Target_Min THEN 1 ELSE 0 END +

CASE WHEN TRR13_Target_Exp IS NOT NULL AND TRR13 IS NOT NULL AND TRR13_Target_Min IS NOT NULL AND TRR13 >= TRR13_Target_Min THEN 1 ELSE 0 END +

CASE WHEN TRR14_Target_Exp IS NOT NULL AND TRR14 IS NOT NULL AND TRR14_Target_Min IS NOT NULL AND TRR14 >= TRR14_Target_Min THEN 1 ELSE 0 END +

CASE WHEN TRR15_Target_Exp IS NOT NULL AND TRR15 IS NOT NULL AND TRR15_Target_Min IS NOT NULL AND TRR15 >= TRR15_Target_Min THEN 1 ELSE 0 END +

CASE WHEN TRR16_Target_Exp IS NOT NULL AND TRR16 IS NOT NULL AND TRR16_Target_Min IS NOT NULL AND TRR16 >= TRR16_Target_Min THEN 1 ELSE 0 END +

CASE WHEN TRR17_Target_Exp IS NOT NULL AND TRR17 IS NOT NULL AND TRR17_Target_Min IS NOT NULL AND TRR17 >= TRR17_Target_Min THEN 1 ELSE 0 END) as Metrics_Met

, SUM(CASE WHEN Answer_Rate_Exp IS NOT NULL AND Answer_Rate IS NOT NULL AND Answer_Rate_Min IS NOT NULL AND Answer_Rate < Answer_Rate_Min THEN 1 ELSE 0 END +

CASE WHEN AbandonRateExp1 IS NOT NULL AND AbandonRate1 IS NOT NULL AND AbandonRateMin1 IS NOT NULL AND AbandonRate1 > AbandonRateMin1 THEN 1 ELSE 0 END +

CASE WHEN AbandonRateExp2 IS NOT NULL AND AbandonRate2 IS NOT NULL AND AbandonRateMin2 IS NOT NULL AND AbandonRate2 > AbandonRateMin2 THEN 1 ELSE 0 END +

CASE WHEN ASA_Exp IS NOT NULL AND Speed_to_Answer IS NOT NULL AND ASA_Min IS NOT NULL AND Speed_to_Answer > ASA_Min THEN 1 ELSE 0 END +

CASE WHEN ASAExp2 IS NOT NULL AND Speed_to_Answer2 IS NOT NULL AND ASAMin2 IS NOT NULL AND Speed_to_Answer2 > ASAMin2 THEN 1 ELSE 0 END +

CASE WHEN LOS_Exp IS NOT NULL AND LOS IS NOT NULL AND LOS_Min IS NOT NULL AND LOS < LOS_Min THEN 1 ELSE 0 END +

CASE WHEN LOS_Exp_2 IS NOT NULL AND LOS_2 IS NOT NULL AND LOS_Min_2 IS NOT NULL AND LOS_2 < LOS_Min_2 THEN 1 ELSE 0 END +

CASE WHEN Alt_Chnl_Resp_Time_Exp IS NOT NULL AND Alt_Chnl_Resp_Time IS NOT NULL AND Alt_Chnl_Resp_Time_Min IS NOT NULL AND Alt_Chnl_Resp_Time > Alt_Chnl_Resp_Time_Min THEN 1 ELSE 0 END +

CASE WHEN Alt_Chnl_Resp_Time_Exp_2 IS NOT NULL AND Alt_Chnl_Resp_Time_2 IS NOT NULL AND Alt_Chnl_Resp_Time_Min_2 IS NOT NULL AND Alt_Chnl_Resp_Time_Percent_2 < Alt_Chnl_Resp_Time_Min_2 THEN 1 ELSE 0 END +

CASE WHEN Alt_Chnl_Resp_Time_Exp_3 IS NOT NULL AND Alt_Chnl_Resp_Time_3 IS NOT NULL AND Alt_Chnl_Resp_Time_Min_3 IS NOT NULL AND Alt_Chnl_Resp_Time_Percent_3 < Alt_Chnl_Resp_Time_Min_3 THEN 1 ELSE 0 END +

CASE WHEN Tkt_FCR_Exp_1 IS NOT NULL AND Tkt_FCR_1 IS NOT NULL AND Tkt_FCR_Min_1 IS NOT NULL AND Tkt_FCR_1 < Tkt_FCR_Min_1 THEN 1 ELSE 0 END +

CASE WHEN Tkt_FCR_Exp_2 IS NOT NULL AND Tkt_FCR_2 IS NOT NULL AND Tkt_FCR_Min_2 IS NOT NULL AND Tkt_FCR_2 < Tkt_FCR_Min_2 THEN 1 ELSE 0 END +

CASE WHEN Tkt_FCR_Exp_3 IS NOT NULL AND Tkt_FCR_3 IS NOT NULL AND Tkt_FCR_Min_3 IS NOT NULL AND Tkt_FCR_3 < Tkt_FCR_Min_3 THEN 1 ELSE 0 END +

CASE WHEN Tkt_FCR_Exp_4 IS NOT NULL AND Tkt_FCR_4 IS NOT NULL AND Tkt_FCR_Min_4 IS NOT NULL AND Tkt_FCR_4 < Tkt_FCR_Min_4 THEN 1 ELSE 0 END +

CASE WHEN Incd_FCR_Exp IS NOT NULL AND Incd_FCR IS NOT NULL AND Incd_FCR_Min IS NOT NULL AND Incd_FCR < Incd_FCR_Min THEN 1 ELSE 0 END +

CASE WHEN IMAC_Exp IS NOT NULL AND IMAC_FCR IS NOT NULL AND IMAC_Min IS NOT NULL AND IMAC_FCR < IMAC_Min THEN 1 ELSE 0 END +

CASE WHEN CSI_Exp IS NOT NULL AND CSI IS NOT NULL AND CSI_Min IS NOT NULL AND CSI < CSI_Min THEN 1 ELSE 0 END +

CASE WHEN CSI_Exp2 IS NOT NULL AND CSI2 IS NOT NULL AND CSI_Min2 IS NOT NULL AND CSI2 < CSI_Min2 THEN 1 ELSE 0 END +

CASE WHEN Comp_in_Spec_Time < CWST_Target AND Comp_in_Spec_Time > 0 AND Comp_in_Spec_Time IS NOT NULL AND CWST_Target IS NOT NULL THEN 1 ELSE 0 END +

CASE WHEN TRR1_Target_Exp IS NOT NULL AND TRR1 IS NOT NULL AND TRR1_Target_Min IS NOT NULL AND TRR1 < TRR1_Target_Min THEN 1 ELSE 0 END +

CASE WHEN TRR2_Target_Exp IS NOT NULL AND TRR2 IS NOT NULL AND TRR2_Target_Min IS NOT NULL AND TRR2 < TRR2_Target_Min THEN 1 ELSE 0 END +

CASE WHEN TRR3_Target_Exp IS NOT NULL AND TRR3 IS NOT NULL AND TRR3_Target_Min IS NOT NULL AND TRR3 < TRR3_Target_Min THEN 1 ELSE 0 END +

CASE WHEN TRR4_Target_Exp IS NOT NULL AND TRR4 IS NOT NULL AND TRR4_Target_Min IS NOT NULL AND TRR4 < TRR4_Target_Min THEN 1 ELSE 0 END +

CASE WHEN TRR5_Target_Exp IS NOT NULL AND TRR5 IS NOT NULL AND TRR5_Target_Min IS NOT NULL AND TRR5 < TRR5_Target_Min THEN 1 ELSE 0 END +

CASE WHEN TRR6_Target_Exp IS NOT NULL AND TRR6 IS NOT NULL AND TRR6_Target_Min IS NOT NULL AND TRR6 < TRR6_Target_Min THEN 1 ELSE 0 END +

CASE WHEN TRR7_Target_Exp IS NOT NULL AND TRR7 IS NOT NULL AND TRR7_Target_Min IS NOT NULL AND TRR7 < TRR7_Target_Min THEN 1 ELSE 0 END +

CASE WHEN TRR8_Target_Exp IS NOT NULL AND TRR8 IS NOT NULL AND TRR8_Target_Min IS NOT NULL AND TRR8 < TRR8_Target_Min THEN 1 ELSE 0 END +

CASE WHEN TRR9_Target_Exp IS NOT NULL AND TRR9 IS NOT NULL AND TRR9_Target_Min IS NOT NULL AND TRR9 < TRR9_Target_Min THEN 1 ELSE 0 END +

CASE WHEN TRR10_Target_Exp IS NOT NULL AND TRR10 IS NOT NULL AND TRR10_Target_Min IS NOT NULL AND TRR10 < TRR10_Target_Min THEN 1 ELSE 0 END +

CASE WHEN TRR11_Target_Exp IS NOT NULL AND TRR11 IS NOT NULL AND TRR11_Target_Min IS NOT NULL AND TRR11 < TRR11_Target_Min THEN 1 ELSE 0 END +

CASE WHEN TRR12_Target_Exp IS NOT NULL AND TRR12 IS NOT NULL AND TRR12_Target_Min IS NOT NULL AND TRR12 < TRR12_Target_Min THEN 1 ELSE 0 END +

CASE WHEN TRR13_Target_Exp IS NOT NULL AND TRR13 IS NOT NULL AND TRR13_Target_Min IS NOT NULL AND TRR13 < TRR13_Target_Min THEN 1 ELSE 0 END +

CASE WHEN TRR14_Target_Exp IS NOT NULL AND TRR14 IS NOT NULL AND TRR14_Target_Min IS NOT NULL AND TRR14 < TRR14_Target_Min THEN 1 ELSE 0 END +

CASE WHEN TRR15_Target_Exp IS NOT NULL AND TRR15 IS NOT NULL AND TRR15_Target_Min IS NOT NULL AND TRR15 < TRR15_Target_Min THEN 1 ELSE 0 END +

CASE WHEN TRR16_Target_Exp IS NOT NULL AND TRR16 IS NOT NULL AND TRR16_Target_Min IS NOT NULL AND TRR16 < TRR16_Target_Min THEN 1 ELSE 0 END +

CASE WHEN TRR17_Target_Exp IS NOT NULL AND TRR17 IS NOT NULL AND TRR17_Target_Min IS NOT NULL AND TRR17 < TRR17_Target_Min THEN 1 ELSE 0 END) as Metrics_Missed

, IFCR_F_Level_0 as INCD_FCR_Include_Level_0

, IFCR_F_Incident_Tickets_Closed as INCD_FCR_Include_Tickets_Closed

, IFCR_F_Incd_Res as INCD_FCR_Include_FCR_Closed

, SUM(Total_Satisfied) as Total_Satisfied

, SUM(Total_Surveys) as Total_Surveys

, isnull(Agents,0) as Agents

, isnull(Escalation_Agents,0) as Escalation_Agents

, isnull(Agents_in_Training,0) as Agents_in_Training

, isnull(Admin,0) + isnull(BIM_FSS_Analysts,0) + isnull(Black_Belts_Business_Process,0) +

isnull(Business_Ops_Business_Planning,0) + isnull(Incident_Lifecycle_Cordinator,0) +

isnull(Managers_Delivery_Support_SDDMs,0) + isnull(Mentors_SMEs,0) + isnull(Outsourcing_Solution_Consultants,0) +

isnull(Problem_Analysts,0) + isnull(Project_Program_Mgrs,0) + isnull(Quality_Analysts_TCE,0) +

isnull(Team_Leads_Supervisors,0) + isnull(Trainers_Knowledge_Learning_resources,0) +

isnull(Transition_Mgrs,0) + isnull(Other,0) + isnull(Workforce_Intraday_Analyst,0)+

isnull(Service_Delivery_Analyst,0)+isnull(Service_Desk_Delivery_Support_Manager,0)+

isnull(Knowledge_Content_Analyst,0)+

isnull(Voice_Coach_Soft_Skills_Trainer,0) as delivery_support

,AVG(DivertedTime) as DivertedTime

,AVG(TotalCoreTasks) as TotalCoreTasks

, CASE

WHEN AVG(WorkoutActual) IS NULL

OR AVG(TimeWorkedActual) IS NULL THEN NULL

WHEN AVG(WorkoutActual) = 0

OR AVG(TimeWorkedActual) = 0 THEN 0

ELSE CONVERT(Float, AVG(WorkoutActual)) /

CONVERT(Float, AVG(TimeWorkedActual)) END as Utilization

, CASE

WHEN AVG(TimeWorkedActual) IS NULL

OR AVG(DivertedTime) IS NULL THEN NULL

WHEN AVG(TimeWorkedActual) + AVG(DivertedTime) = 0

OR AVG(TimeWorkedActual) = 0 THEN 0

ELSE

CONVERT(Float, (AVG(TimeWorkedActual) - AVG(DivertedTime))) /

CONVERT(Float, AVG(TimeWorkedActual)) END as Occupancy

, CASE

WHEN AVG(WorkoutActual) IS NULL

OR AVG(TotalCoreTasks) IS NULL THEN NULL

WHEN AVG(WorkoutActual) = 0

OR AVG(TotalCoreTasks) = 0 THEN 0

ELSE CONVERT(Float, AVG(WorkoutActual)) /

CONVERT(Float, AVG(TotalCoreTasks)) END as AHT

, sum(isnull(avg_talk_time,0)) talk_time

, sum(isnull(d.avg_wrap_time,0)) wrap_time

,isnull(Level_1_5_Service_Desk_Agent_Ext_RDM,0) as Level_1_5_Service_Desk_Agent_Ext_RDM

,SUM(SvDTicketsResolved) as SvDTicketsResolved

, SUM(IMAC_Tickets_Closed) as IMAC_Tickets_Closed_Resolved

, SUM(Incident_Tickets_Closed) as Incident_Tickets_Closed_Resolved

, SUM(CASE WHEN Avg_Talk_Time IS NULL OR Avg_Talk_Time = 0 THEN NULL ELSE Avg_Talk_Time * Agent_Calls_Handled END) as Weighted_Talk

, SUM(CASE WHEN Avg_Talk_Time IS NULL OR Avg_Talk_Time = 0 THEN NULL ELSE Agent_Calls_Handled END) as Talk_Volume

, SUM(CASE WHEN Avg_Wrap_Time IS NULL OR Avg_Wrap_Time = 0 THEN NULL ELSE Avg_Wrap_Time * Agent_Calls_Handled END) as Weighted_Wrap

, SUM(CASE WHEN Avg_Wrap_Time IS NULL OR Avg_Wrap_Time = 0 THEN NULL ELSE Agent_Calls_Handled END) as Wrap_Volume

,IFCR_F_Incident_Tickets_Created as Call_Interaction_Tickets_Created

, SUM(CASE WHEN Incident_Tickets_Created IS NULL OR Incd_Res IS NULL THEN 0

WHEN (Level_0 + Incident_Tickets_Created ) = 0 THEN 0

ELSE Incident_Tickets_Created

END) as INCD_FCR_Incident_Tickets_Created_NEW

from

GYR_Summary as d

INNER JOIN xref_SupportDesks as s on s.SupportDeskID = d.SupportDeskID

left outer join

(select reportmonth,

supportdeskid,

sum(isnull(Agents,0)) as Agents,

sum(isnull(Escalation_Agents,0)) as Escalation_Agents,

sum(isnull(Agents_in_Training,0)) as Agents_in_Training,

sum(isnull(Admin,0)) as Admin,

sum(isnull(BIM_FSS_Analysts,0)) as BIM_FSS_Analysts,

sum(isnull(Black_Belts_Business_Process,0)) as Black_Belts_Business_Process,

sum(isnull(Business_Ops_Business_Planning,0)) as Business_Ops_Business_Planning,

sum(isnull(Incident_Lifecycle_Cordinator,0)) as Incident_Lifecycle_Cordinator,

sum(isnull(Managers_Delivery_Support_SDDMs,0)) as Managers_Delivery_Support_SDDMs,

sum(isnull(Mentors_SMEs,0)) as Mentors_SMEs,

sum(isnull(Outsourcing_Solution_Consultants,0)) as Outsourcing_Solution_Consultants,

sum(isnull(Problem_Analysts,0)) as Problem_Analysts,

sum(isnull(Project_Program_Mgrs,0)) as Project_Program_Mgrs,

sum(isnull(Quality_Analysts_TCE,0)) as Quality_Analysts_TCE,

sum(isnull(Team_Leads_Supervisors,0)) as Team_Leads_Supervisors,

sum(isnull(Trainers_Knowledge_Learning_resources,0)) as Trainers_Knowledge_Learning_resources,

sum(isnull(Transition_Mgrs,0)) as Transition_Mgrs,

sum(isnull(Other,0)) as Other,

sum(isnull(Level_1_5_Service_Desk_Agent_Ext_RDM,0)) as Level_1_5_Service_Desk_Agent_Ext_RDM,

sum(isnull(Voice_Coach_Soft_Skills_Trainer,0)) as Voice_Coach_Soft_Skills_Trainer,

sum(isnull(Knowledge_Content_Analyst,0)) as Knowledge_Content_Analyst,

sum(isnull(Service_Desk_Delivery_Support_Manager,0)) as Service_Desk_Delivery_Support_Manager,

sum(isnull(Service_Delivery_Analyst,0)) as Service_Delivery_Analyst,

sum(isnull(Workforce_Intraday_Analyst,0)) as Workforce_Intraday_Analyst

from gyr_gsdheadcount

group by reportmonth, supportdeskid) as h on d.month = h.reportmonth and d.supportdeskid = h.supportdeskid

left outer JOIN (SELECT ReportDate

,SupportDeskID

,sum(WorkOutActual) as WorkOutActual

,sum(TimeWorkedActual) as TimeWorkedActual

,sum(TimeWorkedFTE) as TimeWorkedFTE

,sum(TotalCoreTasks) as TotalCoreTasks

,sum(DivertedTime) as DivertedTime

,sum(Utilization) as Utilization

,sum(Occupancy) as Occupancy

,sum(AvgHandleTime) as AvgHandleTime

,sum(ACD_Time) as ACDTime

,sum(ACW_Time) as ACWTime

,sum(Other_Time) as OtherTime

,sum(Ring_Time) as RingTime

,sum(AUX_0) as AUX0

,sum(AUX_1) as AUX1

,sum(AUX_2) as AUX2

,sum(AUX_3) as AUX3

,sum(AUX_4) as AUX4

,sum(AUX_5) as AUX5

,sum(AUX_6) as AUX6

,sum(AUX_7) as AUX7

,sum(AUX_8) as AUX8

,sum(AUX_9) as AUX9

,sum(Avail_Time) as Avail_Time

,sum(Total_Productive_Handling_Duration) as Total_Productive_Handling_Duration

,sum(Agent_Utilization) as AgentUtilization

,sum(Occupancy_Rate) as OccupancyRate

,sum(WorkOutActual_NEW) as WorkOutActual_NEW

,sum(TimeWorkedActual_NEW) as TimeWorkedActual_NEW

FROM [E2E].[dbo].[VIEW_AOM_NEW]

group by

ReportDate

,SupportDeskID) as a on d.supportdeskid = a.supportdeskid and d.month = a.reportdate

LEFT OUTER JOIN @TMP_INCFCR_Final as nf on d.SupportDeskID = nf.IFCR_F_SupportDeskID and d.month = nf.ifcr_f_month

GROUP BY s.SD_Organization, s.SupportDesk, [month], h.agents, h.Escalation_Agents, h.Agents_in_Training,h.Level_1_5_Service_Desk_Agent_Ext_RDM,

h.Workforce_Intraday_Analyst,h.Service_Delivery_Analyst,h.Service_Desk_Delivery_Support_Manager,h.Knowledge_Content_Analyst,h.Voice_Coach_Soft_Skills_Trainer,

Admin,BIM_FSS_Analysts,Black_Belts_Business_Process,

Business_Ops_Business_Planning,Incident_Lifecycle_Cordinator,

Managers_Delivery_Support_SDDMs,Mentors_SMEs,Outsourcing_Solution_Consultants,

Problem_Analysts,Project_Program_Mgrs,Quality_Analysts_TCE,

Team_Leads_Supervisors,Trainers_Knowledge_Learning_resources,

Transition_Mgrs,Other , IFCR_F_Level_0, IFCR_F_Incident_Tickets_Closed, IFCR_F_Incd_Res,IFCR_F_Incident_Tickets_Created

ORDER BY [month], s.SD_Organization, SupportDesk

END

QlikView App Dev