SELECT TenantName,
Description,
TransactionDate,
AmountPaid,
AmountRequired - AmountPaid AS AmountOwedTotal,
AmountOwedThisMonth
FROM (
SELECT t.TenantName,
code.Description,
trans.TransactionDate,
(
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
) AS AmountPaid,
(
SELECT SUM(Amount)
FROM tblTransaction
WHERE tblTransaction.TransactionCode = trans.TransactionCode
AND tblTransaction.TenantID = t.ID
) AS AmountRequired,
(
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'
) AS AmountOwedThisMonth,
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
) q