POC tracker Logic Check

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
--DECLARE @Date datetime
--SET @Date = '20230930'



if object_id('tempdb.dbo.#erctierqtr', 'U') is not null
drop table #erctierqtr;



if object_id('tempdb.dbo.#Aaron', 'U') is not null
drop table #Aaron;

     select
            n.[fileID]
     ,      f.[fileAccCode]
     ,      m.[Client]
     ,      n.ID
     ,      n.[status]
     ,      n.[TaxYear]
     ,      n.FEDState
     --,      n.[FiscalQuarter] [Quarter]
     --,      cast(left(n.[ERCQualifiedType],1) as int) [Tier]
     ,      n.[sentTo3E]
     ,      n.[FeeCapType]
     ,      n.GrossValue
     ,      n.Value
     ,  n.Type
     ,      row_number() over(partition by m.[Client], n.[TaxYear],n.Type/*, n.[FiscalQuarter]*/ order by n.[sentTo3E] desc) [Rn]
     into #erctierqtr
     from
           [MS_LIVE_Clone].[dbo].[udFiNumbers] n
     join
           [MS_LIVE_Clone].[config].[dbfile] f on f.[fileID] = n.[fileID]
     join
           [TE_3E_PROD_Clone].[dbo].[Matter] m on m.[MattIndex] = f.[fileAccCode]
     where
            --n.[ERCQualifiedType] is not null
            f.fileDepartment = 'R&D/SALT'
            and n.[Active] = 1
            and n.sentTo3E is not null
            --and n.[status] = 'S'
            and cast(n.[sentTo3E] as date) <= @Date


    SELECT *
    ,CASE
            WHEN    HasFedandState = 1 AND
                    (MAX(CASE WHEN FEDState = 'FED' THEN Total END)OVER(Partition BY Number, year) IS NOT NULL AND MAX(CASE WHEN FEDState <> 'FED' THEN Total END)OVER(Partition BY Number, year) IS NOT NULL/* OR
                    MAX(CASE WHEN FEDState <> 'FED' THEN FinalCredit END)OVER(Partition BY Number, year) IS NOT NULL AND MAX(CASE WHEN FEDState = 'FED' THEN FinalCredit END)OVER(Partition BY Number, year) IS NULL*/) THEN 1
                    ELSE 0 END HasFedStateSplit
        into #Aaron
        FROM
        (

    select
    m.Number
    , c.DisplayName
    , pc.TriggerType
    , pc.year
--  , pc.quarter
    ,CASE WHEN IsFederal = 1 THEN 'FED' ELSE pc.State END FEDState
    ,CASE WHEN (MAX(IsFederal)OVER(Partition BY m.Number, pc.year) = 1 AND MAX(IsFederal)OVER(Partition BY m.Number, pc.year) = 0 )
        THEN 1 ELSE 0 END HasFedandState
    , pc.ControlGroup
    , ph.FeeCapPct
    , pc.OrigCredit
    , pc.FinalCredit
    , pc.ValueAssessment
    , pc.Adjustments
    , pc.Total
    , m.MattStatus
    , pc.TimeStamp
--  , eq.Tier
    ,m.NxStartDate
    , m.NxEndDate
    ,m.MattIndex
    ,MAX(COALESCE(NULLIF(NULLIF(CASE WHEN eq.type = 'FN' THEN sentTo3E END,'19990131'),'19990101'),FNDate,ph.EffStart))sentTo3EFN
    ,MAX(COALESCE(NULLIF(NULLIF(CASE WHEN eq.type = 'VA' THEN sentTo3E END,'19990131'),'19990101'),pc.VADAte,ph.EffStart))sentTo3EVA
    ,SUM(pc.[RevenuePct]) RevenuePct

    from
    (
    SELECT m.MattIndex,m.Number,m.client, m.Description,m.MattStatus, m.EngSignedDate_CCC,DATEADD(d,0,DATEDIFF(d,0,TimeStamp)) NxStartDate,'99991231' NxEndDate
    FROM
        [TE_3E_PROD_Clone].[dbo].matter m
    UNION ALL
    SELECT m.MattIndex,m.Number,m.client, m.Description,m.MattStatus, m.EngSignedDate_CCC,DATEADD(d,0,DATEDIFF(d,0,TimeStamp)) NxStartDate,DATEADD(d,-1,DATEDIFF(d,0,AuditTimeStamp)) NxEndDate
    FROM
        [TE_3E_PROD_Clone].[dbo].matter_audit m
    ) m
    join [TE_3E_PROD_Clone].[dbo].PGDetHdr_CCC ph on ph.MatterLkUp = m.MattIndex
    left join [TE_3E_PROD_Clone].[dbo].PGDetChild_CCC pc on pc.PGDetHdr = ph.PGDetHdr_CCCID
    left join [TE_3E_PROD_Clone].[dbo].Client c on c.ClientIndex = m.Client
    join [TE_3E_PROD_Clone].[dbo].MattDate MD ON MD.MatterLkUp = m.MattIndex
    LEFT JOIN
        #erctierqtr eq on eq.Client = m.Client and eq.taxYear = pc.Year and ((eq.FEDState = 'FED' AND pc.IsFederal = 1) OR eq.FEDState = pc.State COLLATE SQL_Latin1_General_CP1_CI_AS)  /*and eq.Quarter = pc.Quarter COLLATE SQL_Latin1_General_CP1_CI_AS*/  and rn=1
    where
    @Date Between m.NxStartDate and m.NxEndDate AND
    @Date Between md.NxStartDate and md.NxEndDate AND
    @Date Between ph.NxStartDate and ph.NxEndDate AND
    md.PracticeGroup in ('110','120') AND
    md.Arrangement IN ('CONT','FFFINAL') AND
    m.MattStatus not in ('Cancel_NOEL', 'CANCELDUP','NO_EL','KO')
    GROUP BY
    m.Number
    , c.DisplayName
    , pc.TriggerType
    , pc.year
    ,CASE WHEN IsFederal = 1 THEN 'FED' ELSE pc.State END
--  , pc.quarter
    , pc.ControlGroup
    , ph.FeeCapPct
    , pc.OrigCredit
    , pc.FinalCredit
    , pc.ValueAssessment
    , pc.Adjustments
    , pc.Total
    , m.MattStatus
    , pc.TimeStamp
--  , eq.Tier
    ,m.NxStartDate
    , m.NxEndDate
    ,m.MattIndex
    ,IsFederal
    ) a



    if object_id('tempdb.dbo.#Aaron2', 'U') is not null



drop table #Aaron2;

DECLARE @Date2 datetime
SET @Date2 = DATEAdd(d,-day(@Date),@Date)

    SELECT *
    ,CASE
            WHEN    HasFedandState = 1 AND
                    (MAX(CASE WHEN FEDState = 'FED' THEN FinalCredit END)OVER(Partition BY Number, year) IS NOT NULL AND MAX(CASE WHEN FEDState <> 'FED' THEN FinalCredit END)OVER(Partition BY Number, year) IS NOT NULL /*OR
                    MAX(CASE WHEN FEDState <> 'FED' THEN FinalCredit END)OVER(Partition BY Number, year) IS NOT NULL AND MAX(CASE WHEN FEDState = 'FED' THEN FinalCredit END)OVER(Partition BY Number, year) IS NULL*/) THEN 1
                    ELSE 0 END HasFedStateSplit
        into #Aaron2
        FROM
        (

    select
    m.Number
    , c.DisplayName
    , pc.TriggerType
    , pc.year
--  , pc.quarter
    ,CASE WHEN IsFederal = 1 THEN 'FED' ELSE pc.State END FEDState
    ,CASE WHEN (MAX(IsFederal)OVER(Partition BY m.Number, pc.year) = 1 AND MAX(IsFederal)OVER(Partition BY m.Number, pc.year) = 0 )
        THEN 1 ELSE 0 END HasFedandState
    , pc.ControlGroup
    , ph.FeeCapPct
    , pc.OrigCredit
    , pc.FinalCredit
    , pc.ValueAssessment
    , pc.Adjustments
    , pc.Total
    , m.MattStatus
    , pc.TimeStamp
--  , eq.Tier
    ,m.NxStartDate
    , m.NxEndDate
    ,m.MattIndex
    ,MAX(COALESCE(NULLIF(NULLIF(CASE WHEN eq.type = 'FN' THEN sentTo3E END,'19990131'),'19990101'),FNDate,ph.EffStart))sentTo3EFN
    ,MAX(COALESCE(NULLIF(NULLIF(CASE WHEN eq.type = 'VA' THEN sentTo3E END,'19990131'),'19990101'),pc.VADAte,ph.EffStart))sentTo3EVA
    ,SUM(pc.[RevenuePct]) RevenuePct
    ,ms.Description Status
    from
    (
    SELECT m.MattIndex,m.Number,m.client, m.Description,m.MattStatus, m.EngSignedDate_CCC,DATEADD(d,0,DATEDIFF(d,0,TimeStamp)) NxStartDate,'99991231' NxEndDate
    FROM
        [TE_3E_PROD_Clone].[dbo].matter m
    UNION ALL
    SELECT m.MattIndex,m.Number,m.client, m.Description,m.MattStatus, m.EngSignedDate_CCC,DATEADD(d,0,DATEDIFF(d,0,TimeStamp)) NxStartDate,DATEADD(d,-1,DATEDIFF(d,0,AuditTimeStamp)) NxEndDate
    FROM
        [TE_3E_PROD_Clone].[dbo].matter_audit m
    ) m
    JOIN
    TE_3E_PROD_Clone..MattStatus ms on ms.code = m.MattStatus
    join [TE_3E_PROD_Clone].[dbo].PGDetHdr_CCC ph on ph.MatterLkUp = m.MattIndex
    left join [TE_3E_PROD_Clone].[dbo].PGDetChild_CCC pc on pc.PGDetHdr = ph.PGDetHdr_CCCID
    left join [TE_3E_PROD_Clone].[dbo].Client c on c.ClientIndex = m.Client
    join [TE_3E_PROD_Clone].[dbo].MattDate MD ON MD.MatterLkUp = m.MattIndex
    LEFT JOIN
        #erctierqtr eq on eq.Client = m.Client and eq.taxYear = pc.Year and ((eq.FEDState = 'FED' AND pc.IsFederal = 1) OR eq.FEDState = pc.State COLLATE SQL_Latin1_General_CP1_CI_AS)  /*and eq.Quarter = pc.Quarter COLLATE SQL_Latin1_General_CP1_CI_AS*/  and rn=1
    where
    @Date2 Between m.NxStartDate and m.NxEndDate AND
    @Date2 Between md.NxStartDate and md.NxEndDate AND
    @Date2 Between ph.NxStartDate and ph.NxEndDate AND
    md.PracticeGroup in ('110','120') AND
    md.Arrangement IN ('CONT','FFFINAL') AND
    m.MattStatus not in ('Cancel_NOEL', 'CANCELDUP','NO_EL','KO')
    GROUP BY
    m.Number
    , c.DisplayName
    , pc.TriggerType
    , pc.year
    ,CASE WHEN IsFederal = 1 THEN 'FED' ELSE pc.State END
--  , pc.quarter
    , pc.ControlGroup
    , ph.FeeCapPct
    , pc.OrigCredit
    , pc.FinalCredit
    , pc.ValueAssessment
    , pc.Adjustments
    , pc.Total
    , m.MattStatus
    , pc.TimeStamp
--  , eq.Tier
    ,m.NxStartDate
    , m.NxEndDate
    ,m.MattIndex
    ,IsFederal
    ,ms.Description
    ) a




if object_id('tempdb.dbo.#T', 'U') is not null
drop table #T;
Select
ROW_NUMBER()OVER(PARTITION BY f.clid ORDER BY f.filetype asc) rn,f.clid mmclid,mm.*
INTO #T
from
MS_LIVE_Clone.config.dbfile f
JOIN
(
    SELECT
         clid 'KO_SD_Clid'
         ,fileID
        ,Max(CAST(CASE WHEN KDTID in (26,54) and KeyDateIntervalActionGUID IN  ('C6BAB9B2-921D-428E-88C7-D709B2671954','8E852CC2-E94F-4BCE-92BD-7F4451BE7B3B') and NULLIF(wb.Completed,'01/01/1999') IS NOT NULL THEN NULLIF(wb.Completed,'01/01/1999') END AS DATE))  [KOCompleted]
        ,Max(CAST(CASE WHEN KDTID in (26,54) and  KeyDateIntervalActionGUID IN ('54795381-1386-481D-904D-FD351B64D503','FA36CE1A-3321-4299-BEF1-8214CFFA2E8A') AND MilestoneCode = 'IDR 'AND NULLIF(wb.Completed,'01/01/1999') IS NOT NULL THEN NULLIF(wb.Completed,'01/01/1999') END AS DATE)) [IDR]
        ,Max(CAST(CASE WHEN KDTID in (26,54) and  KeyDateIntervalActionGUID IN ('83CECA52-47E1-4CFC-A0C5-1E00AB8940AC','AA39E571-5488-4632-BE9D-EB4E77FCF608')  AND NULLIF(wb.Completed,'01/01/1999') IS NOT NULL THEN NULLIF(wb.Completed,'01/01/1999') END AS DATE)) [SD]
        ,Max(CAST(CASE WHEN KDTID in (26,54) and  KeyDateIntervalActionGUID IN ('80ACDF6B-106A-430E-BEE6-8C5393141764','5E9C07B4-6835-4C2A-ADED-BD579941AD70')  AND NULLIF(wb.Completed,'01/01/1999') IS NOT NULL THEN NULLIF(wb.Completed,'01/01/1999') END AS DATE)) [PLV]
        --,CASE WHEN  MAX(KeyDateIntervalActionGUID) =  'e71a28c4-ef5e-4a19-894f-933a3bd26570' AND wb.Completed IS NOT NULL THEN Cast(wb.Completed AS DATE) END AS [SD]
    FROM [MS_LIVE_Clone].[dbo].[udvwWorkPlanBase] WB
    WHERE  KDIActive = '1' AND KDTID in (26,54) and wb.Completed<=@Date
    Group by clid,fileID
)MM ON mm.KO_SD_Clid = f.clid and f.fileDepartment = 'R&D/SALT' and f.fileID = mm.fileID
where f.fileType in ('R&D','SALT')
--SELECT * FROM #T

--SELECT *
--FROM
--Operations..StudyInfo_Finance

if object_id('tempdb.dbo.#RawDog', 'U') is not null
drop table #RawDog;

SELECT a.*
,   'Year' + CAST(DENSE_RANK()OVER(Partition By [Matter #] Order by COALESCE(Year,9999)) as varchar(2)) YearP
,   ph.OrigWIP
,   CASE WHEN md.Arrangement LIKE 'FF%' THEN 'FF' ELSE CAST(LEFT(ph.FeeCapPct/100,4) as varchar(4)) END Arrangement
,   CASE WHEN md.Arrangement LIKE 'FF%' THEN ph.OrigWIP ELSE ph.AdjWIP END RevisedWIP
,(1.00-TotalPct) meh
,   CASE WHEN md.Arrangement LIKE 'FF%' THEN ph.OrigWIP ELSE ph.AdjWIP END * (1.00-TotalPct) RemainingWIP
,   md.Arrangement FeeType
,   ph.AdjWIP E3RevisedWIP
,   ph.AdjWIP - CASE WHEN md.Arrangement LIKE 'FF%' THEN ph.OrigWIP ELSE ph.AdjWIP END Variance
INTO #RawDog
FROM (
SELECT
    SI.[Matter Number]                          [Matter #]
,   SI.[Project ID]                             MattIndex
,   SI.[Client Name]                            [Client Name]
,   SI.[Project Name]                           [Years]
,   SI.[Engagement Signed Date]
,   (SELECT TOP 1 MattStatusDate FROM TE_3E_PROD_Clone..Matter M WHERE M.NUmber COLLATE SQL_Latin1_General_CP1_CI_AS = SI.[Matter Number] ) MattStatusDate
,   SI.ClosedDate
,   a.Year                                      [Year]
,   a.FEDState
--, a.Quarter                                   [Quarter]
--, CONCAT(SI.[Matter Number],CAST(a.Year as char(4)) COLLATE SQL_Latin1_General_CP1_CI_AS,CAST(a.Quarter as char(2)) COLLATE SQL_Latin1_General_CP1_CI_AS) [Conct]
,   a.ControlGroup                              [Control Group]
--, COALESCE(a.Tier,4)                                      [Tier Designation]
,   a.TriggerType                               [Trigger Type]
,   SI.Staus                                    [Status]
,   a.Total                                     [Tier Credit]
,   a.Total*(a.FeeCapPct/100)                   [Tier WIP]
,   t.SD
,   CASE
        WHEN t.SD <=@Date THEN 0.1
        WHEN a.FinalCredit is not null then 0.1
        WHEN t.PLV <=@Date then 0.1
        WHEN a.ValueAssessment is not null then 0.1
        WHEN t.IDR <=@Date then 0.1
        WHEN t.KOCompleted <=@Date then 0.1
        ELSE 0.00
    END                                         [KOCompletePct]
,   CASE
        WHEN t.SD <=@Date THEN                  CONVERT(CHAR(10),COALESCE(t.KOCompleted,t.IDR,a.SentTo3EVA,t.PLV,a.SentTo3EFN,t.SD),101)
        WHEN a.FinalCredit is not null then     CONVERT(CHAR(10),COALESCE(t.KOCompleted,t.IDR,a.SentTo3EVA,t.PLV,a.SentTo3EFN,t.SD),101)
        WHEN t.PLV <=@Date then                 CONVERT(CHAR(10),COALESCE(t.KOCompleted,t.IDR,a.SentTo3EVA,t.PLV,a.SentTo3EFN,t.SD),101)
        WHEN a.ValueAssessment is not null then CONVERT(CHAR(10),COALESCE(t.KOCompleted,t.IDR,a.SentTo3EVA,t.PLV,a.SentTo3EFN,t.SD),101)
        WHEN t.IDR <=@Date then                 CONVERT(CHAR(10),COALESCE(t.KOCompleted,t.IDR,a.SentTo3EVA,t.PLV,a.SentTo3EFN,t.SD),101)
        WHEN t.KOCompleted <=@Date then         CONVERT(CHAR(10),COALESCE(t.KOCompleted,t.IDR,a.SentTo3EVA,t.PLV,a.SentTo3EFN,t.SD),101)
        ELSE 'Not Completed'
    END                                         [KOCompleteDate]
,   CASE
        WHEN t.SD <=@Date THEN 0.2
        WHEN a.FinalCredit is not null then 0.2
        WHEN t.PLV <=@Date then 0.2
        WHEN a.ValueAssessment is not null then 0.2
        WHEN t.IDR <=@Date then 0.2
        ELSE 0.00
    END                                         [IDRCompletePct]
,   CASE
        WHEN t.SD <=@Date THEN                  CONVERT(CHAR(10),COALESCE(t.IDR,a.SentTo3EVA,t.PLV,a.SentTo3EFN,t.SD),101)
        WHEN a.FinalCredit is not null then     CONVERT(CHAR(10),COALESCE(t.IDR,a.SentTo3EVA,t.PLV,a.SentTo3EFN,t.SD),101)
        WHEN t.PLV <=@Date then                 CONVERT(CHAR(10),COALESCE(t.IDR,a.SentTo3EVA,t.PLV,a.SentTo3EFN,t.SD),101)
        WHEN a.ValueAssessment is not null then CONVERT(CHAR(10),COALESCE(t.IDR,a.SentTo3EVA,t.PLV,a.SentTo3EFN,t.SD),101)
        WHEN t.IDR <=@Date then                 CONVERT(CHAR(10),COALESCE(t.IDR,a.SentTo3EVA,t.PLV,a.SentTo3EFN,t.SD),101)
        ELSE 'Not Completed'
    END                                         [IDRCompleteDate]
,   CASE
        WHEN t.SD <=@Date THEN 0.2
        WHEN a.FinalCredit is not null then 0.2
        WHEN t.PLV <=@Date then 0.2
        WHEN a.ValueAssessment is not null then 0.2
        ELSE 0.00
    END                                         [VACompletePct]
,   CASE
        WHEN t.SD <=@Date THEN                  CONVERT(CHAR(10),COALESCE(a.SentTo3EVA,t.PLV,a.SentTo3EFN,t.SD),101)
        WHEN a.FinalCredit is not null then     CONVERT(CHAR(10),COALESCE(a.SentTo3EVA,t.PLV,a.SentTo3EFN,t.SD),101)
        WHEN t.PLV <=@Date then                 CONVERT(CHAR(10),COALESCE(a.SentTo3EVA,t.PLV,a.SentTo3EFN,t.SD),101)
        WHEN a.ValueAssessment is not null then CONVERT(CHAR(10),COALESCE(a.SentTo3EVA,t.PLV,a.SentTo3EFN,t.SD),101)
        ELSE 'Not Completed'
    END                                         [VACompleteDate]
,   CASE
        WHEN t.SD <=@Date THEN 0.2
        WHEN a.FinalCredit is not null then 0.2
        WHEN t.PLV <=@Date then 0.2
        ELSE 0.00
    END                                         [PLVCompletePct]
,   CASE
        WHEN t.SD <=@Date THEN                  CONVERT(CHAR(10),COALESCE(t.PLV,a.SentTo3EFN,t.SD),101)
        WHEN a.FinalCredit is not null then     CONVERT(CHAR(10),COALESCE(t.PLV,a.SentTo3EFN,t.SD),101)
        WHEN t.PLV <=@Date then                 CONVERT(CHAR(10),COALESCE(t.PLV,a.SentTo3EFN,t.SD),101)
        ELSE 'Not Completed'
    END                                         [PLVCompleteDate]
,   CASE WHEN a.FEDState = 'FED' THEN CASE
     --   WHEN a.FinalCredit is not null and a.HasFedStateSplit = 1 THEN 0.2
        --WHEN a.FinalCredit is not null /*and a.HasFedStateSplit != 1*/ then 0.4
        --WHEN t.SD <=@Date THEN 0.4
        WHEN t.SD <=@Date THEN 0.4
        WHEN a.FinalCredit is not null and a.HasFedStateSplit = 1 THEN 0.2
        WHEN a.FinalCredit is not null /*and a.HasFedStateSplit != 1*/ then 0.4
        ELSE 0.00
    END END                                     [Final#sSentPctFED]

--, CASE WHEN a.FEDState = 'FED' THEN CASE
--      WHEN t.SD <=@Date THEN 0.4
--      WHEN a.FinalCredit is not null and a.HasFedStateSplit = 1 THEN 0.2
--      WHEN a.FinalCredit is not null and a.HasFedStateSplit != 1 THEN 0.4
--      ELSE 0.00
--  END END                                     [Final#sSentPctFED]
,   CASE WHEN a.FEDState = 'FED' THEN CASE
        WHEN t.SD <=@Date THEN CONVERT(CHAR(10),COALESCE(a.SentTo3EFN,t.SD),101)
        WHEN a.FinalCredit is not null then CONVERT(CHAR(10),COALESCE(a.SentTo3EFN,t.SD),101)
        ELSE 'Not Completed'
    END END                                             [Final#sSentDateFED]


,   CASE WHEN a.FEDState != 'FED' THEN CASE
        --WHEN a.FinalCredit is not null and a.HasFedStateSplit = 1 THEN 0.2
        --WHEN a.FinalCredit is not null /*and a.HasFedStateSplit != 1*/ then 0.4
        --WHEN t.SD <=@Date THEN 0.4
        WHEN t.SD <=@Date THEN 0.4
        WHEN a.FinalCredit is not null and a.HasFedStateSplit = 1 THEN 0.2
        WHEN a.FinalCredit is not null /*and a.HasFedStateSplit != 1*/ then 0.4
        ELSE 0.00
    END END                                             [Final#sSentPctState]


--, CASE WHEN a.FEDState != 'FED' THEN CASE
--      WHEN t.SD <=@Date THEN 0.4
--      WHEN a.FinalCredit is not null and a.HasFedStateSplit = 1 THEN 0.2
--      WHEN a.FinalCredit is not null and a.HasFedStateSplit != 1 THEN 0.4
--      ELSE 0.00
--  END END                                             [Final#sSentPctState]

,   CASE WHEN a.FEDState != 'FED' THEN CASE
        WHEN t.SD <=@Date THEN CONVERT(CHAR(10),COALESCE(a.SentTo3EFN,t.SD),101)
        WHEN a.FinalCredit is not null then CONVERT(CHAR(10),COALESCE(a.SentTo3EFN,t.SD),101)
        ELSE 'Not Completed'
    END END                                             [Final#sSentDateState]
,   CASE
        WHEN t.SD <=@Date THEN 0.1
        ELSE 0.00
    END                                         [ReportDeliveredPct]
,   CASE
        WHEN t.SD <=@Date THEN CONVERT(CHAR(10),t.SD,101)
        ELSE 'Not Completed'
    END                                         [ReportDeliveredDate]
,   CASE
        WHEN t.SD <=@Date THEN 1.0
        WHEN a.FinalCredit is not null AND a.HasFedStateSplit = 1 then 0.7
        WHEN a.FinalCredit is not null then 0.9
        WHEN t.PLV <=@Date then 0.5
    --  WHEN a.ValueAssessment is not null then 0.5
        WHEN t.IDR <=@Date then 0.3
        WHEN t.KOCompleted <=@Date then 0.1
        ELSE 0.00
    END                                         [TotalPct]
,   null                                        [Comments]
,   SI.[Project Manager]                        [PM]
,   SI.TD                                       [TD]
,   SI.BD1                                      [BD]
,   CASE
        WHEN a.TriggerType in ('FN','FR','VA') THEN (a.Total*(a.FeeCapPct/100)) *
            CASE
                WHEN t.SD <=@Date THEN 1.0
                WHEN a.FinalCredit is not null AND a.HasFedStateSplit = 1 then 0.7
                WHEN a.FinalCredit is not null then 0.9
                WHEN t.PLV <=@Date then 0.5
            --  WHEN a.ValueAssessment is not null then 0.5
                WHEN t.IDR <=@Date then 0.3
                WHEN t.KOCompleted <=@Date then 0.1
                ELSE 0.00
            END
    END                                         [RevbasedonPct]
,LastStatusUpdateDate
,CASE WHEN a.HasFedStateSplit = 0 THEN 100 ELSE a.RevenuePct END RevenuePct
,a2.Status PrevStatus
,a.HasFedStateSplit
,a.HasFedandState

FROM
    Operations..StudyInfo_Finance SI
JOIN
    #Aaron a on a.MattIndex = SI.[Project ID]
LEFT JOIN
    #Aaron2 a2 on a2.MattIndex = SI.[Project ID]
LEFT JOIN
    MS_LIVE_Clone.config.dbClient c on c.clextID = SI.[Client ID]
LEFT JOIN
    #T t on t.fileid = SI.[File ID]
LEFT JOIN
    (
        SELECT
        doc.clID
        ,
        doc.fileid
        ,
        Cast(MAX(docAuthored) AS DATE) AS LastStatusUpdateDate
    FROM
        MS_LIVE_Clone.config.dbDocument AS doc
        JOIN
        MS_LIVE_Clone.config.dbfile f ON f.fileID = doc.fileID AND f.clID = doc.clID
    GROUP BY
       doc.clID
       ,doc.fileid
    ) SU ON SU.fileid = SI.[File ID]
WHERE
    SI.[Project Class] IN ('R&D Services','SALT Services') and SI.[Fee Type] != 'Hourly'
    and a.YEar is not null
    --and si.[Matter Number] = '002035.0003'

GROUP BY
    SI.[Matter Number]
,   SI.[Client Name]
,   SI.[Project Name]
,   SI.[Engagement Signed Date]
,   SI.ClosedDate
,   a.Year
,   a.FEDState
--, a.Quarter
--, CONCAT(SI.[Matter Number],CAST(a.Year as char(4)) COLLATE SQL_Latin1_General_CP1_CI_AS,CAST(a.Quarter as char(2)) COLLATE SQL_Latin1_General_CP1_CI_AS,a.ControlGroup  COLLATE SQL_Latin1_General_CP1_CI_AS)
,   a.ControlGroup
--, COALESCE(a.Tier,4)
,   a.TriggerType
,   SI.Staus
,   a.Total
,   a.Total*(a.FeeCapPct/100)
,   t.SD
,   CASE
        WHEN t.SD <=@Date THEN 0.1
        WHEN a.FinalCredit is not null then 0.1
        WHEN t.PLV <=@Date then 0.1
        WHEN a.ValueAssessment is not null then 0.1
        WHEN t.IDR <=@Date then 0.1
        WHEN t.KOCompleted <=@Date then 0.1
        ELSE 0.00
    END
,   CASE
        WHEN t.SD <=@Date THEN                  CONVERT(CHAR(10),COALESCE(t.KOCompleted,t.IDR,a.SentTo3EVA,t.PLV,a.SentTo3EFN,t.SD),101)
        WHEN a.FinalCredit is not null then     CONVERT(CHAR(10),COALESCE(t.KOCompleted,t.IDR,a.SentTo3EVA,t.PLV,a.SentTo3EFN,t.SD),101)
        WHEN t.PLV <=@Date then                 CONVERT(CHAR(10),COALESCE(t.KOCompleted,t.IDR,a.SentTo3EVA,t.PLV,a.SentTo3EFN,t.SD),101)
        WHEN a.ValueAssessment is not null then CONVERT(CHAR(10),COALESCE(t.KOCompleted,t.IDR,a.SentTo3EVA,t.PLV,a.SentTo3EFN,t.SD),101)
        WHEN t.IDR <=@Date then                 CONVERT(CHAR(10),COALESCE(t.KOCompleted,t.IDR,a.SentTo3EVA,t.PLV,a.SentTo3EFN,t.SD),101)
        WHEN t.KOCompleted <=@Date then         CONVERT(CHAR(10),COALESCE(t.KOCompleted,t.IDR,a.SentTo3EVA,t.PLV,a.SentTo3EFN,t.SD),101)
        ELSE 'Not Completed'
    END
,   CASE
        WHEN t.SD <=@Date THEN 0.2
        WHEN a.FinalCredit is not null then 0.2
        WHEN t.PLV <=@Date then 0.2
        WHEN a.ValueAssessment is not null then 0.2
        WHEN t.IDR <=@Date then 0.2
        ELSE 0.00
    END
,   CASE
        WHEN t.SD <=@Date THEN                  CONVERT(CHAR(10),COALESCE(t.IDR,a.SentTo3EVA,t.PLV,a.SentTo3EFN,t.SD),101)
        WHEN a.FinalCredit is not null then     CONVERT(CHAR(10),COALESCE(t.IDR,a.SentTo3EVA,t.PLV,a.SentTo3EFN,t.SD),101)
        WHEN t.PLV <=@Date then                 CONVERT(CHAR(10),COALESCE(t.IDR,a.SentTo3EVA,t.PLV,a.SentTo3EFN,t.SD),101)
        WHEN a.ValueAssessment is not null then CONVERT(CHAR(10),COALESCE(t.IDR,a.SentTo3EVA,t.PLV,a.SentTo3EFN,t.SD),101)
        WHEN t.IDR <=@Date then                 CONVERT(CHAR(10),COALESCE(t.IDR,a.SentTo3EVA,t.PLV,a.SentTo3EFN,t.SD),101)
        ELSE 'Not Completed'
    END
,   CASE
        WHEN t.SD <=@Date THEN 0.2
        WHEN a.FinalCredit is not null then 0.2
        WHEN t.PLV <=@Date then 0.2
        WHEN a.ValueAssessment is not null then 0.2
        ELSE 0.00
    END
,   CASE
        WHEN t.SD <=@Date THEN                  CONVERT(CHAR(10),COALESCE(a.SentTo3EVA,t.PLV,a.SentTo3EFN,t.SD),101)
        WHEN a.FinalCredit is not null then     CONVERT(CHAR(10),COALESCE(a.SentTo3EVA,t.PLV,a.SentTo3EFN,t.SD),101)
        WHEN t.PLV <=@Date then                 CONVERT(CHAR(10),COALESCE(a.SentTo3EVA,t.PLV,a.SentTo3EFN,t.SD),101)
        WHEN a.ValueAssessment is not null then CONVERT(CHAR(10),COALESCE(a.SentTo3EVA,t.PLV,a.SentTo3EFN,t.SD),101)
        ELSE 'Not Completed'
    END
,   CASE
        WHEN t.SD <=@Date THEN 0.2
        WHEN a.FinalCredit is not null then 0.2
        WHEN t.PLV <=@Date then 0.2
        ELSE 0.00
    END
,   CASE
        WHEN t.SD <=@Date THEN                  CONVERT(CHAR(10),COALESCE(t.PLV,a.SentTo3EFN,t.SD),101)
        WHEN a.FinalCredit is not null then     CONVERT(CHAR(10),COALESCE(t.PLV,a.SentTo3EFN,t.SD),101)
        WHEN t.PLV <=@Date then                 CONVERT(CHAR(10),COALESCE(t.PLV,a.SentTo3EFN,t.SD),101)
        ELSE 'Not Completed'
    END
,   CASE WHEN a.FEDState = 'FED' THEN CASE
        -- WHEN a.FinalCredit is not null and a.HasFedStateSplit = 1 THEN 0.2
        --WHEN a.FinalCredit is not null /*and a.HasFedStateSplit != 1*/ then 0.4
        --WHEN t.SD <=@Date THEN 0.4
        WHEN t.SD <=@Date THEN 0.4
        WHEN a.FinalCredit is not null and a.HasFedStateSplit = 1 THEN 0.2
        WHEN a.FinalCredit is not null /*and a.HasFedStateSplit != 1*/ then 0.4
        ELSE 0.00
    END END
,   CASE WHEN a.FEDState = 'FED' THEN CASE
        WHEN t.SD <=@Date THEN CONVERT(CHAR(10),COALESCE(a.SentTo3EFN,t.SD),101)
        WHEN a.FinalCredit is not null then CONVERT(CHAR(10),COALESCE(a.SentTo3EFN,t.SD),101)
        ELSE 'Not Completed'
    END END
,   CASE WHEN a.FEDState != 'FED' THEN CASE
     --   WHEN a.FinalCredit is not null and a.HasFedStateSplit = 1 THEN 0.2
        --WHEN a.FinalCredit is not null /*and a.HasFedStateSplit != 1*/ then 0.4
        --WHEN t.SD <=@Date THEN 0.4
        WHEN t.SD <=@Date THEN 0.4
        WHEN a.FinalCredit is not null and a.HasFedStateSplit = 1 THEN 0.2
        WHEN a.FinalCredit is not null /*and a.HasFedStateSplit != 1*/ then 0.4
        ELSE 0.00
    END END
,   CASE WHEN a.FEDState != 'FED' THEN CASE
        WHEN t.SD <=@Date THEN CONVERT(CHAR(10),COALESCE(a.SentTo3EFN,t.SD),101)
        WHEN a.FinalCredit is not null then CONVERT(CHAR(10),COALESCE(a.SentTo3EFN,t.SD),101)
        ELSE 'Not Completed'
    END END
,   CASE
        WHEN t.SD <=@Date THEN 0.1
        ELSE 0.00
    END
,   CASE
        WHEN t.SD <=@Date THEN CONVERT(CHAR(10),t.SD,101)
        ELSE 'Not Completed'
    END
,   CASE
        WHEN t.SD <=@Date THEN 1.0
        WHEN a.FinalCredit is not null AND a.HasFedStateSplit = 1 then 0.7
        WHEN a.FinalCredit is not null then 0.9
        WHEN t.PLV <=@Date then 0.5
    --  WHEN a.ValueAssessment is not null then 0.5
        WHEN t.IDR <=@Date then 0.3
        WHEN t.KOCompleted <=@Date then 0.1
        ELSE 0.00
    END
,   SI.[Project Manager]
,   SI.TD
,   SI.BD1
,   CASE
        WHEN a.TriggerType in ('FN','FR','VA') THEN (a.Total*(a.FeeCapPct/100)) *
            CASE
                WHEN t.SD <=@Date THEN 1.0
                WHEN a.FinalCredit is not null AND a.HasFedStateSplit = 1 then 0.7
                WHEN a.FinalCredit is not null then 0.9
                WHEN t.PLV <=@Date then 0.5
            --  WHEN a.ValueAssessment is not null then 0.5
                WHEN t.IDR <=@Date then 0.3
                WHEN t.KOCompleted <=@Date then 0.1
                ELSE 0.00
            END
    END
    ,SI.[Project ID]
    ,LastStatusUpdateDate
    ,CASE WHEN a.HasFedStateSplit = 0 THEN 100 ELSE a.RevenuePct END
    ,a2.Status
    ,a.HasFedStateSplit
    ,a.HasFedandState
    ) a
LEFT JOIN
    TE_3E_PROD_Clone..PGDetHdr_CCC ph on ph.MatterLkUp = a.MattIndex and ph.NxEndDate = '99991231' and ph.NxStartDate != '99991231'
LEFT JOIN
    TE_3E_PROD_Clone..MattDate MD on MD.MatterLkUp = a.MattIndex and MD.NxEndDate = '99991231' and MD.NxStartDate != '99991231'

    --Select * from #RawDog

if object_id('tempdb.dbo.#Pivot', 'U') is not null
    drop table #Pivot;

SELECT
    [Matter #]
,   MattIndex
,   MattStatusDate
,   ClosedDate
,   [Client Name]
,   [Engagement Signed Date]
,   [TD]
,   [PM]
,   [BD]
,   Max([SD]) [SD]
--, [FEDState]
,   Max([HasFedandState]) HasFedandState
,   Max([HasFedStateSplit]) [HasFedStateSplit]
,   STUFF((SELECT DISTINCT ',' + NULLIF(FEDState,'FED') FROM #RawDog I WHERE I.MattIndex = p.MattIndex FOR XML PATH('')),1,1,'') State
,   STUFF((SELECT DISTINCT ',' + CASE WHEN FEDState = 'FED' THEN 'FED' ELSE NULL END FROM #RawDog I WHERE I.MattIndex = p.MattIndex FOR XML PATH('')),1,1,'') FED
,   Years StudyYears
,   MAX(KOCompletePct) KOCompletePct
,   MAX(KOCompleteDate) KOCompleteDate
,   MAX(IDRCompletePct) IDRCompletePct
,   MAX(IDRCompleteDate) IDRCompleteDate
,   MAX(VACompletePct) VACompletePct
,   MAX(VACompleteDate) VACompleteDate
,   MAX(PLVCompletePct) PLVCompletePct
,   MAX(PLVCompleteDate) PLVCompleteDate
,   Case when MAX(Final#sSentPctFED) = 0.4 and MAX(Final#sSentPctState) = 0.4 then 0.2 else MAX(Final#sSentPctFED) end Final#sSentPctFED
,   MAX(Final#sSentDateFED) Final#sSentDateFED
,   Case when MAX(Final#sSentPctState) = 0.4 and MAX(Final#sSentPctFED) = 0.4 then 0.2 else MAX(Final#sSentPctState) end Final#sSentPctState
,   MAX(Final#sSentDateState) Final#sSentDateState
,   MAX(ReportDeliveredPct) ReportDeliveredPct
,   MAX(ReportDeliveredDate) ReportDeliveredDate
,   MAX(TotalPct)TotalPct
,   LastStatusUpdateDate
,   Comments
,   CASE WHEN SUM(Year2) is null then MAX(E3RevisedWIP) ELSE SUM(Year1) END Year1
,   SUM(Year2) Year2
,   SUM(Year3) Year3
,   SUM(Year4) Year4
,   SUM(Year5) Year5
,   SUM(CASE WHEN FEDState = 'FED' THEN (COALESCE(Year1,0) + COALESCE(Year2,0) + COALESCE(Year3,0) + COALESCE(Year4,0) + COALESCE(Year5,0))* TRY_CAST(Arrangement as decimal(12,2)) ELSE 0 END) FEDCredit
,   SUM(CASE WHEN FEDState != 'FED' THEN (COALESCE(Year1,0) + COALESCE(Year2,0) + COALESCE(Year3,0) + COALESCE(Year4,0) + COALESCE(Year5,0))* TRY_CAST(Arrangement as decimal(12,2)) ELSE 0 END) StateCredit
,   CASE WHEN SUM(CASE WHEN FEDState = 'FED' THEN RevenuePct ELSE 0 END)>1.0 AND MAX(HasFedStateSplit) = 0 THEN 1.0 WHEN SUM(CASE WHEN FEDState = 'FED' THEN RevenuePct ELSE 0 END)>1.0 AND MAX(HasFedStateSplit) = 1 THEN 0.5 ELSE SUM(CASE WHEN FEDState = 'FED' THEN RevenuePct ELSE 0 END) END FEDRevPerc
,   CASE WHEN SUM(CASE WHEN FEDState != 'FED' THEN RevenuePct ELSE 0 END)>1.0  AND MAX(HasFedStateSplit) = 0 THEN 1.0 WHEN SUM(CASE WHEN FEDState != 'FED' THEN RevenuePct ELSE 0 END)>1.0  AND MAX(HasFedStateSplit) = 1 THEN 0.5 ELSE SUM(CASE WHEN FEDState != 'FED' THEN RevenuePct ELSE 0 END) END StateRevPerc
,   OrigWIP
,   Arrangement
,   RevisedWIP
,   MAX(RemainingWIP) RemainingWIP
,   FeeType
,   E3RevisedWIP
,   Variance
,   Status
,   PrevStatus
INTO #Pivot

FROM
(
SELECT *
FROM
#RawDog
) p
PIVOT
(SUM([Tier Credit]) FOR YearP IN ([Year1],[Year2],[Year3],[Year4],[Year5])
) as p
GROUP BY
[Matter #]
,   [Client Name]
,   [Engagement Signed Date]
,   [TD]
,   [PM]
,   [BD]
,   Years
--, [SD]
--, [FEDState]
--, [HasFedStateSplit]
--, KOCompletePct
--, KOCompleteDate
--, IDRCompletePct
--, IDRCompleteDate
--, VACompletePct
--, VACompleteDate
--, PLVCompletePct
--, PLVCompleteDate
--, Final#sSentPct
--, Final#sSentDate
--, ReportDeliveredPct
--, ReportDeliveredDate
--, TotalPct
,   Comments
,   OrigWIP
,   Arrangement
,   RevisedWIP
--, RemainingWIP
,   FeeType
,   E3RevisedWIP
,   Variance
,   Status
,   p.MattIndex
,   MattStatusDate
,   ClosedDate
,   LastStatusUpdateDate
,   PrevStatus



if object_id('tempdb.dbo.#Invoice', 'U') is not null
    drop table #Invoice;

SELECT Number
,   MattINdex
,   InvNumber
,   InvDate
,   PracticeGroupDesc
,   ArrangementDesc
,   COUNT(1) InvNbr
,   SUM(OrgFee) OrgAmt
,   SUM(AdjWIP) AdjWIP
INTO #Invoice
FROM
TE_3E_PROD_Clone..InvMaster IM
OUTER APPLY
(SELECT TOP 1 md.*,pg.Description PracticeGroupDesc, A.Description ArrangementDesc
FROM
TE_3E_PROD_Clone..MattDate MD
JOIN
TE_3E_PROD_Clone..PracticeGroup PG on PG.Code = MD.PracticeGroup
JOIN
TE_3E_PROD_Clone..Arrangement A on A.Code = MD.Arrangement
WHERE IM.LeadMatter = MD.MatterLkUp and @Date between MD.NxStartDate and MD.NxEndDate
Order by MD.NxStartDate
) MD
OUTER APPLY
(SELECT TOP 1 *
FROM
TE_3E_PROD_Clone..PGDetHdr_CCC PH
WHERE IM.LeadMatter = PH.MatterLkUp and @Date between PH.NxStartDate and PH.NxEndDate
Order by PH.NxStartDate
) PH
JOIN
TE_3E_PROD_Clone..Matter M on M.MattIndex = IM.LeadMatter
WHERE
Arrangement IN ('CONT','FFFINAL','FFPHASE') and IM.PostDate<=@Date --and COALESCE(ReversedDate,@Date)>=@Date
AND PracticeGroup in (110,120) --and m.Number = '008070.0009'
GROUP BY
Number
,   InvNumber
,   InvDate
,   PracticeGroupDesc
,   ArrangementDesc
,   MattINdex
UNION ALL
SELECT Number
,   MattINdex
,   InvNumber
,   ReversedDate InvDate
,   PracticeGroupDesc
,   ArrangementDesc
,   COUNT(1) InvNbr
,   -SUM(OrgFee) OrgAmt
,   SUM(AdjWIP) AdjWIP
FROM
TE_3E_PROD_Clone..InvMaster IM
OUTER APPLY
(SELECT TOP 1 md.*,pg.Description PracticeGroupDesc, A.Description ArrangementDesc
FROM
TE_3E_PROD_Clone..MattDate MD
JOIN
TE_3E_PROD_Clone..PracticeGroup PG on PG.Code = MD.PracticeGroup
JOIN
TE_3E_PROD_Clone..Arrangement A on A.Code = MD.Arrangement
WHERE IM.LeadMatter = MD.MatterLkUp and @Date between MD.NxStartDate and MD.NxEndDate
Order by MD.NxStartDate
) MD
OUTER APPLY
(SELECT TOP 1 *
FROM
TE_3E_PROD_Clone..PGDetHdr_CCC PH
WHERE IM.LeadMatter = PH.MatterLkUp and @Date between PH.NxStartDate and PH.NxEndDate
Order by PH.NxStartDate
) PH
JOIN
TE_3E_PROD_Clone..Matter M on M.MattIndex = IM.LeadMatter
WHERE
Arrangement IN ('CONT','FFFINAL','FFPHASE') and IM.ReversedDate<=@Date and IsReversed = 1
AND PracticeGroup in (110,120) --and m.Number = '008070.0009'
GROUP BY
Number
,   InvNumber
,   ReversedDate
,   PracticeGroupDesc
,   ArrangementDesc
,   MattINdex
UNION ALL
SELECT
    m.Number
    ,   MattINdex
,   CRInvNumber
,   CRDate
,   PracticeGroupDesc
,   ArrangementDesc
,   COUNT(1) InvNbr
,   SUM(CrAmt) OrgAmt
,   SUM(AdjWIP) AdjWIP
from
(
    SELECT
        IM.InvIndex
    ,   IM.InvNumber CRInvNumber
    ,   IM.CrNoteNumber
    ,   IM.InvDate CRDate
    ,   IM.OrgAmt CrAmt
    ,   CASE WHEN SUM(COALESCE(CN.Amount,-ARI.ARAmt,-IM.OrgAmt)) >0 then COALESCE(IMC.InvNumber,IMR.InvNumber,IMO.InvNumber,'Unal') ELSE MAX(CASE WHEN SUM(COALESCE(CN.Amount,-ARI.ARAmt,-IM.OrgAmt)) >0 then COALESCE(IMC.InvNumber,IMR.InvNumber,IMO.InvNumber,'Unal') end)OVER(PARTITION BY IM.InvNumber) end AppliedToInvNumber
    ,   CASE WHEN SUM(COALESCE(CN.Amount,-ARI.ARAmt,-IM.OrgAmt))>0 then COALESCE(IMO.InvDate,IMR.InvDate,ARUN.InvDate,CN.CrDate) ELSE MAX(CASE WHEN SUM(COALESCE(CN.Amount,-ARI.ARAmt,-IM.OrgAmt))>0 then COALESCE(IMO.InvDate,IMR.InvDate,ARUN.InvDate,CN.CrDate) end)OVER(PARTITION BY IM.InvNumber) end AppliedToDate
    ,   SUM(COALESCE(CN.Amount,-ARI.ARAmt,-IM.OrgAmt)) AppliedToAmt
    ,   IM.LeadMatter
    FROM
        TE_3E_PROD_Clone..InvMaster IM
    LEFT JOIN
        TE_3E_PROD_Clone..ProfMaster PM on PM.InvMaster = IM.InvIndex
    LEFT JOIN
        TE_3E_PROD_Clone..CreditNote CN on CN.CreditNoteIndex = pm.CreditNoteIndex
    LEFT JOIN
        TE_3E_PROD_Clone..Invmaster IMC on IMC.InvIndex = cn.InvMaster--Applied to
    LEFT JOIN
        TE_3E_PROD_Clone..ARDetail AR on AR.AppInvMaster = IM.InvIndex and ARList = 'Bill' and cn.CreditNoteID is null
    LEFT JOIN
        TE_3E_PROD_Clone..ARDetail ARA on ARA.AppInvMaster = AR.AppInvMaster and ARA.ARList = 'Receipt' and cn.CreditNoteID is null
    LEFT JOIN
        TE_3E_PROD_Clone..ARDetail ARI ON ARA.RcptMaster = ARI.RcptMaster AND ARA.ARDetailID != ARI.ARDetailID and cn.CreditNoteID is null
    LEFT JOIN
        TE_3E_PROD_Clone..InvMaster IMR on IMR.InvIndex = ARI.AppInvMaster and cn.CreditNoteID is null
    LEFT JOIN
        TE_3E_PROD_Clone..ARDetail ARUN on ARUN.UnalType = ARI.UnalType and ARUN.Payor = ARI.Payor and ARUN.ARDetailID != ARI.ARDetailID
                                    AND ARUN.ARAmt = -ARI.ARAmt
    OUTER APPLY
        (SELECT TOP 1 *
        FROM
        TE_3E_PROD_Clone..InvMaster IMO where IMO.InvNUmber = IM.InvNumber and IMO.InvIndex != IM.InvIndex and IMO.CrNoteNumber is null
        ORDER BY CASE WHEN IM.IsReversed = IMO.IsReversed then 0 else 1 end
        ) IMO

    WHERE
        IM.OrgAmt<0 AND CRDate<=@Date
        --and IM.InvNumber = '121172'
    GROUP BY
        IM.InvIndex
    ,   IM.InvNumber
    ,   IM.CrNoteNumber
    ,   IM.InvDate
    ,   IM.OrgAmt

    ,   COALESCE(IMC.InvNumber,IMR.InvNumber,IMO.InvNumber,'Unal')
    ,   COALESCE(IMO.InvDate,IMR.InvDate,ARUN.InvDate,CN.CrDate)
    ,   IM.LeadMatter
) a
JOIN
TE_3E_PROD_Clone..Matter m on m.MattIndex = a.LeadMatter
JOIN
TE_3E_PROD_Clone..Client c on c.clientindex = m.client
OUTER APPLY
    (SELECT TOP 1 md.*,pg.Description PracticeGroupDesc, A2.Description ArrangementDesc
    FROM
    TE_3E_PROD_Clone..MattDate MD
    JOIN
    TE_3E_PROD_Clone..PracticeGroup PG on PG.Code = MD.PracticeGroup
    JOIN
    TE_3E_PROD_Clone..Arrangement A2 on A2.Code = MD.Arrangement
    WHERE a.LeadMatter = MD.MatterLkUp and @Date between MD.NxStartDate and MD.NxEndDate
    Order by MD.NxStartDate
    ) MD
OUTER APPLY
    (SELECT TOP 1 *
    FROM
    TE_3E_PROD_Clone..PGDetHdr_CCC PH
    WHERE a.LeadMatter = PH.MatterLkUp and @Date between PH.NxStartDate and PH.NxEndDate
    Order by PH.NxStartDate
    ) PH
WHERE
Arrangement IN ('CONT','FFFINAL','FFPHASE') --and IM.PostDate<=@Date and COALESCE(ReversedDate,@Date)>=@Date
AND PracticeGroup in (110,120)  --and m.Number = '008070.0009'
group by
    m.Number
,   CRInvNumber
,   CRDate
,   PracticeGroupDesc
,   ArrangementDesc
,   MattINdex




SELECT P.*
,   CASE WHEN TotalPct<0.5 THEN 0 ELSE E3RevisedWIP*TotalPct END RecognizedWIP
,   I.OrgAmt TotalInvoiced
,   I.OrgAmtLastMonth
,   FEDCredit*TotalPct FEDRev
,   StateCredit*TotalPct StateRev
,   CASE
        WHEN I.OrgAmt<(FEDCredit*TotalPct) AND TotalPct>=0.5 THEN FEDCredit*TotalPct-I.OrgAmt
        ELSE 0
    END AccruedFEDRevenue
,   CASE

        WHEN I.OrgAmt>=(FEDCredit*TotalPct) AND I.OrgAmt<(E3RevisedWIP*TotalPct) AND TotalPct>=0.5 THEN E3RevisedWIP*TotalPct-I.OrgAmt
        ELSE 0
    END AccruedSALTRevenue
,   CASE WHEN I.OrgAmt>=(E3RevisedWIP*TotalPct) AND TotalPct>=0.5 AND ISNULL(FEDCredit,0)*TotalPct>=0 THEN I.OrgAmt-(E3RevisedWIP*TotalPct)
        ELSE 0
    END DeferredFEDRevenue
,   CASE WHEN I.OrgAmt>=(E3RevisedWIP*TotalPct) AND TotalPct>=0.5 AND FEDCredit*TotalPct<=0 THEN I.OrgAmt-E3RevisedWIP*TotalPct
        ELSE 0
    END DeferredSALTRevenue


FROM
#Pivot p
LEFT JOIN
(SELECT MattIndex,SUM(OrgAmt) OrgAmt,SUM(CASE WHEN InvDate between DATEADD(d,-DAY(@Date),@Date)+1 AND @Date THEN OrgAmt ELSE 0 END) OrgAmtLastMonth
FROM
#Invoice GROUP BY MattIndex) I on I.MattIndex = p.MattIndex
WHERE
Status IN (@Status)

--Order by
--p.[Matter #] Asc