I'm trying to make the result of one of my subqueries be used in another subquery but it doesn't work.
This is the query:
SELECT t.TenantName,
(SELECT SUM(Amount)
FROM tblTransaction
WHERE Amount > 0
AND TransactionDate >= '12/01/09'
AND TransactionDate <= '12/31/09'
AND TenantID = t.ID
AND TransactionCode = trans.TransactionCode) AmountPaid,
(SELECT SUM(Amount)
FROM tblTransaction
WHERE tblTransaction.TransactionCode = trans.TransactionCode
AND tblTransaction.TenantID = t.ID) - AmountPaid AmountOwedTotal,
(SELECT SUM(Amount)
FROM tblTransaction
WHERE tblTransaction.TransactionCode = trans.TransactionCode
AND tblTransaction.TenantID = t.ID
AND Amount < 0 AND TransactionDate >= '12/01/09'
AND TransactionDate <= '12/31/09') AmountOwedThisMonth,
code.Description, trans.TransactionDate
FROM tblTransaction trans
LEFT JOIN tblTenantTransCode code
ON code.ID = trans.TransactionCode
LEFT JOIN tblTenant t
ON t.ID = trans.TenantID
WHERE trans.TransactionDate >= '12/01/09'
AND trans.TransactionDate <= '12/31/09'
AND trans.Amount > 0
Sorry it's so complicated.
Where I do (subquery) - AmountPaid the SqlServer complains AmountPaid isn't a valid column name. How can I get access to the result of it's subquery?