Database

SELECT A.CNAME, B.ANAME FROM CLIENT A, DEALER B
WHERE A.CCODE=B.ACODE(+)

FUNCTION [dbo].[GetChildrenAccount] 
(@AccountID INT, 
 @DateFrom DATETIME,
 @DateTo DATETIME,
 --@TypeTransaction INT,
 --@Currnecy INT,
 @Branch INT) 

RETURNS DECIMAL(18,3)
     AS      
BEGIN 
    
DECLARE @Account_ID AS BIGINT,
        @IsLeaf AS BIT,
        @TotalValue AS DECIMAL(18,3),

SELECT @TotalValue = ISNULL(SUM(JournalDet_Debit),0) * CASE WHEN @Currnecy = 0 THEN AccountBranch_CurrencyConv ELSE 1 END
FROM AccountTree 
INNER JOIN Account_InBranch ON (AccountBranch_AccountID = Account_ID)
LEFT JOIN Journal_Details ON (Account_ID = JournalDet_AccountID) 
LEFT JOIN Journal_Head ON (Journal_ID = JournalDet_HeadID)
WHERE (Account_ParentID = @AccountID OR Account_ID = @AccountID )
AND Journal_BranchID = @Branch 
AND Journal_Date >= @DateFrom AND Journal_Date <= @DateTo
GROUP BY AccountBranch_BegBalDebit , AccountBranch_BalanceDebit , AccountBranch_CurrencyConv

OPEN GetAccount_ID      	      
	FETCH NEXT FROM GetAccount_ID INTO @Account_ID      
	WHILE @@fetch_Status = 0      
	BEGIN      
	SET @TotalValue = @TotalValue + dbo.GetChildrenAccount(@Account_ID,@DateFrom,@DateTo,@Branch)
	FETCH NEXT FROM GetAccount_ID INTO  @Account_ID     
	END      	      
	CLOSE GetAccount_ID      
DEALLOCATE GetAccount_ID  	

RETURN ISNULL(@TotalValue,0)
END