Advertisement
cahyadsn

subtract with previous record

Sep 5th, 2018
200
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
MySQL 1.94 KB | None | 0 0
  1. DROP TABLE IF EXISTS stok;
  2. CREATE 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.  
  10. INSERT INTO stok(item,periode,jumlah)
  11. VALUES
  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.  
  20. SELECT * 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.  
  34. SELECT
  35. a.periode,
  36. IF(@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
  39. FROM
  40. stok a, (SELECT @prev_item:=0, @prev_jumlah:=0) b
  41. ORDER 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.  
  55. SELECT
  56. a.periode,
  57. CASE 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
  60. FROM
  61. stok a, (SELECT @prev_item:=0, @prev_jumlah:=0) b
  62. ORDER BY a.item,a.periode;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement