/* Survey by Supplier — Summary by Section (transactional / ReloAction) BackOffice: frmROLSurveySupplier2007 (cboRptFormat index 0) Crystal: NewSupplierSurveyReport.rpt DatawarehouseNCM has no survey-response fact. Client flags ROLSurveyQuestion / TESurveyDomestic / TESurveyInternational are setup indicators only, so this query runs on ReloAction. The report reads a per-user scratch table (tblTERptTempFile) filled by ReportManager.clsTransfereeSurvey.CreateTmpTable with the latest survey per transferee that has a response in the date range. That restriction is inlined below. User-initials and division-security filters are omitted. Score buckets match the Crystal running totals. Yes/No answers fold into Agree (Yes) and Disagree (No). NA (Range = 7) is excluded from ResponseCount and from the top-box percentages. */ DECLARE @StartDate datetime = '2026-01-01'; DECLARE @EndDate datetime = '2026-11-16'; DECLARE @CorpCode varchar(4) = ''; -- '' = all DECLARE @SrvcProvider varchar(4) = ''; -- '' = all (tblTEMain1.PrimaryContact) DECLARE @ProfitCenter varchar(2) = ''; -- '' = all DECLARE @CompanyName varchar(100) = '(All Suppliers)'; -- '(All Suppliers)' or '' = all DECLARE @SupplierType varchar(10) = ''; -- '' = all (vwTESurveyDetailReport.Service) ;WITH LatestSurvey AS ( SELECT sc.TEFileNo, MAX(sc.TESurveyID) AS TESurveyID FROM dbo.tblTESurveyControl sc INNER JOIN dbo.tblTEResponses r ON r.TESurveyID = sc.TESurveyID INNER JOIN dbo.tblQuestions q ON q.QuestionID = r.QuestionID WHERE r.CreatedOn >= @StartDate AND r.CreatedOn < DATEADD(day, 1, @EndDate) GROUP BY sc.TEFileNo ), Responses AS ( SELECT sec.SectionID, sec.SectionOrder, sec.Description AS SectionDescription, ql.QuestionLabel, q.QuestionID, q.QuestionOrder, q.SubQuestionOrder, q.QuestionText, q.ResponseType, sup.CompanyName, CASE WHEN q.ResponseType = 'Range' AND r.Response = 1 THEN 1 ELSE 0 END AS StronglyDisagree, CASE WHEN q.ResponseType = 'Range' AND r.Response = 2 THEN 1 WHEN q.ResponseType = 'YesNo' AND r.Response = 2 THEN 1 ELSE 0 END AS Disagree, CASE WHEN q.ResponseType = 'Range' AND r.Response = 3 THEN 1 ELSE 0 END AS SlightlyDisagree, CASE WHEN q.ResponseType = 'Range' AND r.Response = 4 THEN 1 ELSE 0 END AS SlightlyAgree, CASE WHEN q.ResponseType = 'Range' AND r.Response = 5 THEN 1 WHEN q.ResponseType = 'YesNo' AND r.Response = 1 THEN 1 ELSE 0 END AS Agree, CASE WHEN q.ResponseType = 'Range' AND r.Response = 6 THEN 1 ELSE 0 END AS StronglyAgree, CASE WHEN r.Response BETWEEN 1 AND 6 THEN 1 ELSE 0 END AS ResponseCount FROM LatestSurvey ls INNER JOIN dbo.tblTESurveyControl sc ON sc.TESurveyID = ls.TESurveyID INNER JOIN dbo.tblTEResponses r ON r.TESurveyID = sc.TESurveyID INNER JOIN dbo.tblQuestions q ON q.QuestionID = r.QuestionID INNER JOIN dbo.tblSections sec ON sec.SectionID = q.SectionID INNER JOIN dbo.tblQuestionLabels ql ON ql.SectionID = q.SectionID AND ql.QuestionOrder = q.QuestionOrder INNER JOIN dbo.tblTEMain1 te ON te.TEFileNo = sc.TEFileNo INNER JOIN dbo.tblCorpClient cl ON cl.CorpCode = te.CorpCode INNER JOIN dbo.tblInitials sp ON sp.Initials = te.PrimaryContact INNER JOIN HROServiceEval.dbo.vwTESurveyDetailReport sup ON sup.TEFileNo = sc.TEFileNo AND sup.GenericVendorID = r.GenericVendorID WHERE r.GenericVendorID <> 0 AND ( q.ResponseType = 'Range' OR q.SectionID IN (3, 4, 5) ) AND r.CreatedOn >= @StartDate AND r.CreatedOn < DATEADD(day, 1, @EndDate) AND (@CorpCode = '' OR te.CorpCode = @CorpCode) AND (@SrvcProvider = '' OR te.PrimaryContact = @SrvcProvider) AND (@ProfitCenter = '' OR sp.ProfitCenter = @ProfitCenter) AND ( @CompanyName IN ('', '(All Suppliers)') OR sup.CompanyName = @CompanyName ) AND (@SupplierType = '' OR sup.Service = @SupplierType) ), Buckets AS ( SELECT SectionID, SectionOrder, SectionDescription, QuestionLabel, QuestionID, QuestionOrder, SubQuestionOrder, QuestionText, ResponseType, CompanyName, SUM(StronglyDisagree) AS StronglyDisagree, SUM(Disagree) AS Disagree, SUM(SlightlyDisagree) AS SlightlyDisagree, SUM(SlightlyAgree) AS SlightlyAgree, SUM(Agree) AS Agree, SUM(StronglyAgree) AS StronglyAgree, SUM(ResponseCount) AS ResponseCount FROM Responses GROUP BY SectionID, SectionOrder, SectionDescription, QuestionLabel, QuestionID, QuestionOrder, SubQuestionOrder, QuestionText, ResponseType, CompanyName ) SELECT SectionID, SectionOrder, SectionDescription, QuestionLabel, QuestionID, QuestionOrder, SubQuestionOrder, QuestionText, ResponseType, CompanyName, StronglyDisagree, Disagree, SlightlyDisagree, SlightlyAgree, Agree, StronglyAgree, ResponseCount, CAST(100.0 * StronglyAgree / NULLIF(ResponseCount, 0) AS decimal(9, 2)) AS TopBox, CAST(100.0 * (StronglyAgree + Agree) / NULLIF(ResponseCount, 0) AS decimal(9, 2)) AS TopBox2, CAST(100.0 * (StronglyAgree + SlightlyAgree + Agree) / NULLIF(ResponseCount, 0) AS decimal(9, 2)) AS TopBox3 FROM Buckets ORDER BY SectionID, SectionOrder, SectionDescription, QuestionLabel, QuestionText, CompanyName;