DECLARE @AsofDATE AS DATE
                    DECLARE @AsofDateINT AS Int
                    SET @AsofDATE ='1/1/2018'
                    SET @AsofDateINT = 20180101

                               SELECT  count(S.ClaimNum) Count_of_Claims , sum(ReserveIndemnityAmount) as RD_REserve ,@AsofDATE  as AsofDate -- INTO #tempRD
                                            FROM    ( SELECT    f.* ,
                                                                ROW_NUMBER() OVER ( PARTITION BY ClaimNum ORDER BY f.ModifiedDate DESC ) AS Row_order_desc
                                                      FROM      [dbo].[Snapshot] f
                                                      WHERE     CAST(f.ModifiedDate AS DATE) <= @AsofDATE
                                                    ) S
                                                left outer join (SELECT ClaimKey, SUM( t.LossRsvAmt)  AS ReserveIndemnityAmount  FROM
                                                                Stg.Claim_Transaction t  WHERE TransactionDate <@AsofDateINT group by ClaimKey) T

                                                on t.ClaimKey = s.ClaimID
                                            WHERE   S.Row_order_desc = 1
                                                    AND S.DerivedClaimStatus NOT IN ( 'Closed',      'Cancelled',    'Abandoned', 'Record only','Opened in error' )
                                                    and s.specialty = 'RD'

Count_of_Claims     RD_REserve          AsofDate
            15317               112192.15           2018-01-01

Count_of_Claims     RD_REserve          AsofDate
            15317               112192.15           2017-01-12
            15567               111592.15           2017-01-11
            15356               15492.15            2017-01-10