DROP TABLE #temp;

with

cr as

(

SELECT    At.SysNo AS AtSysNo ,

                    ( CASE WHEN At.Source = 1 THEN At.VendorSysNo

                           ELSE At.DistributorSysNo

                      END ) AS VdSysNo ,

                    At.Source ,

                    At.BizTypeCode ,

                    BT.Name ,

                    ABC.*

          FROM      BBCAccount.dbo.Account (NOLOCK) At

                    LEFT JOIN ( SELECT  *

                                FROM    BBCAccount.dbo.BusinessType (NOLOCK) BT

                                WHERE   BT.ParentCode IS NULL

                                        AND Status = 1

                                        AND BT.IsSystem = 1

                                        AND BT.SysNo IS NOT NULL

                              ) AS BT ON At.BizTypeCode = BT.Code

                    LEFT JOIN ( SELECT  ABC.SysNo ,

                                        ABC.BizTypeCode AS ABCBTC ,

                                        ABC.AccountSysNo ,

                                        ABC.BizID ,

                                        ABC.Balance ,

                                        ABC.AdjustAmount ,

                                        ABC.AdjustType ,

                                        ABC.AdjustedBalance ,

                                        ABC.InDate

                                FROM    BBCFinance.dbo.AccountBalanceChange (NOLOCK) ABC

                                        LEFT JOIN BBCFinance.dbo.AccountAdjustReceipt (NOLOCK)AAR  ON AAR.AccountSysNo = ABC.AccountSysNo      AND AAR.ChangeNo = ABC.BizID AND AAR.Status=2

                              ) AS ABC ON ABC.AccountSysNo = At.SysNo 

	 )

	 SELECT * INTO #temp FROM cr 

SELECT * FROM #temp WHERE    VdSysNo=2384

SELECT TE.* FROM   #temp TE

LEFT JOIN

 (

SELECT VdSysNo,Source, Name,MAX(InDate) AS InDate FROM #temp   WHERE VdSysNo=2384     GROUP  BY VdSysNo,Source,Name )

AS TT ON TT.VdSysNo=TE.VdSysNo AND TT.Source = TE.Source AND TT.InDate = TE.InDate AND TT.Name = TE.Name

WHERE TE.VdSysNo=2384