/* Survey by Supplier — Summary by Supplier / with transferee detail (transactional / ReloAction) BackOffice: frmROLSurveySupplier2007 (cboRptFormat index 1 and 2) Crystal: NewSupplierSurveyDetailReport.rpt Pm-TEDetail 0 = supplier summary, 1 = show the Details subreport DatawarehouseNCM has no survey-response fact. Client flags ROLSurveyQuestion / TESurveyDomestic / TESurveyInternational are setup indicators only, so this query runs on ReloAction. Same joins and record selection as NewSupplierSurveyReport.rpt. This file stays at one row per survey response (the detail grain). The Details subreport is this same set limited to QuestionID in (36, 37, 39, 40, 41, 42, 43, 44); add that predicate to reproduce it. 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. vwTESurveyDetailReport can return more than one row for the same transferee and GenericVendorID (HHG and AUTO, for example). Crystal joins only on TEFileNo + GenericVendorID, so those responses repeat here too. */ 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 ) SELECT sup.CompanyName, sup.Service AS SupplierTypeCode, sup.VendorType, sup.LocationName, sup.ShortName AS SupplierShortName, r.GenericVendorID, sec.SectionID, sec.SectionOrder, sec.Description AS SectionDescription, ql.QuestionLabel, q.QuestionID, q.QuestionOrder, q.SubQuestionOrder, q.QuestionText, q.ResponseType, sc.TESurveyID, te.TEFileNo, te.TELastName, te.TEFirstName, CASE WHEN te.ExecPgm = 1 THEN '(VIP)' END AS VIP, te.CorpCode, cl.FormalName, cl.ParentCompany, te.PrimaryContact AS SrvcProvider, LTRIM(RTRIM(ISNULL(sp.FirstName, '') + ' ' + ISNULL(sp.LastName, ''))) AS ServiceProviderName, sp.ProfitCenter, mt.DomesticIntl, mt.moveTypeDescription, r.CreatedOn AS ResponseDate, r.Response, CASE WHEN q.ResponseType = 'Range' AND r.Response = 1 THEN 'Strongly Disagree' WHEN q.ResponseType = 'Range' AND r.Response = 2 THEN 'Disagree' WHEN q.ResponseType = 'Range' AND r.Response = 3 THEN 'Slightly Disagree' WHEN q.ResponseType = 'Range' AND r.Response = 4 THEN 'Slightly Agree' WHEN q.ResponseType = 'Range' AND r.Response = 5 THEN 'Agree' WHEN q.ResponseType = 'Range' AND r.Response = 6 THEN 'Strongly Agree' WHEN q.ResponseType = 'Range' AND r.Response = 7 THEN 'NA' WHEN q.ResponseType = 'YesNo' AND r.Response = 1 THEN 'Yes' WHEN q.ResponseType = 'YesNo' AND r.Response = 2 THEN 'No' END AS ResponseLabel, CASE WHEN q.ResponseType = 'YesNo' THEN '**' ELSE ' ' END AS NoteYesNoAnswer, CAST(r.ResponseComments AS varchar(max)) AS ResponseComments 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 LEFT JOIN dbo.tblMoveTypes mt ON mt.MoveType = te.MoveType 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) ORDER BY sup.CompanyName, sec.SectionOrder, sec.Description, ql.QuestionLabel, q.QuestionOrder, q.SubQuestionOrder, q.QuestionText, te.TEFileNo;