/* Monthly Auth Count — TE grain (ReloAction) BackOffice: frmMnthlySrvcAuthCountRpts / MonthlyAuthCountReport.rpt ReportManager: MonthlyAuthCount → tblTERptTempFile Grain: one TE. AuthDate = MIN(service AuthDate). CancelDate = MAX(CancelDate) only when every service has a CancelDate; else NULL. UserType: 1=Internal, 3=External, 0=All. MoveType: 'D','I','NO' (null DomesticIntl), '' = Both (no DomesticIntl filter). */ DECLARE @CorpCode varchar(4) = NULL; -- NULL = all DECLARE @ParentRollup bit = 0; DECLARE @MoveType varchar(2) = ''; -- 'D','I','NO','' DECLARE @UserType smallint = 0; -- 1 / 3 / 0 DECLARE @ProfitCenter varchar(2) = NULL; DECLARE @CAC varchar(2) = NULL; DECLARE @SrvcProvider varchar(4) = NULL; DECLARE @RevenueShareOnly bit = 0; SELECT a.CorpCode, a.TEFileNo, MIN(a.AuthDate) AS AuthDate, CASE WHEN COUNT(a.CancelDate) < COUNT(*) THEN NULL ELSE MAX(a.CancelDate) END AS CancelDate, COUNT(*) AS ServiceCount, COUNT(a.CancelDate) AS CancelledServiceCount FROM dbo.vwTEAllSrvcs a WHERE ( NULLIF(@CorpCode, '') IS NULL OR (@ParentRollup = 1 AND a.ParentCompany = @CorpCode) OR (@ParentRollup = 0 AND a.CorpCode = @CorpCode) ) AND ( @MoveType = '' OR (@MoveType = 'D' AND a.DomesticIntl = 'D') OR (@MoveType = 'I' AND a.DomesticIntl = 'I') OR (@MoveType = 'NO' AND a.DomesticIntl IS NULL) ) AND (@UserType = 0 OR a.UserType = @UserType) AND (NULLIF(@ProfitCenter, '') IS NULL OR a.ProfitCenter = @ProfitCenter) AND (NULLIF(@CAC, '') IS NULL OR a.CAC = @CAC) AND (NULLIF(@SrvcProvider, '') IS NULL OR a.SrvcProvider = @SrvcProvider) AND (@RevenueShareOnly = 0 OR a.IncludeInRevenueShare = 1) GROUP BY a.CorpCode, a.TEFileNo ORDER BY a.CorpCode, a.TEFileNo;