/* Monthly Service Count — detail (DatawarehouseNCM) BackOffice: frmMnthlySrvcAuthCountRpts / MonthlyServiceCountReport.rpt Fact: FactMonthlyAuthorization Service grain filtered by AuthorizationDate in range (BO MonthlyServiceCount). */ 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; -- 1 / 2 / 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, cc.FormalName, m.ParentCompany, m.TEFileNo, m.TEName, f.ServiceCode, f.ServiceType, ds.ServiceDesc, f.AuthorizationDate AS AuthDate, f.CancelDate, f.DepartureOwnRent, CASE WHEN f.DomesticIntl = 1 THEN 'D' WHEN f.DomesticIntl = 2 THEN 'I' ELSE NULL END AS DomesticIntl, f.UserType, f.ProfitCenter, f.CAC, f.SrvcProvider, f.IncludeInRevenueShare, f.ServiceFee, f.Weight, f.WeightINTL, d.DivisionName, p.ReloPolicy, m.Descriptor1, m.Descriptor2, m.Descriptor3, m.Descriptor4, m.Descriptor5 FROM dbo.FactMonthlyAuthorization f INNER JOIN dbo.DimMain m ON f.DimMainID = m.DimMainID LEFT JOIN dbo.DimCorpClient cc ON m.CorpCode = cc.CorpCode LEFT JOIN dbo.DimDivision d ON f.DimDivisionID = d.DimDivisionID LEFT JOIN dbo.DimPolicy p ON f.DimPolicyID = p.DimPolicyID 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) ORDER BY m.CorpCode, f.AuthorizationDate, m.TEFileNo, f.ServiceCode;