I'm having problem with showing data in my database. I have jos_stock table with field rem_balance. rem_balance have value 0, 126, 12, 9. In my website. rem_balance show it depends on the week i.e. year 2012 week 26 the value of the remaining balance is 0. It means 2012 week 25 = 126, 2012 week 24 = 12 and 2012 week 23 = 9.
All I want to do is if the value of the current week is which is 26 is equal to 0. It will show the previous balance which is 126. How do I do this? Any Suggestion? I'm using Ruby on Rails.
Here is my query in model:
SELECT("jos_product.id, jos_product.product_code AS code, jos_product.name,
pc.id AS category_id, pc.name AS category_name, thumbnail, location,
original_image,CONCAT(sm.year, '/', sm.week) as week_start,
CONCAT(sm2.year, '/', sm2.week) AS reprint_week, pr.quantity AS reprint_qty,
jos_stock.rem_balance AS balance")
.joins("INNER JOIN jos_product_category AS pc ON pc.id = jos_product.product_category")
.joins("INNER JOIN jos_stock_movement AS sm ON sm.id = jos_product.start_week")
.joins("LEFT OUTER JOIN jos_stock ON jos_stock.product_id = jos_product.id")
.joins("LEFT OUTER JOIN (select product, max(stock_movement) AS reprint, quantity from
jos_product_reprint group by product) AS pr ON pr.product IN (jos_product.id)")
.joins("LEFT OUTER JOIN jos_stock_movement AS sm2 ON sm2.id = pr.reprint")
.where("jos_product.published = 1 #{ search_query }")
.order("jos_product.product_code ASC")
.group("jos_product.product_code")
While I can't show you the exact query that will solve your problems (I don't have the DDL for all of your tables) I can show you a hypothetical example of how to retrieve the previous row's data. Here it is using the test data described in your question (I've assumed that where you wrote "i.e. year 2012 week 36 the value of the remaining balance is 0" you meant "i.e. year 2012 week 26 the value of the remaining balance is 0".
Hopefully you can work learn from it and work it into your Ruby on Rails query:
drop table if exists test_jos_stock;
create table test_jos_stock
(
id int unsigned not null primary key auto_increment,
rem_balance int not null default 0,
year int not null default 0,
week int not null default 0
);
insert into test_jos_stock (rem_balance,year,week) values (9,2012,23);
insert into test_jos_stock (rem_balance,year,week) values (12,2012,24);
insert into test_jos_stock (rem_balance,year,week) values (126,2012,25);
insert into test_jos_stock (rem_balance,year,week) values (0,2012,26);
select js.year,js.week,js.rem_balance,
case when js.rem_balance = 0 then @prev_rem_balance else js.rem_balance end as rem_balance_zero_or_prev,
@prev_rem_balance := js.rem_balance
from test_jos_stock js
inner join (SELECT @prev_rem_balance := 0) as t
order by year,week;