/* Monthly New Authorization Count — with TE detail (DatawarehouseNCM) BackOffice: frmMnthlySrvcAuthCountRpts / MonthlyAuthCountReport.rpt Setup: Report Type = Authorization Count, Show TE Detail, Sort = None, Move Type = Both. Profit center is omitted. First result is one row per transferee. Second result is the month counts. Fact: FactMonthlyAuthorization. CancelDate is set only when every service on the file is cancelled. */ 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 @UserType smallint = 1; -- 1 internal, 3 external, 0 all DECLARE @DomesticIntl smallint = NULL; DECLARE @SrvcProvider varchar(4) = NULL; DECLARE @RevenueShareOnly bit = 0; DECLARE @DivisionName varchar(30) = NULL; DECLARE @PolicyGroup varchar(50) = NULL; IF OBJECT_ID('tempdb..#TEAuth') IS NOT NULL DROP TABLE #TEAuth; ;WITH Svc AS ( SELECT m.CorpCode, m.ParentCompany, cc.FormalName, m.TEFileNo, m.TEName, MIN(f.AuthorizationDate) AS AuthDate, CASE WHEN COUNT(f.CancelDate) < COUNT(*) THEN NULL ELSE MAX(f.CancelDate) END AS CancelDate, COALESCE(MAX(f.DomesticIntl), 1) AS DomesticIntl, MAX(f.DepartureOwnRent) AS DepartureOwnRent, MAX(mt.MoveTypeDescription) AS MoveTypeDescription, MAX(d.DivisionName) AS DivisionName, MAX(COALESCE(NULLIF(pm.MasterPolicy, ''), NULLIF(p.ReloPolicy, ''), 'Undefined')) AS PolicyGroup 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.DimPolicyMaster pm ON p.DimPolicyMasterID = pm.DimPolicyMasterID 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(@SrvcProvider, '') IS NULL OR f.SrvcProvider = @SrvcProvider) AND (@RevenueShareOnly = 0 OR ISNULL(f.IncludeInRevenueShare, 0) = 1) GROUP BY m.CorpCode, m.ParentCompany, cc.FormalName, m.TEFileNo, m.TEName ) SELECT CorpCode, ParentCompany, FormalName, TEFileNo, TEName, AuthDate, CancelDate, YEAR(AuthDate) AS AuthYear, DivisionName, PolicyGroup, CASE WHEN DomesticIntl = 1 THEN 'Domestic' ELSE 'International' END AS MoveGroup, CASE WHEN ISNULL(DepartureOwnRent, '') = '' THEN 'Unassigned' ELSE DepartureOwnRent END AS OwnRent, CASE WHEN DomesticIntl = 1 THEN CASE WHEN ISNULL(DepartureOwnRent, '') = '' THEN 'Unassigned' ELSE DepartureOwnRent END ELSE COALESCE(NULLIF(MoveTypeDescription, ''), 'Unassigned') END AS SubGroup INTO #TEAuth FROM Svc WHERE CAST(AuthDate AS date) BETWEEN @StartDate AND @EndDate AND (NULLIF(@DivisionName, '') IS NULL OR DivisionName = @DivisionName) AND (NULLIF(@PolicyGroup, '') IS NULL OR PolicyGroup = @PolicyGroup); SELECT PolicyGroup, DivisionName, FormalName, CorpCode, MoveGroup, SubGroup, OwnRent, TEFileNo, TEName, AuthDate, FORMAT(AuthDate, 'MMM-yy') AS AuthMonth, CASE WHEN CancelDate IS NULL THEN 0 ELSE 1 END AS Canceled FROM #TEAuth ORDER BY MoveGroup, SubGroup, TEName, TEFileNo; SELECT MoveGroup, SubGroup, AuthYear, SUM(CASE WHEN MONTH(AuthDate) = 1 AND CancelDate IS NULL THEN 1 ELSE 0 END) AS [Jan], SUM(CASE WHEN MONTH(AuthDate) = 2 AND CancelDate IS NULL THEN 1 ELSE 0 END) AS [Feb], SUM(CASE WHEN MONTH(AuthDate) = 3 AND CancelDate IS NULL THEN 1 ELSE 0 END) AS [Mar], SUM(CASE WHEN MONTH(AuthDate) = 4 AND CancelDate IS NULL THEN 1 ELSE 0 END) AS [Apr], SUM(CASE WHEN MONTH(AuthDate) = 5 AND CancelDate IS NULL THEN 1 ELSE 0 END) AS [May], SUM(CASE WHEN MONTH(AuthDate) = 6 AND CancelDate IS NULL THEN 1 ELSE 0 END) AS [Jun], SUM(CASE WHEN MONTH(AuthDate) = 7 AND CancelDate IS NULL THEN 1 ELSE 0 END) AS [Jul], SUM(CASE WHEN MONTH(AuthDate) = 8 AND CancelDate IS NULL THEN 1 ELSE 0 END) AS [Aug], SUM(CASE WHEN MONTH(AuthDate) = 9 AND CancelDate IS NULL THEN 1 ELSE 0 END) AS [Sep], SUM(CASE WHEN MONTH(AuthDate) = 10 AND CancelDate IS NULL THEN 1 ELSE 0 END) AS [Oct], SUM(CASE WHEN MONTH(AuthDate) = 11 AND CancelDate IS NULL THEN 1 ELSE 0 END) AS [Nov], SUM(CASE WHEN MONTH(AuthDate) = 12 AND CancelDate IS NULL THEN 1 ELSE 0 END) AS [Dec], SUM(CASE WHEN CancelDate IS NULL THEN 1 ELSE 0 END) AS Total, SUM(CASE WHEN CancelDate IS NULL THEN 0 ELSE 1 END) AS Canceled FROM #TEAuth GROUP BY MoveGroup, SubGroup, AuthYear ORDER BY MoveGroup, SubGroup, AuthYear; SELECT MoveGroup, SUM(CASE WHEN OwnRent = 'Homeowner' AND CancelDate IS NULL THEN 1 ELSE 0 END) AS Homeowner, SUM(CASE WHEN OwnRent = 'Renter' AND CancelDate IS NULL THEN 1 ELSE 0 END) AS Renter, SUM(CASE WHEN OwnRent = 'Unassigned' AND CancelDate IS NULL THEN 1 ELSE 0 END) AS Unassigned, SUM(CASE WHEN CancelDate IS NULL THEN 1 ELSE 0 END) AS Total FROM #TEAuth GROUP BY MoveGroup ORDER BY MoveGroup;