/* Monthly Auth Count — TE detail (DatawarehouseNCM) BackOffice: frmMnthlySrvcAuthCountRpts / MonthlyAuthCountReport.rpt Fact: FactMonthlyAuthorization Mirrors ReportManager MonthlyAuthCount + date filter on first AuthDate. CancelDate NULL unless every service for the TE is cancelled. DomesticIntl on fact: 1=Domestic, 2=International. */ 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=all (fact mapping; null OLTP loaded as 1) 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.ParentCompany, m.TEFileNo, m.TEName, m.Descriptor1, m.Descriptor2, m.Descriptor3, m.Descriptor4, m.Descriptor5, m.DepartureOwnRent AS MainDepartureOwnRent, MIN(f.AuthorizationDate) AS AuthDate, CASE WHEN COUNT(f.CancelDate) < COUNT(*) THEN NULL ELSE MAX(f.CancelDate) END AS CancelDate, MAX(f.DepartureOwnRent) AS DepartureOwnRent, MAX(f.DomesticIntl) AS DomesticIntl, MAX(f.UserType) AS UserType, MAX(d.DivisionName) AS DivisionName, MAX(p.ReloPolicy) AS ReloPolicy, MAX(cc.FormalName) AS FormalName 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 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.ParentCompany, m.TEFileNo, m.TEName, m.Descriptor1, m.Descriptor2, m.Descriptor3, m.Descriptor4, m.Descriptor5, m.DepartureOwnRent ) SELECT t.*, FORMAT(t.AuthDate, 'MMM-yy') AS AuthMonthYear, CASE WHEN t.CancelDate IS NULL THEN 1 ELSE 0 END AS IsActiveAuth, CASE WHEN t.CancelDate IS NULL THEN 0 ELSE 1 END AS IsCancelledAuth FROM TEAuth t WHERE CAST(t.AuthDate AS date) BETWEEN @StartDate AND @EndDate ORDER BY t.CorpCode, t.AuthDate, t.TEFileNo;