0gptY6fa

1SELECT("jos_product.id, jos_product.product_code AS code, jos_product.name, 
2pc.id AS category_id, pc.name AS category_name, thumbnail, location, 
3original_image,CONCAT(sm.year, '/', sm.week) as week_start, 
4CONCAT(sm2.year, '/', sm2.week) AS reprint_week, pr.quantity AS reprint_qty,
5jos_stock.rem_balance AS balance")
6.joins("INNER JOIN jos_product_category AS pc ON pc.id = jos_product.product_category")
7.joins("INNER JOIN jos_stock_movement AS sm ON sm.id = jos_product.start_week")
8.joins("LEFT OUTER JOIN jos_stock ON jos_stock.product_id = jos_product.id")
9.joins("LEFT OUTER JOIN (select product, max(stock_movement) AS reprint, quantity from 
10jos_product_reprint group by product) AS pr ON pr.product IN (jos_product.id)")
11.joins("LEFT OUTER JOIN jos_stock_movement AS sm2 ON sm2.id = pr.reprint")
12.where("jos_product.published = 1 #{ search_query }")
13.order("jos_product.product_code ASC")
14.group("jos_product.product_code")
15	
16drop table if exists test_jos_stock;
17
18create table test_jos_stock
19(
20id int unsigned not null primary key auto_increment,
21rem_balance int not null default 0,
22year int not null default 0,
23week int not null default 0
24);
25
26insert into test_jos_stock (rem_balance,year,week) values (9,2012,23);
27insert into test_jos_stock (rem_balance,year,week) values (12,2012,24);
28insert into test_jos_stock (rem_balance,year,week) values (126,2012,25);
29insert into test_jos_stock (rem_balance,year,week) values (0,2012,26);
30
31select js.year,js.week,js.rem_balance,
32case when js.rem_balance = 0 then @prev_rem_balance else js.rem_balance end as rem_balance_zero_or_prev,
33@prev_rem_balance := js.rem_balance
34from test_jos_stock js
35inner join (SELECT @prev_rem_balance := 0) as t
36order by year,week;