Better written Select with Subquery

[santiago]

Can anybody see if this query can be written better?

SELECT a.AccountNbr, a.AccountName,
(SELECT SUM(b.DebitAmt) FROM b.Transactions as b WHERE b.acctid = a.acctid
AND DatePart(yyyy, b.TransactionDate) = 2004) as Debit,
(SELECT SUM(b.CreditAmt) FROM Transactions as b WHERE b.acctid = a.acctid
AND DatePart(yyyy, b.TransactionDate) = 2004) as Credit
FROM Account as a
WHERE a.EntityId = 1

Is there a way to get the two subqueries be built as one since their WHERE clause is the same?

Thanks in advance.

 

[r937]

select a.AccountNbr
     , a.AccountName
     , sum(b.DebitAmt)  as Debit
     , sum(b.CreditAmt) as Credit 
  from Account as a 
inner
  join Transactions as b 
    on a.acctid = b.acctid 
   and       year(b.TransactionDate) = 2004
 where a.EntityId = 1 
group
    by a.AccountNbr
     , a.AccountName

rudy

http://r937.com/

SQL Consulting

 

[tektipdjango]

Try this piece of code :

[b]DECLARE[/b] [COLOR=red]@beginning_of_2004[/color] datetime
[b]SET[/b] [COLOR=red]@beginning_of_2004[/color] = '1/01/2004'
[b]SELECT[/b] 
  a.AccountNbr, a.AccountName, b.Debit, b.Credit
  [b]FROM[/b]
  (
    [b]SELECT[/b]
    acctid,
    AccountNbr,
    AccountName
    [b]FROM[/b] Account 
    [b]WHERE[/b] EntityId = 1
  )[b]AS[/b] a
  [b]INNER JOIN[/b]
  (
    [b]SELECT[/b]
    acctid,
    [b]SUM[/b](DebitAmt)[b]AS[/b] Debit,
    [b]SUM[/b](CreditAmt)[b]AS[/b] Credit
    [b]FROM[/b] Transactions
    [b]WHERE[/b] TransactionDate>=[COLOR=red]@beginning_of_2004[/color]
  ) [b]AS[/b] b
  [b]ON[/b] a.acctid = b.acctid

I put a variable to avoid the use of transaction.date in a function.
It will allow the use of indexed search (if transaction.date is indexed) instead of a complete scan, speeding up the query.

django
bug exterminator
tips'n tricks addict

 

[tektipdjango]

Sorry, I forgot the

group by acctId,transactionDate

statement in the last select clause.

DECLARE @beginning_of_2004 datetime
SET @beginning_of_2004 = '1/01/2004'
SELECT
a.AccountNbr, a.AccountName, b.Debit, b.Credit
FROM
(
SELECT
acctid,
AccountNbr,
AccountName
FROM Account
WHERE EntityId = 1
)AS a
INNER JOIN
(
SELECT
acctid,
SUM(DebitAmt)AS Debit,
SUM(CreditAmt)AS Credit
FROM Transactions
GROUP BY acctid, TransactionDate
HAVING TransactionDate>=@beginning_of_2004

) AS b
ON a.acctid = b.acctid


django
bug exterminator
tips'n tricks addict

 

[r937]

i would rewrite

SELECT
acctid,
SUM(DebitAmt)AS Debit,
SUM(CreditAmt)AS Credit
FROM Transactions
GROUP BY acctid, TransactionDate
HAVING TransactionDate>=@beginning_of_2004

as

SELECT
acctid,
SUM(DebitAmt)AS Debit,
SUM(CreditAmt)AS Credit
FROM Transactions
WHERE TransactionDate>=@beginning_of_2004
GROUP BY acctid

otherwise the derived table b will have multiple rows per acctid, and thus each account in the results will too


rudy

http://r937.com/

SQL Consulting

 

[tektipdjango]

Right Rudy!

django
bug exterminator
tips'n tricks addict

 

[santiago]

Thanks for the tips.

Regards.