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=2384SELECT TE.* FROM #temp TELEFT 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.NameWHERE TE.VdSysNo=2384