CODEWITHSUNDEEP
sql
Michael Tuma
Michael Tuma

Reputation: 57

Is a better way to join theses subqueries?

I have the results I required, however there must be a more efficient way to do this. I looked at a pivot, but that would not help me. Suggestions welcome!

SELECT GL1.CashPosition,
GL2.ARBalance,
GL3.APBalance,
GL4.LineOfCredit
FROM
(
Select GLAccounts.CurrencyCodeID,
SUM(GLAccounts.PostedBalance + GLAccounts.UnPostedBalance) AS CashPosition
FROM GLAccounts
WHERE GLAccounts.GLAccountID IN(1,3)
GROUP BY GLAccounts.CurrencyCodeID
) AS GL1
JOIN
(
Select GLAccounts.CurrencyCodeID,
SUM(GLAccounts.PostedBalance + GLAccounts.UnPostedBalance) AS ARBalance
FROM GLAccounts
WHERE GLAccounts.GLAccountID IN(5,6,7,9,10,11,12)
GROUP BY GLAccounts.CurrencyCodeID
) AS GL2
JOIN
(
Select GLAccounts.CurrencyCodeID,
SUM(GLAccounts.PostedBalance + GLAccounts.UnPostedBalance) AS APBalance
FROM GLAccounts
WHERE GLAccounts.GLAccountID IN(108,109)
GROUP BY GLAccounts.CurrencyCodeID
) AS GL3
JOIN
(
Select GLAccounts.CurrencyCodeID,
SUM(GLAccounts.PostedBalance + GLAccounts.UnPostedBalance) AS LineOfCredit
FROM GLAccounts
WHERE GLAccounts.GLAccountID IN(139,140)
GROUP BY GLAccounts.CurrencyCodeID
)AS GL4 ON GL3.CurrencyCodeID = GL4.CurrencyCodeID ON GL2.CurrencyCodeID 
= GL3.CurrencyCodeID ON GL1.CurrencyCodeID = GL2.CurrencyCodeID

Upvotes: 0

Views: 32

Answers (4)

Stu
Stu

Reputation: 32599

You can use a conditional sum using case expressions. This avoids repeatedly hitting the same table multiple times and significantly reduces your logical reads:

select
  Sum(case when GLAccountID in(1,3) then GLAccounts.PostedBalance + GLAccounts.UnPostedBalance end) as CashPosition,
  Sum(case when GLAccountID in(5,6,7,9,10,11,12) then GLAccounts.PostedBalance + GLAccounts.UnPostedBalance end) as ARBalance,
  Sum(case when GLAccountID in(108,109) then GLAccounts.PostedBalance + GLAccounts.UnPostedBalance end) as APBalance,
  Sum(case when GLAccountID in(139,140) then GLAccounts.PostedBalance + GLAccounts.UnPostedBalance end) as LineOfCredit
from GLAccounts

Upvotes: 0

d0little
d0little

Reputation: 476

You can case inside a sum

SELECT
SUM(CASE WHEN GLAccounts.GLAccountID IN (1,3) THEN GLAccounts.PostedBalance + GLAccounts.UnPostedBalance ELSE 0 END) AS CashPosition,
SUM(CASE WHEN GLAccounts.GLAccountID IN (5,6,7,9,10,11,12) THEN GLAccounts.PostedBalance + GLAccounts.UnPostedBalance ELSE 0 END) AS ARBalance,
SUM(CASE WHEN GLAccounts.GLAccountID IN (108,109) THEN GLAccounts.PostedBalance + GLAccounts.UnPostedBalance ELSE 0 END) AS APBalance,
SUM(CASE WHEN GLAccounts.GLAccountID IN (139,140) THEN GLAccounts.PostedBalance + GLAccounts.UnPostedBalance ELSE 0 END) AS LineOfCredit
FROM GLAccounts

Upvotes: 0

trincot
trincot

Reputation: 350147

You can use case when:

SELECT 
SUM(CASE WHEN GLAccounts.GLAccountID IN (1,3) 
    THEN GLAccounts.PostedBalance + GLAccounts.UnPostedBalance ELSE 0 END) AS CashPosition,
SUM(CASE WHEN GLAccounts.GLAccountID IN (5,6,7,9,10,11,12) 
    THEN GLAccounts.PostedBalance + GLAccounts.UnPostedBalance ELSE 0 END) AS ARBalance,
SUM(CASE WHEN GLAccounts.GLAccountID IN (108,109) 
    THEN GLAccounts.PostedBalance + GLAccounts.UnPostedBalance ELSE 0 END) AS APBalance,
SUM(CASE WHEN GLAccounts.GLAccountID IN (139,140) 
    THEN GLAccounts.PostedBalance + GLAccounts.UnPostedBalance ELSE 0 END) AS LineOfCredit
FROM GLAccounts

Upvotes: 2

Mark C
Mark C

Reputation: 129

Not necessarily an efficiency improvement, but I would suggest turning the subquery into a stored proc to neaten the code.

Upvotes: 0

Related Questions