Multiple Table Query

[RFH Reseller: spire128]

Hi All

Hope someone can help me, I am struggeling with a multiple table query so I hope there may be an Sql guru on here.

Okay I have 4 tables as follows
Accounts
Ajustments
LegalFees
Reciepts

here is what I am trying to do

SUM(Ajustments.Amounts) + SUM(LegalFees.Amounts) + Accounts.OpenBal - SUM(Reciepts.Amounts) AS CurrentBal

WHERE (AccountID = @AccountID)

I know the above syntax is not correct but it shows the result I am trying to get

The Accounts table only has 1 entry while the other tables may have none or many entries.

I have tried Joins I have played about with GroupBY and Subquieries but have not managed to get it right

Anyway I have been trying to make this work for the last 2 days and sometimes I get an answer but the calucutaion is incorrect and other times I get no answer returned.

Can anyone help please

[RFH Reseller: Sol]

I'd do this using a Stored Procedure:

CREATE PROCEDURE GetCurrentBalance
 @AccountId int,
 @CurrentBalance decimal
AS
DECLARE @Adjustments decimal,
 @LegalFees decimal,
 @Receipts decimal,
 @OpeningBalance decimal
SELECT @Adjustments = SUM(Amounts) FROM Adjustments WHERE AccountID = @AccountID
SELECT @LegalFees = SUM(Amounts) FROM LegalFees WHERE AccountID = @AccountID
SELECT @Receipts = SUM(Amounts) FROM Receipts WHERE AccountID = @AccountID
SELECT @OpeningBalance = OpenBal FROM Accounts WHERE AccountId = @AccountID
SELECT @CurrentBalance = @Adjustments + @LegalFees + @OpeningBalance - @Receipts
 
Return @CurrentBalance
GO

[RFH Reseller: spire128]

Hi

Many thanks for your reply I arrived at much the same solution, I did try almost the same code as u posted, the problem I found was that it returned no valaue at all if 1 of the tables did not have any entries, so my final solution was the stored proc below

ALTER PROCEDURE [dbo].[GetCurrentBal_Select]

(
@iAccountID Int
)

AS

DECLARE @Receipt_Balance Money , @Ajust_Balance Money, @LegalFees_Balance Money

SELECT

@Receipt_Balance = CASE
WHEN SUM(ReceiptAmount) IS NOT NULL THEN SUM(ReceiptAmount)
ELSE
0.00
END

FROM
Debt_AccountsReceipts
WHERE
AccountID = @iAccountID


SELECT

@Ajust_Balance = CASE
WHEN SUM(AjustAmount) IS NOT NULL THEN SUM(AjustAmount)
ELSE
0.00
END

FROM
Debt_AccountsAjustments
WHERE
AccountID = @iAccountID


SELECT

@LegalFees_Balance = CASE
WHEN SUM(FeesAmount) IS NOT NULL THEN SUM(FeesAmount)
ELSE
0.00
END
FROM
Debt_AccountsLegalFees
WHERE
AccountID = @iAccountID


SELECT
Acc_OpeningBalance + ((@Ajust_Balance + @LegalFees_Balance) - @Receipt_Balance) AS CurrentBal

FROM
Debt_Accounts
WHERE
AccountID = @iAccountID

Not sure how efficent this stored proc is?, as it could be seraching thru thousands of records when the user gets going.

[RFH Reseller: Sol]

That procedure looks fine, I can't think of any ways to optimize it as such. I'd make sure that AccountId is and Index in each of the tables to ensure that SQL server can pull those records quickly rather than doing a table scan.

You could also move the IF statements out of the SELECT statements to make the code easier to read ie.:

SELECT @LegalFees_Balance = SUM(FeesAmount) 
FROM Debt_AccountsLegalFees 
WHERE AccountID = @iAccountID 
 
IF (@LegalFees_Balance IS NULL)
    SELECT @LegalFees_Balance = 0.00

Not sure if that would speed up your query any, but it shouldn't slow it.

[Tanzy]

Using Sol's code example you could use ISNULL, that may be more efficent than an IF or CASE statement

CREATE PROCEDURE GetCurrentBalance
 @AccountId int,
 @CurrentBalance decimal
AS
DECLARE @Adjustments decimal,
 @LegalFees decimal,
 @Receipts decimal,
 @OpeningBalance decimal
SELECT @Adjustments = SUM(Amounts) FROM Adjustments WHERE AccountID = @AccountID
SELECT @LegalFees = SUM(Amounts) FROM LegalFees WHERE AccountID = @AccountID
SELECT @Receipts = SUM(Amounts) FROM Receipts WHERE AccountID = @AccountID
SELECT @OpeningBalance = OpenBal FROM Accounts WHERE AccountId = @AccountID
SELECT @CurrentBalance = ISNULL(@Adjustments, 0) + ISNULL(@LegalFees, 0) + ISNULL(@OpeningBalance, 0) - ISNULL(@Receipts, 0)
 
Return @CurrentBalance
GO

[RFH Reseller: Sol]

Great catch Tanzy, I should have realized that myself!

That should speed the query up.

[Tanzy]

One of those inbuilt functions that we all forget about Sol. Only remembered myself due to needing to use it the other day