With Selection.QueryTable -** Please help**

R

Ramnaidu

New Member
Joined
May 7, 2014
Messages
17
Hi Friends

Hope you all are doing good. Sorry for such a long code. When I run the below sql script in sql server the results are fine but when I try to run this from excel using VBA I get With Selection.QueryTable error.

Can you please let me know, how to eliminate this error. Please find the below SQL script and attached VBA code. Please help me I need to deliver a new scorecard by EOD.
Code: 
[TABLE="width: 624"]
<tbody>[TR]
[TD]SET NOCOUNT ON[/TD]
[/TR]
[TR]
[TD]DECLARE @StartDate AS DATETIME;[/TD]
[/TR]
[TR]
[TD]DECLARE @EndDate AS DATETIME;[/TD]
[/TR]
[TR]
[TD]DECLARE @MonthEndDate AS DATETIME;[/TD]
[/TR]
[TR]
[TD]SELECT @StartDate = '01/07/2014';[/TD]
[/TR]
[TR]
[TD]SELECT @EndDate = '10/07/2014';[/TD]
[/TR]
[TR]
[TD]SELECT @MonthEndDate = '09/07/2014';[/TD]
[/TR]
[TR]
[TD]With Manp AS ([/TD]
[/TR]
[TR]
[TD]SELECT RTRIM(REPLACE(agent.[EMP_SK],'<wbr>.0','')) AS [Emp_SK],  agent.[Forename] + ' ' + agent.[Surname] AS [Agent], agent.[Staff_id] AS [LAN],[/TD]
[/TR]
[TR]
[TD]agent.[PayrollNumber] AS [Pay], agent.[Pin], agent.[Pin2], agent.[Grade], agent.[Hours_PW],[/TD]
[/TR]
[TR]
[TD]agent.[Office_Name] AS [Site], agent.[PFW_Group], agent.[Dept_Name] AS [Dept],[/TD]
[/TR]
[TR]
[TD]RTRIM(REPLACE(agent.[<wbr>LineManagerSK],'.0',''))  AS [LineManagerSK], agent.[LineManager] AS [TM], tm.[PayrollNumber] AS [TM Pay],[/TD]
[/TR]
[TR]
[TD]RTRIM(REPLACE(agent.[<wbr>OpsManagerSK],'.0',''))  AS [OpsManagerSK], agent.[OpsManager] AS [CSM], csm.[PayrollNumber] AS [CSM Pay],[/TD]
[/TR]
[TR]
[TD]        DATEADD(mm, DATEDIFF(mm,0,agent.thedate), 0) as month[/TD]
[/TR]
[TR]
[TD]FROM[/TD]
[/TR]
[TR]
[TD] (SELECT tab1.[CUPID] ,tab1.[Emp_SK] , TAB1.DATASOURCEID, tab1.[LANID] AS [Staff_id] ,tab1.[first_name] as [Forename] ,tab1.[last_name] as [Surname] ,tab1.[PIN] as [PIN] ,NULL AS [Originator] ,tab1.[BadgeNumber] as [Badge_no] ,tab1.[Payroll] as [PayrollNumber] ,tab1.[Hours_PW] ,tab1.[StartDate] as [Start_Date] ,tab1.[Grade] ,CAST(tab1.[LeavingDate] as datetime) as [End_Date] ,tab1.[LeavingReason] as [LeavingReasonID] ,tab1.[Site] as [Office_Name] ,tab1.[Status] ,tab1.[Pin] as [Ext_Number] ,tab1.[Activity] as [PFW_Group] ,tab1.[Department] as [Dept_Name] ,tab1.[LineMangerSK] as [LineManagerSK] ,tab1.[LineManager] ,tab1.[OpsManagerSK] ,tab1.[OpsManager] ,tab1.[Contract] as [ContractStatusID] ,tab1.[PIN2] ,tab1.[SAPFLAG] ,tab1.[SENIORITY] ,tab1.[SHIFTPATTERNCODE] ,tab1.[SUBSTANTIVE] ,tab1.[thedate] FROM [TAMI].[dbo].[STD_EWFM_<wbr>Manpower] tab1 WHERE tab1.[Status] <> ''   AND tab1.[theDate] >= @startdate AND tab1.[theDate] < @enddate) agent[/TD]
[/TR]
[TR]
[TD]INNER JOIN[/TD]
[/TR]
[TR]
[TD](SELECT max(thedate) as thedate, DATEADD(mm, DATEDIFF(mm,0,thedate), 0) as month FROM [TAMI].[dbo].[STD_EWFM_<wbr>Manpower][/TD]
[/TR]
[TR]
[TD] WHERE TheDate >= @StartDate and thedate <@EndDate GROUP BY DATEADD(mm, DATEDIFF(mm,0,thedate), 0)) month[/TD]
[/TR]
[TR]
[TD]on month.thedate = agent.thedate[/TD]
[/TR]
[TR]
[TD]        LEFT JOIN[/TD]
[/TR]
[TR]
[TD][TAMI].[dbo].[STD_EWFM_<wbr>Manpower_Snapshot] tm[/TD]
[/TR]
[TR]
[TD]        ON agent.[LineManagerSK] = tm.[Emp_SK] and agent.datasourceid = tm.datasourceid[/TD]
[/TR]
[TR]
[TD]        LEFT JOIN[/TD]
[/TR]
[TR]
[TD][TAMI].[dbo].[STD_EWFM_<wbr>Manpower_Snapshot] csm[/TD]
[/TR]
[TR]
[TD]ON agent.[OpsManagerSK] = csm.[Emp_SK] and agent.datasourceid = csm.datasourceid[/TD]
[/TR]
[TR]
[TD])[/TD]
[/TR]
[TR]
[TD]SELECT[/TD]
[/TR]
[TR]
[TD]CASE WHEN I2.Date = '29/4/13' then '30/4/13' else I2.Date end as [TheDate],[/TD]
[/TR]
[TR]
[TD]manp.lan as [LanID],[/TD]
[/TR]
[TR]
[TD]i2.CallGroup as [CallGroup],[/TD]
[/TR]
[TR]
[TD]i2.CallType,[/TD]
[/TR]
[TR]
[TD]SUM(isnull(i2.[Total Discount Value],0)) as [Total_Discount_Value],[/TD]
[/TR]
[TR]
[TD]SUM(isnull(i2.[Total Ret],0)) as [Total_Ret],[/TD]
[/TR]
[TR]
[TD]COALESCE(SUM(i2.[Total Discount Value])/NULLIF(SUM(i2.[Total Ret]),0),0) as [Cost_per_Save][/TD]
[/TR]
[TR]
[TD]FROM[/TD]
[/TR]
[TR]
[TD] ([/TD]
[/TR]
[TR]
[TD]SELECT[/TD]
[/TR]
[TR]
[TD]*,[/TD]
[/TR]
[TR]
[TD][CHC Ret] +[/TD]
[/TR]
[TR]
[TD][CHC Excess Ret]  +[/TD]
[/TR]
[TR]
[TD][GAC Ret]  +[/TD]
[/TR]
[TR]
[TD][GAC Excess Ret]  +[/TD]
[/TR]
[TR]
[TD][KAC Ret]  +[/TD]
[/TR]
[TR]
[TD][KAC Excess Ret]  +[/TD]
[/TR]
[TR]
[TD][HEC Ret]  +[/TD]
[/TR]
[TR]
[TD][HEC Excess Ret]  +[/TD]
[/TR]
[TR]
[TD][PAD Ret]  +[/TD]
[/TR]
[TR]
[TD][PAD Excess Ret]  +[/TD]
[/TR]
[TR]
[TD][Landlords Ret]  +[/TD]
[/TR]
[TR]
[TD][Other Ret] AS [Total Ret][/TD]
[/TR]
[TR]
[TD]FROM[/TD]
[/TR]
[TR]
[TD]([/TD]
[/TR]
[TR]
[TD]SELECT[/TD]
[/TR]
[TR]
[TD] CreatedBy,[/TD]
[/TR]
[TR]
[TD]ID,[/TD]
[/TR]
[TR]
[TD]Date,[/TD]
[/TR]
[TR]
[TD]Callgroup,[/TD]
[/TR]
[TR]
[TD]CallType,[/TD]
[/TR]
[TR]
[TD]AcceptedfuelRet,[/TD]
[/TR]
[TR]
[TD]AcceptedfuelSale,[/TD]
[/TR]
[TR]
[TD]AcceptedDuelFuelSale,[/TD]
[/TR]
[TR]
[TD]SUM(ISNULL([CHC RET],0)) AS [CHC RET],[/TD]
[/TR]
[TR]
[TD]SUM(ISNULL([GAC RET],0)) AS [GAC RET],[/TD]
[/TR]
[TR]
[TD]SUM(ISNULL([KAC_A RET],0)) AS [KAC RET],[/TD]
[/TR]
[TR]
[TD]SUM(ISNULL([HEC RET],0)) AS [HEC RET],[/TD]
[/TR]
[TR]
[TD]SUM(ISNULL([PAD RET],0)) AS [PAD RET],[/TD]
[/TR]
[TR]
[TD]SUM(ISNULL([CHC Excess RET],0)) AS [CHC Excess RET],[/TD]
[/TR]
[TR]
[TD]SUM(ISNULL([GAC Excess RET],0)) AS [GAC Excess RET],[/TD]
[/TR]
[TR]
[TD]SUM(ISNULL([KAC Excess_A RET],0)) AS [KAC Excess RET],[/TD]
[/TR]
[TR]
[TD]SUM(ISNULL([HEC Excess RET],0)) AS [HEC Excess RET],[/TD]
[/TR]
[TR]
[TD]SUM(ISNULL([PAD Excess RET],0)) AS [PAD Excess RET],[/TD]
[/TR]
[TR]
[TD]SUM(ISNULL([Landlords RET],0)) AS [Landlords RET],[/TD]
[/TR]
[TR]
[TD]SUM(ISNULL([Other RET],0)) AS [Other RET],[/TD]
[/TR]
[TR]
[TD]SUM(ISNULL([CHC Dis2],0)) +[/TD]
[/TR]
[TR]
[TD]SUM(ISNULL([GAC Dis2],0)) +[/TD]
[/TR]
[TR]
[TD]SUM(ISNULL([KAC Dis2],0)) +[/TD]
[/TR]
[TR]
[TD]SUM(ISNULL([HEC Dis2],0)) +[/TD]
[/TR]
[TR]
[TD]SUM(ISNULL([PAD Dis2],0)) +[/TD]
[/TR]
[TR]
[TD]SUM(ISNULL([CHC Excess Dis2],0)) +[/TD]
[/TR]
[TR]
[TD]SUM(ISNULL([GAC Excess Dis2],0)) +[/TD]
[/TR]
[TR]
[TD]SUM(ISNULL([KAC Excess Dis2],0)) +[/TD]
[/TR]
[TR]
[TD]SUM(ISNULL([HEC Excess Dis2],0)) +[/TD]
[/TR]
[TR]
[TD]SUM(ISNULL([PAD Excess Dis2],0)) +[/TD]
[/TR]
[TR]
[TD]SUM(ISNULL([Landlords Dis2],0)) +[/TD]
[/TR]
[TR]
[TD]SUM(ISNULL([Other Dis2],0)) as [Total Discount Value][/TD]
[/TR]
[TR]
[TD]FROM[/TD]
[/TR]
[TR]
[TD]([/TD]
[/TR]
[TR]
[TD]SELECT[/TD]
[/TR]
[TR]
[TD] TH.[ID],[/TD]
[/TR]
[TR]
[TD]Cast(Floor(Cast(TH.<wbr>CallStartTime as float)) as Datetime) as Date,[/TD]
[/TR]
[TR]
[TD]Outcome.NewField,[/TD]
[/TR]
[TR]
[TD]Outcome.Value,[/TD]
[/TR]
[TR]
[TD]TKSub.AcceptedFuelRet,[/TD]
[/TR]
[TR]
[TD]TKSub.AcceptedFuelSale,[/TD]
[/TR]
[TR]
[TD]TKSub.AcceptedDuelFuelSale,[/TD]
[/TR]
[TR]
[TD]CT.[Mapping Department] as CallGroup,[/TD]
[/TR]
[TR]
[TD]CT.[Call] as [CallType],[/TD]
[/TR]
[TR]
[TD]RIGHT(TH.CreatedBy, LEN(TH.CreatedBy) - CHARINDEX('\',TH.CreatedBy)) as CreatedBy[/TD]
[/TR]
[TR]
[TD] FROM[/TD]
[/TR]
[TR]
[TD] [DAVE_ODS_ISSAC].[dbo].[vw_<wbr>BGSRT_TransactionHeader] TH[/TD]
[/TR]
[TR]
[TD]LEFT JOIN[/TD]
[/TR]
[TR]
[TD][DAVE_ODS_ISSAC].[dbo].[vw_<wbr>BGSRT_ParameterDetail] PD[/TD]
[/TR]
[TR]
[TD]ON TH.[ParameterDetailCallTypeID] = [URL="http://pd.id/"]PD.ID[/URL] and PD.ParameterMasterID = 1 and (PD.Archived = 'N'or PD.ArchivedDate >= TH.CallStartTime) and TH.SchemaName = PD.SchemaName[/TD]
[/TR]
[TR]
[TD]LEFT JOIN[/TD]
[/TR]
[TR]
[TD][DAVE_ODS_ISSAC].[dbo].[vw_<wbr>BGSRT_ParameterDetail] PD3[/TD]
[/TR]
[TR]
[TD]ON TH.[<wbr>ParameterDetailsCallTriggerID] = [URL="http://pd3.id/"]PD3.ID[/URL] and PD3.ParameterMasterID = 3 and (PD3.Archived = 'N'or PD3.ArchivedDate >= TH.CallStartTime)and TH.SchemaName = PD3.SchemaName[/TD]
[/TR]
[TR]
[TD]  LEFT JOIN[/TD]
[/TR]
[TR]
[TD](SELECT[/TD]
[/TR]
[TR]
[TD]  [URL="http://tk.id/"]TK.ID[/URL],[/TD]
[/TR]
[TR]
[TD]  SUM(CASE WHEN TK.Accepted IN ('POMP v3 (Retention)') THEN 1 ELSE 0 END) as [AcceptedFuelRet],[/TD]
[/TR]
[TR]
[TD]  SUM(CASE WHEN TK.Accepted IN ('C&S +£200', 'POMP V3 Sale (E)', 'POMP V3 Sale (G)', 'Discount Variable August 2013') THEN 1 ELSE 0 END) as [AcceptedFuelSale],[/TD]
[/TR]
[TR]
[TD]  SUM(CASE WHEN TK.Accepted IN ('POMP V3 Sale (DF)') THEN 1 ELSE 0 END) as [AcceptedDuelFuelSale][/TD]
[/TR]
[TR]
[TD]  FROM[/TD]
[/TR]
[TR]
[TD]  ([/TD]
[/TR]
[TR]
[TD]  SELECT[/TD]
[/TR]
[TR]
[TD]  [URL="http://th.id/"]TH.ID[/URL],[/TD]
[/TR]
[TR]
[TD]  CASE WHEN TKD.Offered= 'Y' THEN TK.Description ELSE NULL END AS Offered,[/TD]
[/TR]
[TR]
[TD]  MAX(CASE WHEN TKD.Accepted= 'Y' THEN TK.Description ELSE NULL END) AS Accepted[/TD]
[/TR]
[TR]
[TD]  FROM[/TD]
[/TR]
[TR]
[TD]  [DAVE_ODS_ISSAC].[dbo].[vw_<wbr>BGSRT_TransactionHeader] TH[/TD]
[/TR]
[TR]
[TD]  INNER JOIN[/TD]
[/TR]
[TR]
[TD]  [DAVE_ODS_ISSAC].[dbo].[vw_<wbr>BGSRT_ToolKitDetail] TKD[/TD]
[/TR]
[TR]
[TD]  ON [URL="http://th.id/"]TH.ID[/URL] = TKD.TransactionHeaderID and TH.SchemaName = TKD.SchemaName[/TD]
[/TR]
[TR]
[TD]  INNER JOIN[/TD]
[/TR]
[TR]
[TD]  [DAVE_ODS_ISSAC].[dbo].[vw_<wbr>BGSRT_ToolKit] TK[/TD]
[/TR]
[TR]
[TD]  ON TKD.ToolkitID = [URL="http://tk.id/"]TK.ID[/URL] and (TK.Archived = 'N' or TK.ArchivedDate >= TH.CallStartTime)  and TKD.SchemaName = TK.SchemaName[/TD]
[/TR]
[TR]
[TD]  WHERE[/TD]
[/TR]
[TR]
[TD]  TH.[CallStartTime] > = @startdate[/TD]
[/TR]
[TR]
[TD]  and TH.[CallStartTime] < @enddate[/TD]
[/TR]
[TR]
[TD]  and ( TH.schemaname = 'Issac_New')[/TD]
[/TR]
[TR]
[TD]  AND TKD.Accepted= 'Y'[/TD]
[/TR]
[TR]
[TD]  and TK.Description IN ('POMP v3 (Retention)', 'C&S +£200', 'POMP V3 Sale (DF)', 'POMP V3 Sale (E)', 'POMP V3 Sale (G)', 'Discount Variable August 2013')[/TD]
[/TR]
[TR]
[TD]GROUP BY[/TD]
[/TR]
[TR]
[TD]  [URL="http://th.id/"]TH.ID[/URL],[/TD]
[/TR]
[TR]
[TD]  CASE WHEN TKD.Offered= 'Y' THEN TK.Description ELSE NULL END[/TD]
[/TR]
[TR]
[TD]  ) TK[/TD]
[/TR]
[TR]
[TD]GROUP BY[/TD]
[/TR]
[TR]
[TD][URL="http://tk.id/"]TK.ID[/URL][/TD]
[/TR]
[TR]
[TD]) TKSub[/TD]
[/TR]
[TR]
[TD]ON TKSub.ID = [URL="http://th.id/"]TH.ID[/URL][/TD]
[/TR]
[TR]
[TD] LEFT JOIN[/TD]
[/TR]
[TR]
[TD]([/TD]
[/TR]
[TR]
[TD]([/TD]
[/TR]
[TR]
[TD]SELECT[/TD]
[/TR]
[TR]
[TD][URL="http://th.id/"]TH.ID[/URL],[/TD]
[/TR]
[TR]
[TD]TH.Schemaname,[/TD]
[/TR]
[TR]
[TD]CASE WHEN P.Description NOT IN  ('KAC', 'KAC Excess') AND P.[IsAppliance] = 'N' THEN P.Description + ' ' + (CASE WHEN PD2.Code = 'NRET' Then 'NonRet' ELSE PD2.Code End)[/TD]
[/TR]
[TR]
[TD] WHEN P_appl.description IN ('KAC', 'KAC Excess') THEN[/TD]
[/TR]
[TR]
[TD](CASE WHEN P.[IsAppliance] = 'Y' THEN P_appl.description + '_A ' + (CASE WHEN PD2.Code = 'NRET' Then 'NonRet' ELSE PD2.Code End)[/TD]
[/TR]
[TR]
[TD] ELSE P.description + ' ' + (CASE WHEN PD2.Code = 'NRET' Then 'NonRet' ELSE PD2.Code End) END)[/TD]
[/TR]
[TR]
[TD]ELSE NULL END as NewField,[/TD]
[/TR]
[TR]
[TD]CASE WHEN P.Description NOT IN ('KAC', 'KAC Excess') AND P.[IsAppliance] = 'N' THEN 1[/TD]
[/TR]
[TR]
[TD] WHEN P_appl.description IN ('KAC', 'KAC Excess')  THEN 1 ELSE 0 END as Value[/TD]
[/TR]
[TR]
[TD]  FROM[/TD]
[/TR]
[TR]
[TD]  [DAVE_ODS_ISSAC].[dbo].[vw_<wbr>BGSRT_TransactionHeader] TH[/TD]
[/TR]
[TR]
[TD]  LEFT JOIN[/TD]
[/TR]
[TR]
[TD][DAVE_ODS_ISSAC].[dbo].[vw_<wbr>BGSRT_ProductStatus] PS[/TD]
[/TR]
[TR]
[TD]  ON PS.[TransactionHeaderID] = [URL="http://th.id/"]TH.ID[/URL] and TH.SchemaName = PS.SchemaName[/TD]
[/TR]
[TR]
[TD]   LEFT JOIN[/TD]
[/TR]
[TR]
[TD][DAVE_ODS_ISSAC].[dbo].[vw_<wbr>BGSRT_Product]P[/TD]
[/TR]
[TR]
[TD]  ON  PS.ProductMasterID = [URL="http://p.id/"]P.ID[/URL] and  (P.Archived = 'N'or P.ArchivedDate >= TH.CallStartTime) and PS.SchemaName = P.SchemaName[/TD]
[/TR]
[TR]
[TD]   LEFT JOIN[/TD]
[/TR]
[TR]
[TD][DAVE_ODS_ISSAC].[dbo].[vw_<wbr>BGSRT_Product]P_Appl[/TD]
[/TR]
[TR]
[TD]  ON  PS.Productcode = P_Appl.code and  (P_Appl.Archived = 'N'or P_Appl.ArchivedDate >= TH.CallStartTime) and PS.SchemaName = P_Appl.SchemaName[/TD]
[/TR]
[TR]
[TD]  LEFT JOIN[/TD]
[/TR]
[TR]
[TD]  [DAVE_ODS_ISSAC].[dbo].[vw_<wbr>BGSRT_ParameterDetail] PD2[/TD]
[/TR]
[TR]
[TD]  ON ((PS.ParameterStatusID = [URL="http://pd2.id/"]PD2.ID[/URL] AND PD2.ParameterMasterID = 8 and (PD2.Archived = 'N'or PD2.ArchivedDate >= TH.CallStartTime) and P.SchemaName = PD2.SchemaName))[/TD]
[/TR]
[TR]
[TD]  WHERE[/TD]
[/TR]
[TR]
[TD]  TH.[CallStartTime] > = @startdate[/TD]
[/TR]
[TR]
[TD]  and TH.[CallStartTime] < @enddate[/TD]
[/TR]
[TR]
[TD]  and ([/TD]
[/TR]
[TR]
[TD]  TH.schemaname = 'Issac_New')[/TD]
[/TR]
[TR]
[TD]  )[/TD]
[/TR]
[TR]
[TD]  UNION ALL[/TD]
[/TR]
[TR]
[TD]   ([/TD]
[/TR]
[TR]
[TD]  SELECT[/TD]
[/TR]
[TR]
[TD]  [URL="http://th.id/"]TH.ID[/URL],[/TD]
[/TR]
[TR]
[TD]  TH.Schemaname,[/TD]
[/TR]
[TR]
[TD]  CASE WHEN PD2.Code = 'RET' AND P.[IsAppliance] = 'N'  THEN P.Description + ' ' + 'Dis2' ELSE NULL END as NewField,[/TD]
[/TR]
[TR]
[TD]  CASE WHEN PD2.Code = 'RET' AND P.[IsAppliance] = 'N'  THEN PS.Value ELSE NULL END as Value[/TD]
[/TR]
[TR]
[TD]  FROM[/TD]
[/TR]
[TR]
[TD]  [DAVE_ODS_ISSAC].[dbo].[vw_<wbr>BGSRT_TransactionHeader] TH[/TD]
[/TR]
[TR]
[TD]  LEFT JOIN[/TD]
[/TR]
[TR]
[TD][DAVE_ODS_ISSAC].[dbo].[vw_<wbr>BGSRT_ProductStatus] PS[/TD]
[/TR]
[TR]
[TD]  ON PS.[TransactionHeaderID] = [URL="http://th.id/"]TH.ID[/URL] and TH.SchemaName = PS.SchemaName[/TD]
[/TR]
[TR]
[TD]   LEFT JOIN[/TD]
[/TR]
[TR]
[TD][DAVE_ODS_ISSAC].[dbo].[vw_<wbr>BGSRT_Product]P[/TD]
[/TR]
[TR]
[TD]  ON  PS.ProductMasterID = [URL="http://p.id/"]P.ID[/URL] and  (P.Archived = 'N'or P.ArchivedDate >= TH.CallStartTime) and PS.SchemaName = P.SchemaName[/TD]
[/TR]
[TR]
[TD]   LEFT JOIN[/TD]
[/TR]
[TR]
[TD][DAVE_ODS_ISSAC].[dbo].[vw_<wbr>BGSRT_Product]P_Appl[/TD]
[/TR]
[TR]
[TD]  ON  PS.Productcode = P_Appl.code and  (P_Appl.Archived = 'N'or P_Appl.ArchivedDate >= TH.CallStartTime) and PS.SchemaName = P_Appl.SchemaName[/TD]
[/TR]
[TR]
[TD]  LEFT JOIN[/TD]
[/TR]
[TR]
[TD]  [DAVE_ODS_ISSAC].[dbo].[vw_<wbr>BGSRT_ParameterDetail] PD2[/TD]
[/TR]
[TR]
[TD]  ON ((PS.ParameterStatusID = [URL="http://pd2.id/"]PD2.ID[/URL] AND PD2.ParameterMasterID = 8 and (PD2.Archived = 'N'or PD2.ArchivedDate >= TH.CallStartTime) and P.SchemaName = PD2.SchemaName))[/TD]
[/TR]
[TR]
[TD]  WHERE[/TD]
[/TR]
[TR]
[TD]  TH.[CallStartTime] > = @startdate[/TD]
[/TR]
[TR]
[TD]  and TH.[CallStartTime] < @enddate[/TD]
[/TR]
[TR]
[TD]  and ([/TD]
[/TR]
[TR]
[TD]  TH.schemaname = 'Issac_New')[/TD]
[/TR]
[TR]
[TD]  )[/TD]
[/TR]
[TR]
[TD]) Outcome[/TD]
[/TR]
[TR]
[TD]On Outcome.ID = [URL="http://th.id/"]TH.ID[/URL] and Outcome.SchemaName = TH.SchemaName[/TD]
[/TR]
[TR]
[TD] LEFT JOIN[/TD]
[/TR]
[TR]
[TD]TAMI_Lookups.dbo.MI_<wbr>tblManpower_Mapping CT[/TD]
[/TR]
[TR]
[TD]ON[/TD]
[/TR]
[TR]
[TD] PD.SubTypeDescription = CT.[OPS ALL][/TD]
[/TR]
[TR]
[TD] WHERE[/TD]
[/TR]
[TR]
[TD] TH.[CallStartTime] > = @startdate[/TD]
[/TR]
[TR]
[TD]and TH.[CallStartTime] < @enddate[/TD]
[/TR]
[TR]
[TD]and (TH.schemaname = 'Issac_New')[/TD]
[/TR]
[TR]
[TD]and CT.Dept_name = 'IssacCallTypes'[/TD]
[/TR]
[TR]
[TD]and CT.[Mapping Department] IN ('Pro Active Outbound','Customer Care','Customer Care Admin','Home Move','Home Move Admin','Other Activity','Pro Active Outbound','Reactive Outbound')[/TD]
[/TR]
[TR]
[TD]and ((CT.CALL IN ('Customer Care', 'First Year', 'Web Chat', 'Customer Care Admin', 'Home Move',[/TD]
[/TR]
[TR]
[TD]                  'Home Move Admin', 'Other Activity', 'Post Landlords', 'Post Save In', 'PRE_CASH', 'PRE_DDEB', 'PRE_GUAR', 'PRE_Landlords', 'Welcome CHC', 'Welcome E&DS',[/TD]
[/TR]
[TR]
[TD]                  'Payment Rejections', 'Renf'))[/TD]
[/TR]
[TR]
[TD]                  or CT.CALL like 'Landlord%')[/TD]
[/TR]
[TR]
[TD]  ) ISSACtest[/TD]
[/TR]
[TR]
[TD]PIVOT (SUM(value) FOR NewField IN ([/TD]
[/TR]
[TR]
[TD][CHC HELD], [CHC REM], [CHC RET], [CHC NonRet],[CHC DIS2],[/TD]
[/TR]
[TR]
[TD][GAC HELD], [GAC REM], [GAC RET], [GAC NonRet],[GAC DIS2],[/TD]
[/TR]
[TR]
[TD] [KAC_A HELD], [KAC_A REM], [KAC_A RET], [KAC_A NonRet], [KAC RET], [KAC DIS2],[/TD]
[/TR]
[TR]
[TD][HEC HELD], [HEC REM], [HEC RET], [HEC NonRet],[HEC DIS2],[/TD]
[/TR]
[TR]
[TD][PAD HELD], [PAD REM], [PAD RET], [PAD NonRet],[PAD DIS2],[/TD]
[/TR]
[TR]
[TD] [CHC Excess HELD], [CHC Excess REM], [CHC Excess RET], [CHC Excess NonRet],[CHC Excess DIS2],[/TD]
[/TR]
[TR]
[TD] [GAC Excess HELD], [GAC Excess REM], [GAC Excess RET], [GAC Excess NonRet],[GAC Excess DIS2],[/TD]
[/TR]
[TR]
[TD] [KAC Excess_A HELD], [KAC Excess_A REM], [KAC Excess_A RET], [KAC Excess_A NonRet],[KAC Excess RET], [KAC Excess DIS2],[/TD]
[/TR]
[TR]
[TD] [HEC Excess HELD], [HEC Excess REM], [HEC Excess RET], [HEC Excess NonRet],[HEC Excess DIS2],[/TD]
[/TR]
[TR]
[TD] [PAD Excess HELD], [PAD Excess REM], [PAD Excess RET], [PAD Excess NonRet],[PAD Excess DIS2],[/TD]
[/TR]
[TR]
[TD] [Landlords HELD], [Landlords REM], [Landlords RET], [Landlords NonRet],[Landlords DIS2],[/TD]
[/TR]
[TR]
[TD] [Other HELD], [Other REM], [Other RET], [Other NonRet],[Other DIS2][/TD]
[/TR]
[TR]
[TD])) as Pivot1[/TD]
[/TR]
[TR]
[TD]GROUP BY[/TD]
[/TR]
[TR]
[TD]CreatedBy,[/TD]
[/TR]
[TR]
[TD]ID,[/TD]
[/TR]
[TR]
[TD]Date,[/TD]
[/TR]
[TR]
[TD]Callgroup,[/TD]
[/TR]
[TR]
[TD]CallType,[/TD]
[/TR]
[TR]
[TD]AcceptedfuelRet,[/TD]
[/TR]
[TR]
[TD]AcceptedfuelSale,[/TD]
[/TR]
[TR]
[TD]AcceptedDuelFuelSale[/TD]
[/TR]
[TR]
[TD])I[/TD]
[/TR]
[TR]
[TD])I2[/TD]
[/TR]
[TR]
[TD]LEFT JOIN[/TD]
[/TR]
[TR]
[TD] Manp[/TD]
[/TR]
[TR]
[TD] on I2.CreatedBy = manp.[Lan][/TD]
[/TR]
[TR]
[TD]and  DATEADD(mm, DATEDIFF(mm,0,CAST(I2.date as date)), 0) = manp.month[/TD]
[/TR]
[TR]
[TD]GROUP BY[/TD]
[/TR]
[TR]
[TD]manp.lan,[/TD]
[/TR]
[TR]
[TD]I2.date,[/TD]
[/TR]
[TR]
[TD]I2.CallGroup,[/TD]
[/TR]
[TR]
[TD]I2.CallType[/TD]
[/TR]
</tbody>[/TABLE]

Code: 
Sub RunSQL(SQLRange As String, SQLName As String, TargetSheet As String, targetrange As String)    Dim theQueryText As String
    theQueryText = ""
    
    ThisWorkbook.Activate
        Sheets(SQLSheet).Calculate
        
      Sheets(SQLSheet).Select
     StartSQL = Now
    
   
        Range(SQLRange).Select
            While ActiveCell.Value2 <> ""
                theQueryText = theQueryText + ActiveCell.Value2
                ActiveCell.Offset(1, 0).Select
            Wend
        
    Sheets(TargetSheet).Select
        Range(targetrange).Select
     
            With Selection.QueryTable
            .Connection = _
            TamiConnect & ";Database=TAMI;app=" & ThisWorkbook.Name & ";Trusted_Connection=yes;"
           ' .Connection = _
            "ODBC;DRIVER=SQL Server;SERVER=cnwp0145\tamiweekly;app=" & SQLName & ";Trusted_Connection=yes;"
            .SQL = theQueryText
            .Refresh BackgroundQuery:=False
        End With
        
             EndSQL = Now
        
        Call logSQL(SQLRange)
     End Sub
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
You must log in or register to reply here.

Forum statistics

Threads
1,214,921
Messages
6,122,280
Members
449,075
Latest member
staticfluids

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top