Belajar SQL: Perhitungan Stok dan Harga Rata-Rata Barang untuk Aplikasi Inventory
Belajar SQL | Semua programmer atau software developer aplikasi perkantoranpasti pernah menggunakan SQL. minimal untuk proses CRUD (Create, Read, Update, Delete) sederhana. Namun hanya sedikit yang memanfaatkan SQL untuk proses perhitungan rumitseperti penggajian, perhitungan average harga barang, status inventory, dan sebagainya hanya dengan menggunakan Single SQL Statement, alias dengan satu perintah SQL saja. Beberapa waktu lalu saat diskusi online dengan kawan-kawan di Facebook, saya tergelitik untuk membuat contoh SQL beraksi untuk menghitung nilai stok dan harga rata-rata barang setelah terjadi proses keluar masuk barang pada bulan tertentu. Contoh kasusnya adalah seperti ini.....
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
| Di Ketahui ;QOB := 1.670,28 Kg ( Opening Balance )vOB := Rp. 8.397.476,7 ( Value Opening Balance Akumulasi selama 1 bulan dari pembelian ( Harus Proses seluruh transaksi masuk )SQB := 1.600,Kg ( Supply quantity Barang )vSQB := Rp. 8.825.600 ( Value Quantity Barang )Akumulasi pemakain material selam 1 bulan ( Harus Proses seluruh Transaksi Keluar )uQB := 973,83 ( Used Quantity Balance )Di Tanya ;vuQB : …..?? ( Value used Quatity Balance )SAB : …… ( Saldo akhir balance )vSAB : ……..( Value saldo akhir balance )Jawab ;Average Bahan Baku := (( vSQB+vOB) / (QOB+SQB) )vuQB := uQB * average bahan baku SAB := (( QOB+SQB)-UQB)vSAB := SAB * average bahan baku |
Ketika kita berinteraksi dengan database, maka ada Golden Rule yang harus selalu dipegang. Golden Rule ini disampaikan oleh Tom Kyte, Evangelist Oracle, dalam salah satuartikel di blog-nya
- Sebisa mungkin, selalu gunakan satu perintah SQL.
- Jika tidak bisa dalam satu perintah SQL, maka gunakan Stored Procedure
- Jika tidak bisa menggunakan Stored Procedure, lakukan di aplikasi kita
- Jika masih tidak bisa juga, mungkin kita perlu memikirkan masak-masak, sebenarnya perlu gak sih kita lakukan hal ini? :)
Untuk itu saya akan coba menyelesaikan problem di atas hanya menggunakan satu perintah SQL saja. Namun saya akan jelaskan langkah-demi-langkah dalam membangun"satu perintah SQL" tersebut, agar teman-teman bisa memahami algoritma dan logika yang ada di dalamnya. Untuk kasus ini saya akan menggunakan PostgreSQL untuk menyelesaikannya, namun hasil akhir adalah perintah SQL standar yang bisa digunakan di RDBMS lain. Mari kita mulai dengan membuka command line PostgreSQL ....................
1
2
3
4
5
6
| C:\Program Files\PostgreSQL\9.3\bin>psql -U postgres -d inventorypsql (9.3.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. |
Untuk menyelesaikan kasus di atas kita memerlukan beberapa buah table, yaitu
- table items untuk menampung data master barang.
- table item_balances untuk menampung data saldo awal bulanan tiap-tiap barang.
- table transactions untuk mencatat transaksi harian, keluar dan masuk barang.
Pertama-tama kita buat struktur table items.
1
2
3
4
5
6
7
8
9
| inventory=# -- -----------------------inventory=# -- buat table itemsinventory=# -- -----------------------inventory=# create table items (inventory(# id serial primary key,inventory(# code varchar(20) not null,inventory(# name varchar(200) nullinventory(# ) ;CREATE TABLE |
Kemudian isikan beberapa data menggunakan perintah insert into. dan tampilkan hasilnya untuk memastikan.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
| inventory=# -- -----------------------inventory=# -- masukkan beberapa datainventory=# -- -----------------------inventory=# insert into items (code, name)inventory-# select 'ITEM01', 'Barang Pertama' union allinventory-# select 'ITEM02', 'Barang Kedua' union allinventory-# select 'ITEM03', 'Barang Ketiga'inventory-# ;INSERT 0 3inventory=# -- -----------------------inventory=# -- lihat hasilnyainventory=# -- -----------------------inventory=# select * from items inventory-# ; id | code | name----+--------+---------------- 1 | ITEM01 | Barang Pertama 2 | ITEM02 | Barang Kedua 3 | ITEM03 | Barang Ketiga(3 rows) |
Berikutnya kita siapkan table item_balances yang digunakan untuk menyimpan saldo awal dan harga barang setiap bulannya. untuk itu perlu field periode untuk identifikasi bulan.
1
2
3
4
5
6
7
8
9
10
11
12
| inventory=# -- -----------------------inventory=# -- buat table balanceinventory=# -- -----------------------inventory=# create table item_balances (inventory(# id serial primary key,inventory(# item_id integer not null,inventory(# period date not null,inventory(# quantity numeric(15,2),inventory(# unit_price numeric(15,2),inventory(# foreign key (item_id) references items(id)inventory(# ) ;CREATE TABLE |
Kemudian isikan beberapa data menggunakan perintah insert into. dan tampilkan hasilnya untuk memastikan.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
| inventory=# -- -----------------------inventory=# -- masukkan beberapa datainventory=# -- -----------------------inventory=# insert into item_balances (item_id, period, quantity, unit_price)inventory-# select 1, to_date('2013-12-01','YYYY-MM-DD'), 1600, 23125 union allinventory-# select 2, to_date('2013-12-01','YYYY-MM-DD'), 1250, 13100 union allinventory-# select 3, to_date('2013-12-01','YYYY-MM-DD'), 3250, 3100 inventory-# ;INSERT 0 3inventory=# -- -----------------------inventory=# -- lihat hasilnyainventory=# -- -----------------------inventory=# select * from item_balances inventory-# ; id | item_id | period | quantity | unit_price----+---------+------------+----------+------------ 1 | 1 | 2013-12-01 | 1600.00 | 23125.00 2 | 2 | 2013-12-01 | 1250.00 | 13100.00 3 | 3 | 2013-12-01 | 3250.00 | 3100.00(3 rows) |
Karena desain database kita sudah mengikuti kaidah normalisasi, maka yang tersimpan di table item_balances hanya id barang saja, Untuk mengetahui nama barang bersangkutan, kita gunakan klausa JOIN seperti di bawah ini...
1
2
3
4
5
6
7
8
9
10
11
12
13
14
| inventory=# -- -----------------------inventory=# -- tambahkan klausa JOINinventory=# -- -----------------------inventory=# select a.*, b.period, b.quantity, b.unit_priceinventory-# , b.quantity*b.unit_price as item_valueinventory-# from items a join item_balances b on a.id=b.item_id inventory-# ; id | code | name | period | quantity | unit_price | item_value----+--------+----------------+------------+----------+------------+--------------- 1 | ITEM01 | Barang Pertama | 2013-12-01 | 1600.00 | 23125.00 | 37000000.00 2 | ITEM02 | Barang Kedua | 2013-12-01 | 1250.00 | 13100.00 | 16375000.00 3 | ITEM03 | Barang Ketiga | 2013-12-01 | 3250.00 | 3100.00 | 10075000.00(3 rows) |
Sekarang buatkan table transactions untuk menampung data transaksi harian, meliputi transaksi masuk dan keluar. Untuk membedakan transaksi masuk atau keluar, kita sediakan satu field tx_type untuk membedakannya.
1
2
3
4
5
6
7
8
9
10
11
12
13
| inventory=# -- -----------------------inventory=# -- buat table transaksiinventory=# -- -----------------------inventory=# create table transactions (inventory(# id serial primary key,inventory(# item_id integer not null,inventory(# tx_date date not null,inventory(# tx_type varchar(10) not null,inventory(# quantity numeric(15,2),inventory(# unit_price numeric(15,2),inventory(# foreign key (item_id) references items(id)inventory(# ) ;CREATE TABLE |
Kemudian isikan beberapa data menggunakan perintah insert into. dan tampilkan hasilnya untuk memastikan. Kita tambahkan klausa JOIN untuk mengetahui informasi lebih jelas tentang barang yang terlibat dalam transaksi yang kita masukkan
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
| inventory=# -- -----------------------inventory=# -- masukkan beberapa datainventory=# -- -----------------------inventory=# insert into transactions (item_id, tx_date, tx_type, quantity, unit_price)inventory-# select 1, to_date('2013-12-10','YYYY-MM-DD'), 'IN', 100, 24125 union allinventory-# select 1, to_date('2013-12-20','YYYY-MM-DD'), 'IN', 200, 25125 union allinventory-# select 1, to_date('2013-12-05','YYYY-MM-DD'), 'OUT', 100, 0 union allinventory-# select 1, to_date('2013-12-08','YYYY-MM-DD'), 'OUT', 200, 0 union allinventory-# select 1, to_date('2013-12-18','YYYY-MM-DD'), 'OUT', 400, 0 union allinventory-# select 2, to_date('2013-12-02','YYYY-MM-DD'), 'IN', 1000, 15125 union allinventory-# select 2, to_date('2013-12-23','YYYY-MM-DD'), 'IN', 2000, 14100 union allinventory-# select 2, to_date('2013-12-04','YYYY-MM-DD'), 'OUT', 1100, 0 union allinventory-# select 2, to_date('2013-12-05','YYYY-MM-DD'), 'OUT', 1200, 0 union allinventory-# select 2, to_date('2013-12-15','YYYY-MM-DD'), 'OUT', 400, 0 union allinventory-# select 3, to_date('2013-12-12','YYYY-MM-DD'), 'IN', 1000, 3500 union allinventory-# select 3, to_date('2013-12-24','YYYY-MM-DD'), 'IN', 2000, 3700 union allinventory-# select 3, to_date('2013-12-04','YYYY-MM-DD'), 'OUT', 1100, 0 union allinventory-# select 3, to_date('2013-12-15','YYYY-MM-DD'), 'OUT', 1200, 0 union allinventory-# select 3, to_date('2013-12-25','YYYY-MM-DD'), 'OUT', 2400, 0 inventory-# ;INSERT 0 15inventory=# -- -----------------------inventory=# -- lihat hasilnya, pakai JOINinventory=# -- -----------------------inventory=# select a.*, b.code, b.nameinventory-# from transactions ainventory-# join items b on a.item_id=b.idinventory-# order by tx_date, tx_type inventory-# ; id | item_id | tx_date | tx_type | quantity | unit_price | code | name----+---------+------------+---------+----------+------------+--------+---------------- 6 | 2 | 2013-12-02 | IN | 1000.00 | 15125.00 | ITEM02 | Barang Kedua 8 | 2 | 2013-12-04 | OUT | 1100.00 | 0.00 | ITEM02 | Barang Kedua 13 | 3 | 2013-12-04 | OUT | 1100.00 | 0.00 | ITEM03 | Barang Ketiga 3 | 1 | 2013-12-05 | OUT | 100.00 | 0.00 | ITEM01 | Barang Pertama 9 | 2 | 2013-12-05 | OUT | 1200.00 | 0.00 | ITEM02 | Barang Kedua 4 | 1 | 2013-12-08 | OUT | 200.00 | 0.00 | ITEM01 | Barang Pertama 1 | 1 | 2013-12-10 | IN | 100.00 | 24125.00 | ITEM01 | Barang Pertama 11 | 3 | 2013-12-12 | IN | 1000.00 | 3500.00 | ITEM03 | Barang Ketiga 14 | 3 | 2013-12-15 | OUT | 1200.00 | 0.00 | ITEM03 | Barang Ketiga 10 | 2 | 2013-12-15 | OUT | 400.00 | 0.00 | ITEM02 | Barang Kedua 5 | 1 | 2013-12-18 | OUT | 400.00 | 0.00 | ITEM01 | Barang Pertama 2 | 1 | 2013-12-20 | IN | 200.00 | 25125.00 | ITEM01 | Barang Pertama 7 | 2 | 2013-12-23 | IN | 2000.00 | 14100.00 | ITEM02 | Barang Kedua 12 | 3 | 2013-12-24 | IN | 2000.00 | 3700.00 | ITEM03 | Barang Ketiga 15 | 3 | 2013-12-25 | OUT | 2400.00 | 0.00 | ITEM03 | Barang Ketiga(15 rows) |
Langkah berikutnya adalah membuat query untuk emnghitung summary transaksi. Kita akan menggunakan teknik crosstab untuk memindahkan row transaksi IN dan OUT menjadi kolom.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
| inventory=# -- -----------------------inventory=# -- crosstab data transaksiinventory=# -- -----------------------inventory=# select item_idinventory-# , date_trunc('month',tx_date) tx_dateinventory-# , sum(case when tx_type='IN' then quantity else 0 end) qty_ininventory-# , sum(case when tx_type='IN' then quantity*unit_price else 0 end) value_ininventory-# , sum(case when tx_type='OUT' then quantity else 0 end) qty_outinventory-# from transactionsinventory-# group by item_idinventory-# , date_trunc('month',tx_date) ; item_id | tx_date | qty_in | value_in | qty_out---------+------------------------+---------+---------------+--------- 2 | 2013-12-01 00:00:00+07 | 3000.00 | 43325000.0000 | 2700.00 1 | 2013-12-01 00:00:00+07 | 300.00 | 7437500.0000 | 700.00 3 | 2013-12-01 00:00:00+07 | 3000.00 | 10900000.0000 | 4700.00(3 rows) |
Terakhir...... lakukan JOIN terhadal table items, item_balance dan hasil crosstab transactions untuk proses perhitungan sesuai dengan rumus yang diberikan di bagian awal artikel ini.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
| inventory=# -- -----------------------inventory=# -- perhitungan akhirinventory=# -- -----------------------inventory=# select it.id, it.code, it.nameinventory-# , bl.period, bl.quantity QOB, bl.unit_price Avg1inventory-# , ( bl.quantity *bl.unit_price ) vOBinventory-# , tx.qty_in SQB, tx.value_in vSQB, tx.qty_out uQBinventory-# , ( (tx.value_in +bl.quantity *bl.unit_price) / (bl.quantity +tx.qty_in) ) Avg2inventory-# , tx.qty_outinventory-# * ( (tx.value_in+bl.quantity*bl.unit_price) / (bl.quantity +tx.qty_in) ) vuQBinventory-# , ( bl.quantity +tx.qty_in ) -tx.qty_out SABinventory-# , ( (bl.quantity +tx.qty_in) -tx.qty_out )inventory-# * ( (tx.value_in +bl.quantity *bl.unit_price) / (bl.quantity +tx.qty_in) ) vSABinventory-# from items itinventory-# join item_balances bl on it.id = bl.item_idinventory-# left join (inventory(# select item_idinventory(# , date_trunc('month',tx_date) tx_dateinventory(# , sum(case when tx_type='IN' then quantity else 0 end) qty_ininventory(# , sum(case when tx_type='IN' then quantity*unit_price else 0 end) value_ininventory(# , sum(case when tx_type='OUT' then quantity else 0 end) qty_outinventory(# from transactionsinventory(# group by item_idinventory(# , date_trunc('month',tx_date)inventory(# ) txinventory-# on bl.item_id = tx.item_idinventory-# and bl.period = tx.tx_dateinventory-# ; id | code | name | period | qob | avg1 | vob | sqb | vsqb | uqb | avg2 | vuqb | sab | vsab----+--------+----------------+------------+---------+----------+-------------+---------+-------------+---------+----------+-------------+---------+------------- 1 | ITEM01 | Barang Pertama | 2013-12-01 | 1600.00 | 23125.00 | 37000000.00 | 300.00 | 7437500.00 | 700.00 | 23388.16 | 16371712.00 | 1200.00 | 28065792.00 2 | ITEM02 | Barang Kedua | 2013-12-01 | 1250.00 | 13100.00 | 16375000.00 | 3000.00 | 43325000.00 | 2700.00 | 14047.06 | 37927062.00 | 1550.00 | 21772943.00 3 | ITEM03 | Barang Ketiga | 2013-12-01 | 3250.00 | 3100.00 | 10075000.00 | 3000.00 | 10900000.00 | 4700.00 | 3356.00 | 15773200.00 | 1550.00 | 5201800.00(3 rows) |
BERHASIL!!!!!!
Kita berhasil membuat satu perintah SQL untuk perhitungan status stok akhir bulan dan menghitung harga rata-ratanya. hasil dari perintah SQL ini sudah matang, sehingga aplikasi kita - PHP atau VB.net atau lainnya - bisa langsung menampilkan hasilnya tanpa perlu melakukan proses perhitungan lagi........
Semoga berguna....
^_^
No comments:
Post a Comment