|
ramnaidu
Starting Member
1 Post |
 Posted - 2014-05-22 : 10:37:07
|
| Dear allHope you all are doing good.Please have a look at the below query can you please ket me know where do i put where condition so that I can have the details of Dept name with 'Telesales' only and where do i keep group function so that the output is filtered as per the agent name. Thanks for your help in advance, please note that I have limited knowledge of SQL. SET NOCOUNT ON DECLARE @StartDate AS DATETIME; DECLARE @EndDate AS DATETIME; DECLARE @Yesterday AS DATETIME; SELECT @StartDate = '28/04/2014'; SELECT @EndDate = '26/05/2014'; SELECT @Yesterday = '21/05/2014'; With Manp AS (SELECT RTRIM(REPLACE(agent.Emp_SK, '.0', '')) AS Emp_SK, agent.DatasourceId, agent.Forename + ' ' + agent.Surname AS Agent, agent.Staff_id AS LAN, agent.PayrollNumber AS Pay, agent.PIN, agent.PIN2, agent.Grade, agent.Hours_PW, CASE WHEN agent.[Office_Name] = 'BGS Cardiff' THEN 'Car' WHEN agent.[Office_Name] = 'Cardiff Serco' THEN 'Ser' ELSE LEFT(agent.[Office_Name], 3) END AS Site, agent.PFW_Group, agent.Dept_Name AS Dept, RTRIM(REPLACE(agent.LineManagerSK, '.0', '')) AS LineManagerSK, agent.LineManager AS TM, tm.PayrollNumber AS [TM Pay], RTRIM(REPLACE(agent.OpsManagerSK, '.0', '')) AS OpsManagerSK, agent.OpsManager AS CSM, csm.PayrollNumber AS [CSM Pay]FROM TAMI.dbo.STD_EWFM_Manpower_Snapshot AS agent LEFT OUTER JOIN TAMI.dbo.STD_EWFM_Manpower_Snapshot AS tm ON agent.LineManagerSK = tm.Emp_SK AND agent.DatasourceId = tm.DatasourceId LEFT OUTER JOIN TAMI.dbo.STD_EWFM_Manpower_Snapshot AS csm ON agent.OpsManagerSK = csm.Emp_SK AND agent.DatasourceId = csm.DatasourceId ) , Manp2 AS ( SELECT RTRIM(REPLACE(agent.[emp_sk],'.0','')) AS [Emp_SK], agent.[First_name] + ' ' + agent.[Last_name] AS [Agent], agent.[LanID] AS [LAN], agent.[Payroll] AS [Pay], agent.[Pin], agent.[Pin2], agent.[Grade], agent.[Hours_PW], CASE WHEN agent.[Site] = 'BGS Cardiff' then 'Car' when agent.[Site] = 'Cardiff Serco' then 'Ser' else left(agent.[Site],3) End AS [Site], agent.[Activity], agent.[Department] AS [Dept], RTRIM(REPLACE(agent.[LineMangerSK],'.0','')) AS [LineManagerSK], agent.[LineManager] AS [TM], tm.[PayrollNumber] AS [TM Pay], RTRIM(REPLACE(agent.[OpsManagerSK],'.0','')) AS [OpsManagerSK], agent.[OpsManager] AS [CSM], csm.[PayrollNumber] AS [CSM Pay], agent.thedate FROM [TAMI].[dbo].[STD_EWFM_Manpower] agent LEFT JOIN [TAMI].[dbo].[STD_EWFM_Manpower_Snapshot] tm ON agent.[LineMangerSK] = tm.[emp_sk] and agent.datasourceID = tm.datasourceID LEFT JOIN [TAMI].[dbo].[STD_EWFM_Manpower_Snapshot] csm ON agent.[OpsManagerSK] = csm.[emp_sk] and agent.datasourceID = csm.datasourceID ) /*To list response types */ , OC AS ( (SELECT 'No' AS [ResponseType], 'Pot' AS [Outcome]) UNION ALL (SELECT 'Yes', 'Pot') UNION ALL (SELECT 'Yes', 'Act') ) /*START OF SQL*/ SELECT CASE WHEN T.[Name] IN ('5 Cust Outcomes (BGS CEC)', '5 Cust Outcomes (BGS CEC Reaccreditation', '5 Cust Outcomes (BGS CEC Graduation)', '5 Cust Outcomes (BGS CEC) Edin', 'Dyno Key Accounts', 'Dyno Secure', 'Dyno on Demand Drains Edin', 'Dyno on Demand Plumbing Edin', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '') THEN 'CEC' WHEN T.[Name] IN ('BGS Model Call', 'BGS Model Call (Graduation)', 'BGS Model Call (Dyno Key Accounts)', 'BGS Model Call (Dyno Secure)', 'BGS Model Call (Dyno On Demand)', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '') THEN 'Model Call' WHEN T.[Name] IN ('BGWOS', 'BGWOS (Graduation)', 'BGWOS (RE-accreditation)', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '') THEN 'BGWOS' ELSE NULL END as [QA Scheme], pt.Sale as [Sale Made], manp.[Emp_SK], manp.[Site], manp.[Dept], manp.[CSM], manp.[TM], manp2.[Site], manp2.[Dept], manp2.[CSM], manp2.[TM], 'Normal' AS [Agent Type], manp.[Lan], CASE WHEN manp.[Pin2] IS NULL THEN manp.[Pin] ELSE manp.[Pin] + ', ' + manp.[Pin2] END AS [Pins], manp.[Pay], manp.[Agent], A.[AssessmentID] AS [ID], CASE WHEN A.AuditedAssessmentID IS NOT NULL AND A.AuditedAssessmentID <> '0' THEN 'Yes' ELSE NULL END as [Is Audit?], CASE WHEN A.AuditedAssessmentID IS NOT NULL AND A.AuditedAssessmentID <> '0' THEN A.AuditedAssessmentID ELSE NULL END as [Audited ID], CASE WHEN A.[DetrimentStatus] IN ('Open', 'Closed') THEN 'Yes' ELSE '' END AS [Revisit Trigger], CASE WHEN A.[Classification] = 1 AND AQR2.[Total Act] = AQR2.[Total Pot] THEN 'Great Call' WHEN A.[Classification] = 1 AND AQR2.[Total Act] < AQR2.[Total Pot] THEN 'Customer Experience Development' WHEN A.[Classification] = 2 AND A.[DetrimentStatus] NOT IN ('Open', 'Closed') THEN 'Regulatory Development' WHEN A.[Classification] = 2 AND A.[DetrimentStatus] IN ('Open', 'Closed') THEN 'Unclear Outcome' WHEN A.[Classification] = 3 THEN 'Unclear Outcome' WHEN A.[Classification] = 4 THEN 'Unsuitable Outcome' END AS Outcome, T.[Name] AS [Template], AT.[Name] AS [Assessment Type], CL.[Name] AS [Call Location], A.[WitnessID], null as [Spare1], null as [Spare2], RIGHT(A.[CreatedBy], LEN(A.[CreatedBy]) - CHARINDEX('\',A.[CreatedBy])) AS [Created By], A.[CreatedDate] AS [Created Date], (cast(datepart(dy, A.[CreatedDate] - datepart(dw, A.[CreatedDate]) + 4) as Int) + 6)/7 as [Week No.], AQR2.[Total Act], AQR2.[Total Pot], CASE WHEN AQR2.[Total Pot] = 0 THEN 0 ELSE AQR2.[Total Act]/AQR2.[Total Pot] END AS [Overall Score], CASE WHEN (CASE WHEN AQR2.[Total Pot] = 0 THEN 0 ELSE AQR2.[Total Act]/AQR2.[Total Pot] END) >= 1 THEN 'Platinum' WHEN (CASE WHEN AQR2.[Total Pot] = 0 THEN 0 ELSE AQR2.[Total Act]/AQR2.[Total Pot] END) >= 0.95 THEN 'Gold' WHEN (CASE WHEN AQR2.[Total Pot] = 0 THEN 0 ELSE AQR2.[Total Act]/AQR2.[Total Pot] END) >= 0.90 THEN 'Silver' WHEN (CASE WHEN AQR2.[Total Pot] = 0 THEN 0 ELSE AQR2.[Total Act]/AQR2.[Total Pot] END) >= 0.70 THEN 'Bronze' ELSE 'Ungraded' END AS [Quality Band], AQR2.[1_A], AQR2.[1_B], AQR2.[1_C], AQR2.[1_D], AQR2.[1_E], AQR2.[1_F], AQR2.[1_G], AQR2.[1_H], AQR2.[1_I], AQR2.[1_J], AQR2.[1 Act], AQR2.[1 Pot], CASE WHEN ISNULL(AQR2.[1 Pot],0) = 0 THEN 0 ELSE ISNULL(AQR2.[1 Act],0)/ISNULL(AQR2.[1 Pot],0) END AS [1 Score], AQR2.[2_A], AQR2.[2_B], AQR2.[2_C], AQR2.[2_D], AQR2.[2_E], AQR2.[2_F], AQR2.[2_G], AQR2.[2_H], AQR2.[2_I], AQR2.[2_J], AQR2.[2 Act], AQR2.[2 Pot], CASE WHEN ISNULL(AQR2.[2 Pot],0) = 0 THEN 0 ELSE ISNULL(AQR2.[2 Act],0)/ISNULL(AQR2.[2 Pot],0) END AS [2 Score], AQR2.[3_A], AQR2.[3_B], AQR2.[3_C], AQR2.[3_D], AQR2.[3_E], AQR2.[3_F], AQR2.[3_G], AQR2.[3_H], AQR2.[3_I], AQR2.[3_J], AQR2.[3 Act], AQR2.[3 Pot], CASE WHEN ISNULL(AQR2.[3 Pot],0) = 0 THEN 0 ELSE ISNULL(AQR2.[3 Act],0)/ISNULL(AQR2.[3 Pot],0) END AS [3 Score], AQR2.[4_A], AQR2.[4_B], AQR2.[4_C], AQR2.[4_D], AQR2.[4_E], AQR2.[4_F], AQR2.[4_G], AQR2.[4_H], AQR2.[4_I], AQR2.[4_J], AQR2.[4 Act], AQR2.[4 Pot], CASE WHEN ISNULL(AQR2.[4 Pot],0) = 0 THEN 0 ELSE ISNULL(AQR2.[4 Act],0)/ISNULL(AQR2.[4 Pot],0) END AS [4 Score], AQR2.[5_A], AQR2.[5_B], AQR2.[5_C], AQR2.[5_D], AQR2.[5_E], AQR2.[5_F], AQR2.[5_G], AQR2.[5_H], AQR2.[5_I], AQR2.[5_J], AQR2.[5 Act], AQR2.[5 Pot], CASE WHEN ISNULL(AQR2.[5 Pot],0) = 0 THEN 0 ELSE ISNULL(AQR2.[5 Act],0)/ISNULL(AQR2.[5 Pot],0) END AS [5 Score], AQR2.[6_A], AQR2.[6_B], AQR2.[6_C], AQR2.[6_D], AQR2.[6_E], AQR2.[6_F], AQR2.[6_G], AQR2.[6_H], AQR2.[6_I], AQR2.[6_J], AQR2.[6 Act], AQR2.[6 Pot], CASE WHEN ISNULL(AQR2.[6 Pot],0) = 0 THEN 0 ELSE ISNULL(AQR2.[6 Act],0)/ISNULL(AQR2.[6 Pot],0) END AS [6 Score], AQR2.[7_A], AQR2.[7_B], AQR2.[7_C], AQR2.[7_D], AQR2.[7_E], AQR2.[7_F], AQR2.[7_G], AQR2.[7_H], AQR2.[7_I], AQR2.[7_J], AQR2.[7 Act], AQR2.[7 Pot], CASE WHEN ISNULL(AQR2.[7 Pot],0) = 0 THEN 0 ELSE ISNULL(AQR2.[7 Act],0)/ISNULL(AQR2.[7 Pot],0) END AS [7 Score], AQR2.[8_A], AQR2.[8_B], AQR2.[8_C], AQR2.[8_D], AQR2.[8_E], AQR2.[8_F], AQR2.[8_G], AQR2.[8_H], AQR2.[8_I], AQR2.[8_J], AQR2.[8 Act], AQR2.[8 Pot], CASE WHEN ISNULL(AQR2.[8 Pot],0) = 0 THEN 0 ELSE ISNULL(AQR2.[8 Act],0)/ISNULL(AQR2.[8 Pot],0) END AS [8 Score], AQR2.[9_A], AQR2.[9_B], AQR2.[9_C], AQR2.[9_D], AQR2.[9_E], AQR2.[9_F], AQR2.[9_G], AQR2.[9_H], AQR2.[9_I], AQR2.[9_J], AQR2.[9 Act], AQR2.[9 Pot], CASE WHEN ISNULL(AQR2.[9 Pot],0) = 0 THEN 0 ELSE ISNULL(AQR2.[9 Act],0)/ISNULL(AQR2.[9 Pot],0) END AS [9 Score], pt.HC400, pt.HC300, pt.HC200, pt.[HC100], pt.CHC, pt.CHBC, pt.[B&CBC], pt.[B&CC], pt.[R&C], pt.Bundle , pt.[P&D], pt.hec , pt.GAC , pt.Kac, pt.WSP, pt.GLRP, pt.[HC400 Flexi], pt.[HC300Flexi], pt.[HC200 Flexi], pt.[HC100 Flexi], pt.[CHC Flexi] , pt.[CHBC Flexi] , pt.[B&CBC Flexi] , pt.[B&CC Flexi] , pt.[Bundle Flexi] , pt.[HEC Flexi] , pt.[P&D Flexi] , pt.[GAC Flexi] , pt.[KAC Flexi] , pt.MISSING as Other FROM ( SELECT DISTINCT CASE WHEN audit.[AuditedAssessmentID] IS NULL THEN orig.[AssessmentID] ELSE audit.[AssessmentID] END AS [AssessmentID] FROM (SELECT A.* FROM [QAMS].[dbo].[BGSQAV3_Assessment] A INNER JOIN [QAMS].[dbo].[BGSQAV3_Template] T ON A.[TemplateID] = T.[TemplateID] WHERE A.[IsArchived] <> 1 AND A.[CreatedDate]>= @StartDate AND A.[CreatedDate] < @EndDate AND (T.[Name] IN ('5 Cust Outcomes (BGS CEC)', '5 Cust Outcomes (BGS CEC Reaccreditation', '5 Cust Outcomes (BGS CEC Graduation)', '5 Cust Outcomes (BGS CEC) Edin', 'Dyno Key Accounts', 'Dyno Secure', 'Dyno on Demand Drains Edin', 'Dyno on Demand Plumbing Edin', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '') OR T.[Name] IN ('BGS Model Call', 'BGS Model Call (Graduation)', 'BGS Model Call (Dyno Key Accounts)', 'BGS Model Call (Dyno Secure)', 'BGS Model Call (Dyno On Demand)', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '') OR T.[Name] IN ('BGWOS', 'BGWOS (Graduation)', 'BGWOS (RE-accreditation)', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '') )AND A.[AssessmentID] NOT IN ('299', '302') AND A.[AuditedAssessmentID] = 0 ) orig LEFT JOIN (SELECT A.* FROM [QAMS].[dbo].[BGSQAV3_Assessment] A WHERE [IsArchived] <> 1 AND A.[CreatedDate] >= @StartDate AND A.[CreatedDate] < @EndDate) audit ON orig.[AssessmentID] = audit.[AuditedAssessmentID] ) Included_A /*AH-The [Included_A] sub returns a distinct list of Assessment ID's removing deletions and if an audit has been carried out pulling through the audit */ INNER JOIN [QAMS].[dbo].[BGSQAV3_Assessment] A ON Included_A.[AssessmentID] = A.[AssessmentID] INNER JOIN [QAMS].[dbo].[BGSQAV3_Template] T ON A.[TemplateID] = T.[TemplateID] INNER JOIN [QAMS].[dbo].[BGSQAV3_AssessmentType] AT ON A.[AssessmentTypeID] = AT.[AssessmentTypeID] INNER JOIN [QAMS].[dbo].[BGSQAV3_CallLocation] CL ON A.[CallLocationID] = CL.[CallLocationID] LEFT JOIN (SELECT APT.AssessmentID, Max(case when PT.Name = 'HC400' then 'Yes' else NUll end) as [HC400], Max(case when PT.Name = 'HC300' then 'Yes' else Null end )as [HC300], Max(case when PT.Name = 'HC200' then 'Yes' else Null end) as [HC200], Max(case when PT.Name = 'HC100' then 'Yes' else Null end) as [HC100], Max(case when PT.Name = 'CHC' then 'Yes' else Null end) as [CHC], Max(case when PT.name = 'CHBC' then 'Yes' else Null end) as [CHBC], Max(case when PT.Name = 'B&CC' then 'Yes' else null end) as [B&CC], Max(case when PT.Name = 'B&CBC' then 'Yes' else null end) as [B&CBC], Max(case when PT.name = 'Bundle' then 'Yes' else Null end) as [Bundle], Max(case when PT.Name = 'P&D' then 'Yes' else Null end) as [P&D], Max(case when PT.Name = 'HEC' then 'Yes' else Null end) as [HEC], Max(case when PT.Name = 'KAC' then 'Yes' else null end) as [Kac], Max(case when PT.name = 'GAC' then 'Yes' else Null end) as [GAC], Max(case when PT.Name = 'R&C' then 'Yes' else null end) as [R&C], Max(case when PT.name = 'WSP' then 'Yes' else Null end) as [WSP], Max(case when (PT.Name = 'HC400Flexi' OR PT.Name = 'HC400 Flexi') then 'Yes' else NUll end) as [HC400 Flexi], Max(case when (PT.Name = 'BundleFlexi' or PT.name ='Bundle Flexi') then 'Yes' else null end )as [Bundle Flexi], Max(case when PT.Name = 'GACFlexi' then 'Yes' else null end) as [GAC Flexi], Max(case when PT.Name = 'KACFlexi' then 'Yes' else null end) as [KAC Flexi], Max(Case when PT.Name = 'P&DFlexi' then 'Yes' else Null end) as [P&D Flexi], Max(case when (PT.Name = 'B&CCFlexi' or PT.Name = 'B&CC Flexi') then 'Yes' else null end) as [B&CC Flexi], Max(case when PT.Name = 'GLRP' then 'Yes' else Null end) as [GLRP], Max(case when PT.Name = 'CHCFlexi' then 'Yes' else Null end) as [CHC Flexi], Max(case when PT.Name = 'HECFlexi' then 'Yes' else Null end )as [HEC Flexi], Max(case when (PT.Name = 'HC100Flexi' OR PT.Name = 'HC100 Flexi') then 'Yes' else Null end) as [HC100 Flexi], Max(case when PT.Name = 'HC200 Flexi' then 'Yes' else NUll end) as [HC200 Flexi], Max(case when (PT.Name = 'HC300 Flexi' OR PT.Name = 'HC300Flexi') then 'Yes' else NUll end) as [HC300Flexi], Max(case when PT.name = 'CHBCFlexi' then 'Yes' else Null end) as [CHBC Flexi], Max(case when (PT.Name = 'B&CBCFlexi' or PT.Name = 'B&CBC Flexi') then 'Yes' else null end) as [B&CBC Flexi], max(case when PT.name not in('Bundle Flexi','B&CBCFlexi','HC100 Flexi','HC100Flexi','HC400 Flexi','CHBCFlexi','HC300Flexi' ,'HC300 Flexi','HC200 Flexi','HC200','HC400Flexi','HC100','HECFlexi','CHCFlexi','HE','B&CCFlexi','B&CBC','P&DFlexi','KAC', 'WSP', 'Bundle', 'CHBC', 'CHC', 'GAC', 'HC400','P&D', 'HEC', 'B&CC', 'R&C', 'BundleFlexi', 'GACFlexi','KACFlexi', 'HC300') then PT.Name end) AS [MISSING], max(case when PT.name is not null and PT.name not in ('SAP QA') then 'Yes' else null end) AS [Sale] FROM [QAMS].[dbo].[BGSQAV3_AssessmentProductType] APT LEFT JOIN [QAMS].[dbo].[BGSQAV3_ProductType] PT ON APT.[ProductTypeID] = PT.[ProductTypeID] GROUP BY APT.AssessmentID ) as PT ON A.[AssessmentID] = PT.[AssessmentID] INNER JOIN ( SELECT * FROM ( SELECT * FROM ( SELECT AQR.[AssessmentID], Q_Map.NewMap as [mapped], RT.[Name] FROM [QAMS].[dbo].[BGSQAV3_Assessment] A INNER JOIN [QAMS].[dbo].[BGSQAV3_AssessmentQuestionResponse] AQR ON A.AssessmentID = AQR.AssessmentID and A.[CreatedDate] >= @StartDate AND A.[CreatedDate] < @EndDate INNER JOIN [QAMS].[dbo].[BGSQAV3_ResponseType] RT ON AQR.[ResponseTypeID] = RT.[ResponseTypeID] INNER JOIN [QAMS].[dbo].[BGSQAV3_Question] Q ON AQR.[QuestionID] = Q.[QuestionID] INNER JOIN [QAMS].[dbo].[BGSQAV3_Section] S ON Q.[BaseSectionID] = S.[SectionID] INNER JOIN (SELECT Distinct [CALL] as [Question], [OPS All]as [NewMap] FROM [TAMI_Lookups].dbo.MI_tblManpower_Mapping WHERE Dept_name = 'QAScheme') Q_Map ON Q_Map.Question = CAST(Q.[Mapped] as varchar) ) Q PIVOT (MAX([Name]) FOR Mapped IN ( [1_A],[1_B],[1_C],[1_D],[1_E],[1_F],[1_G],[1_H],[1_I],[1_J], [2_A],[2_B],[2_C],[2_D],[2_E],[2_F],[2_G],[2_H],[2_I],[2_J], [3_A],[3_B],[3_C],[3_D],[3_E],[3_F],[3_G],[3_H],[3_I],[3_J], [4_A],[4_B],[4_C],[4_D],[4_E],[4_F],[4_G],[4_H],[4_I],[4_J], [5_A],[5_B],[5_C],[5_D],[5_E],[5_F],[5_G],[5_H],[5_I],[5_J], [6_A],[6_B],[6_C],[6_D],[6_E],[6_F],[6_G],[6_H],[6_I],[6_J], [7_A],[7_B],[7_C],[7_D],[7_E],[7_F],[7_G],[7_H],[7_I],[7_J], [8_A],[8_B],[8_C],[8_D],[8_E],[8_F],[8_G],[8_H],[8_I],[8_J], [9_A],[9_B],[9_C],[9_D],[9_E],[9_F],[9_G],[9_H],[9_I],[9_J] )) as Pivot1 ) Q_Sub LEFT JOIN ( SELECT *, ISNULL([1 Act],0) + ISNULL([2 Act],0) +ISNULL([3 Act],0) +ISNULL([4 Act],0) +ISNULL([5 Act],0) + ISNULL([6 Act],0) + ISNULL([7 Act],0) + ISNULL([8 Act],0) + ISNULL([9 Act],0) as [Total Act], ISNULL([1 Pot],0) + ISNULL([2 Pot],0) +ISNULL([3 Pot],0) +ISNULL([4 Pot],0) +ISNULL([5 Pot],0) + ISNULL([6 Pot],0) + ISNULL([7 Pot],0) + ISNULL([8 Pot],0) + ISNULL([9 Pot],0) as [Total Pot] FROM ( SELECT AQR.[AssessmentID] as [Assessment_ID], S_Map.NewMap + ' ' + OC.Outcome as [Section], SUM(Q.Score) as Score FROM [QAMS].[dbo].[BGSQAV3_AssessmentQuestionResponse] AQR INNER JOIN [QAMS].[dbo].[BGSQAV3_ResponseType] RT ON AQR.[ResponseTypeID] = RT.[ResponseTypeID] INNER JOIN [QAMS].[dbo].[BGSQAV3_Question] Q ON AQR.[QuestionID] = Q.[QuestionID] INNER JOIN [QAMS].[dbo].[BGSQAV3_Section] S ON Q.[BaseSectionID] = S.[SectionID] INNER JOIN OC ON OC.ResponseType = RT.Name INNER JOIN (SELECT Distinct [OPS P1-P3] as [Section], [ASC_SCMO] as [NewMap] FROM [TAMI_Lookups].dbo.MI_tblManpower_Mapping WHERE Dept_name = 'QAScheme') S_Map ON S_Map.[Section]= S.Name COLLATE Latin1_General_CI_AI GROUP BY AQR.[AssessmentID], S_Map.NewMap + ' ' + OC.Outcome ) S PIVOT (SUM([Score]) FOR Section IN ( [1 Act], [1 Pot], [2 Act], [2 Pot], [3 Act], [3 Pot], [4 Act], [4 Pot], [5 Act], [5 Pot], [6 Act], [6 Pot], [7 Act], [7 Pot], [8 Act], [8 Pot], [9 Act], [9 Pot] )) as Pivot1 ) S_Sub On Q_Sub.AssessmentID = S_Sub.Assessment_ID ) AQR2 ON A.[AssessmentID] = AQR2.[AssessmentID] LEFT JOIN Manp ON Manp.[Lan] = RIGHT(A.[AgentLANID], LEN(A.[AgentLANID]) - CHARINDEX('\',A.[AgentLANID])) COLLATE Latin1_General_CI_AI LEFT JOIN Manp2 ON Manp2.[Lan] = RIGHT(A.[AgentLANID], LEN(A.[AgentLANID]) - CHARINDEX('\',A.[AgentLANID])) COLLATE Latin1_General_CI_AI AND CAST(FLOOR(CAST(A.CreatedDate AS float)) AS DATETIME) = Manp2.Thedate order by a.AssessmentID |
|
Sql query script - please help