Firstly I'm a sql rookie and only know the basics. I have tried googling ( and have seen some references and example of similar but I was not able to follow or apply to my problem), I'm not even 100% if it is possible in my situation. I'm trying figure out how to perform a select that involves calculating a column based on a column from the pervious row. I want the balance from the pervious to be the carry in the next:
I have a list of transactions
_____________________________________________
| id | Date | time | amount | type |
| 1 |2014-01-01 | 12:00 | 2000 | payin |
| 5 |2014-01-01 | 17:00 | -20 | payout |
| 2 |2014-01-01 | 18:00 | -20 | payout |
| 3 |2014-01-02 | 12:00 | -10 | payout |
| 4 |2014-01-03 | 12:00 | +300 | payin |
As you can see there is an id ( which is the primary key ) but its order is not guaranteed because a transaction could be back dated and added after more recent transactions have been. I would like to get the following but I am completely lost. SQL is not my area at all.
| Date | time | carry | amount | balance | type |
|2014-01-01 | 12:00 | 0 | 2000 | 2000 | payin |
|2014-01-01 | 17:00 | 2000 | -20 | 1980 | payout |
|2014-01-01 | 17:00 | 1980 | -20 | 1960 | payout |
|2014-01-02 | 12:00 | 1960 | -10 | 1950 | payout |
|2014-01-03 | 12:00 | 1950 | -1 | 1949 | payout |
|2014-01-03 | 12:00 | 1949 | -5 | 1944 | payout |
|2014-01-03 | 12:00 | 1944 | +300 | 2244 | payin |
and in turn I believe I can get the following by summing based on type and group by date
| Date | carry | total in | total out | balance |
|2014-01-01 | 0 | 2000 | -40 | 1960 |
|2014-01-02 | 1960 | 0 | -10 | 1950 |
|2014-01-03 | 1950 | 300 | -6 | 2244 |
up till now I have been getting the desired result by querying the mysql data base from Nodejs for each date specific date using the following queries ( all performed separately ):
var sql_init_Balance_day ='select sum(amount) as total '+
'from transactions '+
'Where transaction_date<\''+mdate.format('YYYY-MM-DD')+'\'';
var sql_closing_Balance_day ='select sum(amount) as closingBalance '+
'from transactions '+
'Where transaction_date<\''+mtomorrow.format('YYYY-MM-DD')+'\'';
var sql_payins_day = 'select sum(amount) as deposits '+
'from transactions '+
'Where transaction_date=\''+mdate.format('YYYY-MM-DD')+'\''+
' and type=\'payin\'';
var sql_payouts_day = 'select sum(amount) as deductions '+
'from transactions '+
'Where transaction_date=\''+mdate.format('YYYY-MM-DD')+'\' '+
'and type=\'payout\'';
You can calculate the carry using variables
select `Date`, max(case when rowNum = 1 then carry end) carry,
sum(case when type = 'payin' then amount else 0 end) total_in,
sum(case when type = 'payout' then amount else 0 end) total_out,
sum(amount) balance
from (
select `Date`, time, @balance carry, amount,
(@balance := @balance + amount) balance, type,
@rowNum := IF(`Date` = @prevDate, @rowNum + 1, 1) rowNum,
@prevDate := `Date`
from transactions
cross join (select @balance := 0) t1
order by `Date`, time
) t1 group by `Date`
Update: Another query using a self join. Self joins are typically slower than using variables but it might be good enough for what you need.
select t1.date
coalesce(@prevBalance,0) carry,
sum(case when t2.type = 'payin' and t2.date = t1.date then t2.amount else 0 end) total_in,
sum(case when t2.type = 'payout' and t2.date = t1.date then t2.amount else 0 end) total_out,
sum(t2.amount) balance,
@prevBalance := sum(t2.amount)
from transactions t1
join transactions t2 on t2.date <= t1.date
group by t1.date
order by t1.date