I'm trying to get the SUM between each date in the WHERE clause of the following query, not the cummulative sum, but the sum for the period in between the dates. For example:
Query:
SELECT
cdr.datefield AS 'date',
a.id AS 'id',
a.name AS 'name',
SUM(t.debit_amount - t.credit_amount) AS 'balance'
FROM calendar cdr
JOIN transactions t ON (cdr.datefield >= t.value_date)
JOIN accounts a ON (a.id = t.account_id)
WHERE cdr.datefield IN ('2014-03-31', '2013-03-31', '2012-03-31')
GROUP BY cdr.datefield, a.id
ORDER BY a.id ASC
Records:
transactions:
123, 2012-01-01, 10000, 100, 0
124, 2012-01-01, 30000, 100, 0
125, 2013-01-01, 20000, 200, 0
126, 2014-01-01, 20000, 0, 100
127, 2014-01-01, 10000, 0, 200
Desired Result:
date id name balance
-----------------------------------------------
2014-03-31 10000 Cash -200
2014-03-31 20000 Payables -100
2014-03-31 30000 Issued Capital 0
2013-03-31 10000 Cash 0
2013-03-31 20000 Payables 200
2013-03-31 30000 Issued Capital 0
2012-03-31 10000 Cash 100
2012-03-31 20000 Payables 0
2012-03-31 30000 Issued Capital 100
Tables:
calendar:
datefield (date)
transactions:
id (int), value_date (date), account_id, debit_amount (decimal), credit_amount (decimal)
accounts:
id (int), name (varchar), description (varchar)
I've already tried the following GROUP BY statements with no luck:
GROUP BY YEAR(cdr.datefield), MONTH(cdr.datefield), a.id
GROUP BY YEAR(cdr.datefield), a.id
GROUP BY YEAR(t.value_date), MONTH(t.value_date), a.id
GROUP BY YEAR(t.value_date), a.id
I'm thinking my problem has to do with the fact that I want to group by dates that not necessarily belong to the same year.
How can I modify my query to get the desired result?
Thanks!