/* Monthly Service Count — month / service summary (DatawarehouseNCM) BackOffice: frmMnthlySrvcAuthCountRpts / MonthlyServiceCountReport.rpt Fact: FactMonthlyAuthorization Pm-SrvcRecap style: counts by Auth month and ServiceCode. */ 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; SELECT m.CorpCode, CASE WHEN f.DomesticIntl = 1 THEN 'Domestic' WHEN f.DomesticIntl = 2 THEN 'International' ELSE 'UnAssigned' END AS DomesticIntlLabel, COALESCE(NULLIF(f.DepartureOwnRent, ''), 'UnAssigned') AS DepartureOwnRentLabel, f.ServiceCode, ds.ServiceDesc, FORMAT(f.AuthorizationDate, 'MMM-yy') AS AuthMonthYear, DATEFROMPARTS(YEAR(f.AuthorizationDate), MONTH(f.AuthorizationDate), 1) AS AuthMonthStart, COUNT(*) AS ServiceCount, SUM(CASE WHEN f.CancelDate IS NULL THEN 1 ELSE 0 END) AS ActiveServiceCount, SUM(CASE WHEN f.CancelDate IS NULL THEN 0 ELSE 1 END) AS CancelledServiceCount FROM dbo.FactMonthlyAuthorization f INNER JOIN dbo.DimMain m ON f.DimMainID = m.DimMainID LEFT JOIN dbo.DimServices ds ON ds.CorpCode = m.CorpCode AND ds.ServiceCode = f.ServiceCode WHERE CAST(f.AuthorizationDate AS date) BETWEEN @StartDate AND @EndDate AND ( 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) GROUP BY m.CorpCode, CASE WHEN f.DomesticIntl = 1 THEN 'Domestic' WHEN f.DomesticIntl = 2 THEN 'International' ELSE 'UnAssigned' END, COALESCE(NULLIF(f.DepartureOwnRent, ''), 'UnAssigned'), f.ServiceCode, ds.ServiceDesc, FORMAT(f.AuthorizationDate, 'MMM-yy'), DATEFROMPARTS(YEAR(f.AuthorizationDate), MONTH(f.AuthorizationDate), 1) ORDER BY m.CorpCode, AuthMonthStart, f.ServiceCode;