/* Monthly Service Count — detail (ReloAction) BackOffice: frmMnthlySrvcAuthCountRpts / MonthlyServiceCountReport.rpt ReportManager: MonthlyServiceCount → tblTmpTEAllSrvs Grain: one authorized service with AuthDate in [@StartDate, @EndDate]. When CorpCode is blank, BO excludes ACME and GLC2. When MoveType is Both (''), BO requires DomesticIntl IS NOT NULL. UserType: 1=Internal, 3=External, 0=All. */ DECLARE @StartDate date = DATEADD(month, -11, DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1)); DECLARE @EndDate date = EOMONTH(GETDATE()); DECLARE @CorpCode varchar(4) = NULL; -- NULL = all (excludes ACME/GLC2) DECLARE @ParentRollup bit = 0; DECLARE @MoveType varchar(2) = ''; -- 'D','I','NO','' (Both => DomesticIntl NOT NULL) DECLARE @UserType smallint = 0; DECLARE @ProfitCenter varchar(2) = NULL; DECLARE @CAC varchar(2) = NULL; DECLARE @SrvcProvider varchar(4) = NULL; SELECT a.CorpCode, a.FormalName, a.ParentCompany, a.TEFileNo, a.TELastName, a.TEFirstName, a.ServiceCode, a.ServiceType, a.Description AS ServiceDescription, a.AuthDate, a.CancelDate, a.CompleteDate, a.ReloPolicy, a.DivisionName, a.ProfitCenter, a.CAC, a.SrvcProvider, a.DomesticIntl, a.DepartureOwnRent, a.UserType, a.IncludeInRevenueShare, a.Weight, a.WeightINTL FROM dbo.vwTEAllSrvcs a WHERE a.AuthDate >= @StartDate AND a.AuthDate <= @EndDate AND ( (NULLIF(@CorpCode, '') IS NULL AND a.CorpCode NOT IN ('ACME', 'GLC2')) OR (@ParentRollup = 1 AND a.ParentCompany = @CorpCode) OR (@ParentRollup = 0 AND NULLIF(@CorpCode, '') IS NOT NULL AND a.CorpCode = @CorpCode) ) AND ( (@MoveType = '' AND a.DomesticIntl IS NOT NULL) OR (@MoveType = 'D' AND a.DomesticIntl = 'D') OR (@MoveType = 'I' AND a.DomesticIntl = 'I') OR (@MoveType = 'NO' AND a.DomesticIntl IS NULL) ) AND (@UserType = 0 OR a.UserType = @UserType) AND (NULLIF(@ProfitCenter, '') IS NULL OR a.ProfitCenter = @ProfitCenter) AND (NULLIF(@CAC, '') IS NULL OR a.CAC = @CAC) AND (NULLIF(@SrvcProvider, '') IS NULL OR a.SrvcProvider = @SrvcProvider) ORDER BY a.CorpCode, a.AuthDate, a.TEFileNo, a.ServiceCode;