I have a table with transactions by wallet. Using data from this table I need to get information about balance on each wallet in each of month. The list of month must be defined from transactions dates from same table. So if first transaction in was at 2012-01-14 23:44:12 and last in current month (2015-10), I should have list of year-month like this:
2012-01
2012-02
2012-03
...
2015-10
To get list of available year-month combinations I use next subquery:
SELECT DISTINCT DATE_FORMAT( `created` , '%Y-%m' ) AS `d` FROM `transactions`
and it should be good enough, because I'm sure that there is at least one transaction each month (not for each wallet but in common).
What I need to achieve - have a list with combinations for each of available month + wallet_id + max balance for that month. If in some specific month was no transaction for wallet, I need take balance from previous month. So result data must looks like this:
Month | Wallet | Balance 2012-01 | 234 | 111.10 2012-02 | 234 | 45.29 2012-03 | 234 | 45.29 (no transaction in 2012-03, so take value from prev month) 2012-04 | 234 | 45.29 (no transaction in 2012-04, so take value from prev month) 2012-05 | 234 | 45.29 (no transaction in 2012-05, so take value from prev month) 2012-06 | 234 | 45.29 (no transaction in 2012-06, so take value from prev month) 2012-07 | 234 | 14.32 (new transaction in 2012-07, so calculate new value)
and I need have it for each of wallet id.
Table with transactions have next structure:
CREATE TABLE IF NOT EXISTS `transactions` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`wallet_id` int(11) NOT NULL,
`credit` decimal(7,2) NOT NULL DEFAULT '0.00',
`bonus` decimal(7,2) NOT NULL DEFAULT '0.00',
`created` datetime NOT NULL,
PRIMARY KEY (`id`),
KEY `wallet_id` (`wallet_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
So far I have problems even with getting list of all available year-month + wallet id + balance, where balance is NULL if there was no transaction for this wallet in this month. So now I want at least have list like this (comparing to table above, which I need to get eventually):
Month | Wallet | Balance 2012-01 | 234 | 111.10 2012-02 | 234 | 45.29 2012-03 | 234 | NULL 2012-04 | 234 | NULL 2012-05 | 234 | NULL 2012-06 | 234 | NULL 2012-07 | 234 | 14.32
if I'll have this one, next step is to get value from previous row if current have NULL balance.
My query looks like this so far:
SELECT dates.*, `t`.*
FROM (
SELECT DISTINCT DATE_FORMAT( `created` , '%Y-%m' ) AS `d`
FROM `transactions`
) AS `dates`
LEFT JOIN (
SELECT `wallet_id` ,
MAX( `credit` + `bonus` ) AS `balance` ,
DATE_FORMAT( `created` , '%Y-%m' ) AS `date`
FROM `transactions`
GROUP BY `wallet_id` , `date`
) AS `t` ON ( `t`.`date` = `dates`.`d` )
ORDER BY `t`.`wallet_id`, `t`.`date`
But now all months where was no transaction for wallet are missed in result. So I have something like this:
Month | Wallet | Balance 2012-01 | 234 | 111.10 2012-02 | 234 | 45.29 2012-07 | 234 | 14.32
How to modify query to get list in the view I need?
And even better, how to achieve my final goal, where will be all months and NULL balance will be replaced with value from previous row?
And important point - I'm going to use result of query as kind of view (indeed derived table in BI tool).
