· 7 years ago · Sep 05, 2018, 06:56 AM
1DROP TABLE IF EXISTS stok;
2CREATE TABLE IF NOT EXISTS stok (
3 id smallint(4) unsigned NOT NULL AUTO_INCREMENT,
4 item varchar(50) NOT NULL,
5 periode DATE,
6 jumlah int(11) unsigned NOT NULL,
7 PRIMARY KEY (id)
8) ENGINE=MyISAM;
9
10INSERT INTO stok(item,periode,jumlah)
11VALUES
12('garam','2016-12-31',100),
13('garam','2017-12-31',200),
14('kecap','2015-10-15',200),
15('kecap','2016-10-15',450),
16('sambel','2016-09-07',300),
17('sambel','2017-09-07',300),
18('sambel','2018-09-07',500);
19
20SELECT * FROM stok;
21
22+----+--------+------------+--------+
23| id | item | periode | jumlah |
24+----+--------+------------+--------+
25| 1 | garam | 2016-12-31 | 100 |
26| 2 | garam | 2017-12-31 | 200 |
27| 3 | kecap | 2015-10-15 | 200 |
28| 4 | kecap | 2016-10-15 | 450 |
29| 5 | sambel | 2016-09-07 | 300 |
30| 6 | sambel | 2017-09-07 | 300 |
31| 7 | sambel | 2018-09-07 | 500 |
32+----+--------+------------+--------+
33
34SELECT
35a.periode,
36IF(@prev_item=a.item, a.jumlah - @prev_jumlah,0) AS increseament,
37@prev_jumlah:=a.jumlah AS jumlah,
38@prev_item:=a.item AS item
39FROM
40stok a, (SELECT @prev_item:=0, @prev_jumlah:=0) b
41ORDER BY a.item,a.periode;
42
43+------------+--------------+--------+--------+
44| periode | increseament | jumlah | item |
45+------------+--------------+--------+--------+
46| 2016-12-31 | 0 | 100 | garam |
47| 2017-12-31 | 100 | 200 | garam |
48| 2015-10-15 | 0 | 200 | kecap |
49| 2016-10-15 | 250 | 450 | kecap |
50| 2016-09-07 | 0 | 300 | sambel |
51| 2017-09-07 | 0 | 300 | sambel |
52| 2018-09-07 | 200 | 500 | sambel |
53+------------+--------------+--------+--------+
54
55SELECT
56a.periode,
57CASE WHEN @prev_item=a.item THEN a.jumlah - @prev_jumlah ELSE 0 END AS increseament,
58@prev_jumlah:=a.jumlah AS jumlah,
59@prev_item:=a.item AS item
60FROM
61stok a, (SELECT @prev_item:=0, @prev_jumlah:=0) b
62ORDER BY a.item,a.periode;