How to get data by end date of every month of year?

Question

This is sample data in my table

id_item | qty | t_in | t_out | created_at
 1         5     1       0    2018-07-05 10:41:00
 1         5     1       0    2018-08-03 10:41:00
 1         5     0       1    2018-08-05 10:41:00
 1         5     1       0    2018-09-05 10:41:00
 1         5     1       0    2018-09-20 10:41:00
 1         5     0       1    2018-10-31 10:41:00

My expected result will be

id_item | qty | year | month
 1         5    2018   07
 1         5    2018   08
 1         15   2018   09
 1         10   2018   10

What i have tried it works, but not desired output when want to group by montly

$date = '2018-10-31'
$test = Model::whereDate('created_at','<=',$date)->select(DB::raw('(SUM(CASE T_IN WHEN 1 THEN qty ELSE qty * - 1 END)) as total'))->groupBy('id_item')->get();

Raw queries to get the quantity for one month

Select id_item, 
       (SUM(CASE T_IN WHEN 1 THEN qty ELSE qty * - 1 END)) as total 
from transactions 
where DATE(created_at) <= 2018-10-31 
group by id_item

Worst case

$last_day_of_month = [//list of last day of each month]
//then using loop to get qty of each month refer to the raw queries above

From the query above, i only able to get one line of record. I also tried to group by month and year but incorrect result caused of the date condition. How can i include multiple <= $date condition and group it accordingly to get desired output?

Any idea or is that possible to make it? Thanks.

Answer 1

It is a Rolling Sum problem. In newer versions of Mariadb/MySQL, it can be solved using Window Functions with Frames. However, you don't have that available.

We can rather solve this using user-defined variables. In a Derived table, we first determine the total change in qty for a month. Then, we use this result-set to calculate "final qty" at the end of a month, by adding up the previous month (row)'s qty with current month (row)'s qty_change.

I have also extended the query to consider the cases when there are more than one id_item values.

Try the following Raw query:

SELECT 
  @roll_qty := CASE WHEN @id_itm = dt.id_item 
                    THEN @roll_qty + dt.qty_change 
                    ELSE dt.qty_change  
               END AS qty, 
  @id_itm := dt.id_item AS id_item, 
  dt.year, 
  dt.month 
FROM 
(
 SELECT 
   t.id_item, 
   SUM(t.qty * t.t_in - t.qty * t.t_out) AS qty_change, 
   YEAR(t.created_at) AS `year`, 
   LPAD(MONTH(t.created_at), 2, '0') AS `month`
 FROM your_table AS t 
 GROUP BY t.id_item, `year`, `month`
 ORDER BY t.id_item, `year`, `month` 
) AS dt 
CROSS JOIN (SELECT @roll_qty := 0, 
                   @id_itm := 0
           ) AS user_init_vars;

| id_item | year | month | qty |
| ------- | ---- | ----- | --- |
| 1       | 2018 | 07    | 5   |
| 1       | 2018 | 08    | 5   |
| 1       | 2018 | 09    | 15  |
| 1       | 2018 | 10    | 10  |

View on DB Fiddle

Answer 2

If you are going to use variables, you need to do it correctly. MySQL does not guarantee the order of evaluation of expressions in a SELECT. So, a variable should not be assigned in one expression and then used in another.

This makes for complicated expressions, but it is possible:

select yyyy, mm, total,
       (@t := if(@ym = concat_ws('-', yyyy, mm), @t + total,
                 @ym := concat_ws('-', yyyy, mm), total
                )
       ) as running_total                 
from (select year(created_at) as yyyy, month(created_at) as mm,
             id_item, 
             sum(case T_IN when 1 then qty else - qty end) as total 
      from transactions 
      where created_at < '2018-11-01'
      group by id_item
      order by id_item, min(created_at)
     ) i cross join
     (select @ym := '', @n := 0);