Output Hitung Stok PostrgreSQL

output.sql

psql (9.1.1)
WARNING: Console code page (850) differs from Windows code page (1252)
         8-bit characters might not work correctly. See psql reference
         page "Notes for Windows users" for details.
Type "help" for help.

sample=# -- data barang masuk;
sample=# select * from barang_masuk;

 id | item_id |  tanggal   | quantity
----+---------+------------+----------
  1 |       1 | 2012-05-01 |       50
  2 |       1 | 2012-05-05 |       50
(2 rows)


sample=# -- data barang keluar;
sample=# select * from barang_keluar;

 id | item_id |  tanggal   | quantity
----+---------+------------+----------
  1 |       1 | 2012-05-01 |       10
  2 |       1 | 2012-05-02 |       10
  3 |       1 | 2012-05-03 |       20
  4 |       1 | 2012-05-05 |       25
  5 |       1 | 2012-05-10 |       25
(5 rows)


sample=# -- sekarang hitung saldo harian stok;
sample=# select id, item_id, tanggal, quantity, jenis
sample-# , sum(quantity) over(partition by item_id
sample(#        order by item_id, tanggal, jenis
sample(#        rows between unbounded preceding and current row) saldo
sample-# from (
sample(#   select id, item_id, tanggal, quantity, 'IN' jenis
sample(#   from barang_masuk
sample(#   union all
sample(#   select id, item_id, tanggal, -quantity, 'OUT' jenis
sample(#   from barang_keluar
sample(# ) gabungan;

 id | item_id |  tanggal   | quantity | jenis | saldo
----+---------+------------+----------+-------+-------
  1 |       1 | 2012-05-01 |       50 | IN    |    50
  1 |       1 | 2012-05-01 |      -10 | OUT   |    40
  2 |       1 | 2012-05-02 |      -10 | OUT   |    30
  3 |       1 | 2012-05-03 |      -20 | OUT   |    10
  2 |       1 | 2012-05-05 |       50 | IN    |    60
  4 |       1 | 2012-05-05 |      -25 | OUT   |    35
  5 |       1 | 2012-05-10 |      -25 | OUT   |    10
(7 rows)