/* Monthly Auth Count — month summary (DatawarehouseNCM) BackOffice: frmMnthlySrvcAuthCountRpts / MonthlyAuthCountReport.rpt Fact: FactMonthlyAuthorization TE counts by Auth month / DomesticIntl / DepartureOwnRent (Pm-ShowTE off style). Active = CancelDate IS NULL after TE aggregation. Canceled = CancelDate IS NOT NULL. */ DECLARE @CorpCode varchar(4) = NULL; DECLARE @ParentRollup bit = 0; DECLARE @StartDate date = DATEADD(month, -11, DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1)); DECLARE @EndDate date = EOMONTH(GETDATE()); DECLARE @DomesticIntl smallint = NULL; DECLARE @UserType smallint = 0; -- 1 internal, 3 external, 0 all DECLARE @ProfitCenter varchar(2) = NULL; DECLARE @CAC varchar(2) = NULL; DECLARE @SrvcProvider varchar(4) = NULL; DECLARE @RevenueShareOnly bit = 0; ;WITH TEAuth AS ( SELECT m.CorpCode, m.TEFileNo, MIN(f.AuthorizationDate) AS AuthDate, CASE WHEN COUNT(f.CancelDate) < COUNT(*) THEN NULL ELSE MAX(f.CancelDate) END AS CancelDate, CASE WHEN COALESCE(MAX(f.DomesticIntl), 1) = 1 THEN 'Domestic' ELSE 'International' END AS DomesticIntlLabel, CASE WHEN COALESCE(MAX(f.DomesticIntl), 1) = 1 THEN CASE WHEN COALESCE(MAX(f.DepartureOwnRent), '') = '' THEN 'UnAssigned' ELSE MAX(f.DepartureOwnRent) END ELSE COALESCE(MAX(mt.MoveTypeDescription), 'UnAssigned') END AS DepartureOwnRentLabel FROM dbo.FactMonthlyAuthorization f INNER JOIN dbo.DimMain m ON f.DimMainID = m.DimMainID LEFT JOIN dbo.DimMoveType mt ON f.DimMoveTypeID = mt.DimMoveTypeID WHERE ( NULLIF(@CorpCode, '') IS NULL OR (@ParentRollup = 1 AND m.ParentCompany = @CorpCode) OR (@ParentRollup = 0 AND m.CorpCode = @CorpCode) ) AND (@DomesticIntl IS NULL OR f.DomesticIntl = @DomesticIntl) AND (@UserType = 0 OR f.UserType = @UserType) AND (NULLIF(@ProfitCenter, '') IS NULL OR f.ProfitCenter = @ProfitCenter) AND (NULLIF(@CAC, '') IS NULL OR f.CAC = @CAC) AND (NULLIF(@SrvcProvider, '') IS NULL OR f.SrvcProvider = @SrvcProvider) AND (@RevenueShareOnly = 0 OR ISNULL(f.IncludeInRevenueShare, 0) = 1) GROUP BY m.CorpCode, m.TEFileNo ) SELECT CorpCode, DomesticIntlLabel, DepartureOwnRentLabel, FORMAT(AuthDate, 'MMM-yy') AS AuthMonthYear, DATEFROMPARTS(YEAR(AuthDate), MONTH(AuthDate), 1) AS AuthMonthStart, SUM(CASE WHEN CancelDate IS NULL THEN 1 ELSE 0 END) AS TEAuthCount, SUM(CASE WHEN CancelDate IS NULL THEN 0 ELSE 1 END) AS TECancelCount FROM TEAuth WHERE CAST(AuthDate AS date) BETWEEN @StartDate AND @EndDate GROUP BY CorpCode, DomesticIntlLabel, DepartureOwnRentLabel, FORMAT(AuthDate, 'MMM-yy'), DATEFROMPARTS(YEAR(AuthDate), MONTH(AuthDate), 1) ORDER BY CorpCode, DomesticIntlLabel, DepartureOwnRentLabel, AuthMonthStart;