Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- DROP TABLE IF EXISTS stok;
- CREATE TABLE IF NOT EXISTS stok (
- id smallint(4) unsigned NOT NULL AUTO_INCREMENT,
- item varchar(50) NOT NULL,
- periode DATE,
- jumlah int(11) unsigned NOT NULL,
- PRIMARY KEY (id)
- ) ENGINE=MyISAM;
- INSERT INTO stok(item,periode,jumlah)
- VALUES
- ('garam','2016-12-31',100),
- ('garam','2017-12-31',200),
- ('kecap','2015-10-15',200),
- ('kecap','2016-10-15',450),
- ('sambel','2016-09-07',300),
- ('sambel','2017-09-07',300),
- ('sambel','2018-09-07',500);
- SELECT * FROM stok;
- +----+--------+------------+--------+
- | id | item | periode | jumlah |
- +----+--------+------------+--------+
- | 1 | garam | 2016-12-31 | 100 |
- | 2 | garam | 2017-12-31 | 200 |
- | 3 | kecap | 2015-10-15 | 200 |
- | 4 | kecap | 2016-10-15 | 450 |
- | 5 | sambel | 2016-09-07 | 300 |
- | 6 | sambel | 2017-09-07 | 300 |
- | 7 | sambel | 2018-09-07 | 500 |
- +----+--------+------------+--------+
- SELECT
- a.periode,
- IF(@prev_item=a.item, a.jumlah - @prev_jumlah,0) AS increseament,
- @prev_jumlah:=a.jumlah AS jumlah,
- @prev_item:=a.item AS item
- FROM
- stok a, (SELECT @prev_item:=0, @prev_jumlah:=0) b
- ORDER BY a.item,a.periode;
- +------------+--------------+--------+--------+
- | periode | increseament | jumlah | item |
- +------------+--------------+--------+--------+
- | 2016-12-31 | 0 | 100 | garam |
- | 2017-12-31 | 100 | 200 | garam |
- | 2015-10-15 | 0 | 200 | kecap |
- | 2016-10-15 | 250 | 450 | kecap |
- | 2016-09-07 | 0 | 300 | sambel |
- | 2017-09-07 | 0 | 300 | sambel |
- | 2018-09-07 | 200 | 500 | sambel |
- +------------+--------------+--------+--------+
- SELECT
- a.periode,
- CASE WHEN @prev_item=a.item THEN a.jumlah - @prev_jumlah ELSE 0 END AS increseament,
- @prev_jumlah:=a.jumlah AS jumlah,
- @prev_item:=a.item AS item
- FROM
- stok a, (SELECT @prev_item:=0, @prev_jumlah:=0) b
- ORDER BY a.item,a.periode;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement