Belajar SQL | Semua programmer atau software developer aplikasi perkantoran pasti pernah menggunakan SQL. minimal untuk proses CRUD (Create, Read, Update, Delete) sederhana. Namun hanya sedikit yang memanfaatkan SQL untuk proses perhitungan rumit seperti 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.....
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 satu artikel 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 ....................
C:\Program Files\PostgreSQL\9.3\bin>psql -U postgres -d inventory
psql (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.
inventory=# -- ----------------------- inventory=# -- buat table items inventory=# -- ----------------------- inventory=# create table items ( inventory(# id serial primary key, inventory(# code varchar(20) not null, inventory(# name varchar(200) null inventory(# ) ; CREATE TABLE
Kemudian isikan beberapa data menggunakan perintah insert into. dan tampilkan hasilnya untuk memastikan.
inventory=# -- ----------------------- inventory=# -- masukkan beberapa data inventory=# -- ----------------------- inventory=# insert into items (code, name) inventory-# select 'ITEM01', 'Barang Pertama' union all inventory-# select 'ITEM02', 'Barang Kedua' union all inventory-# select 'ITEM03', 'Barang Ketiga' inventory-# ; INSERT 0 3 inventory=# -- ----------------------- inventory=# -- lihat hasilnya inventory=# -- ----------------------- 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.
inventory=# -- ----------------------- inventory=# -- buat table balance inventory=# -- ----------------------- 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.
inventory=# -- -----------------------
inventory=# -- masukkan beberapa data
inventory=# -- -----------------------
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 all
inventory-# select 2, to_date('2013-12-01','YYYY-MM-DD'), 1250, 13100 union all
inventory-# select 3, to_date('2013-12-01','YYYY-MM-DD'), 3250, 3100
inventory-# ;
INSERT 0 3
inventory=# -- -----------------------
inventory=# -- lihat hasilnya
inventory=# -- -----------------------
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...
inventory=# -- ----------------------- inventory=# -- tambahkan klausa JOIN inventory=# -- ----------------------- inventory=# select a.*, b.period, b.quantity, b.unit_price inventory-# , b.quantity*b.unit_price as item_value inventory-# 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.
inventory=# -- ----------------------- inventory=# -- buat table transaksi inventory=# -- ----------------------- 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
inventory=# -- -----------------------
inventory=# -- masukkan beberapa data
inventory=# -- -----------------------
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 all
inventory-# select 1, to_date('2013-12-20','YYYY-MM-DD'), 'IN', 200, 25125 union all
inventory-# select 1, to_date('2013-12-05','YYYY-MM-DD'), 'OUT', 100, 0 union all
inventory-# select 1, to_date('2013-12-08','YYYY-MM-DD'), 'OUT', 200, 0 union all
inventory-# select 1, to_date('2013-12-18','YYYY-MM-DD'), 'OUT', 400, 0 union all
inventory-# select 2, to_date('2013-12-02','YYYY-MM-DD'), 'IN', 1000, 15125 union all
inventory-# select 2, to_date('2013-12-23','YYYY-MM-DD'), 'IN', 2000, 14100 union all
inventory-# select 2, to_date('2013-12-04','YYYY-MM-DD'), 'OUT', 1100, 0 union all
inventory-# select 2, to_date('2013-12-05','YYYY-MM-DD'), 'OUT', 1200, 0 union all
inventory-# select 2, to_date('2013-12-15','YYYY-MM-DD'), 'OUT', 400, 0 union all
inventory-# select 3, to_date('2013-12-12','YYYY-MM-DD'), 'IN', 1000, 3500 union all
inventory-# select 3, to_date('2013-12-24','YYYY-MM-DD'), 'IN', 2000, 3700 union all
inventory-# select 3, to_date('2013-12-04','YYYY-MM-DD'), 'OUT', 1100, 0 union all
inventory-# select 3, to_date('2013-12-15','YYYY-MM-DD'), 'OUT', 1200, 0 union all
inventory-# select 3, to_date('2013-12-25','YYYY-MM-DD'), 'OUT', 2400, 0
inventory-# ;
INSERT 0 15
inventory=# -- -----------------------
inventory=# -- lihat hasilnya, pakai JOIN
inventory=# -- -----------------------
inventory=# select a.*, b.code, b.name
inventory-# from transactions a
inventory-# join items b on a.item_id=b.id
inventory-# 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.
inventory=# -- -----------------------
inventory=# -- crosstab data transaksi
inventory=# -- -----------------------
inventory=# select item_id
inventory-# , date_trunc('month',tx_date) tx_date
inventory-# , sum(case when tx_type='IN' then quantity else 0 end) qty_in
inventory-# , sum(case when tx_type='IN' then quantity*unit_price else 0 end) value_in
inventory-# , sum(case when tx_type='OUT' then quantity else 0 end) qty_out
inventory-# from transactions
inventory-# group by item_id
inventory-# , 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.
inventory=# -- -----------------------
inventory=# -- perhitungan akhir
inventory=# -- -----------------------
inventory=# select it.id, it.code, it.name
inventory-# , bl.period, bl.quantity QOB, bl.unit_price Avg1
inventory-# , ( bl.quantity *bl.unit_price ) vOB
inventory-# , tx.qty_in SQB, tx.value_in vSQB, tx.qty_out uQB
inventory-# , ( (tx.value_in +bl.quantity *bl.unit_price) / (bl.quantity +tx.qty_in) ) Avg2
inventory-# , tx.qty_out
inventory-# * ( (tx.value_in+bl.quantity*bl.unit_price) / (bl.quantity +tx.qty_in) ) vuQB
inventory-# , ( bl.quantity +tx.qty_in ) -tx.qty_out SAB
inventory-# , ( (bl.quantity +tx.qty_in) -tx.qty_out )
inventory-# * ( (tx.value_in +bl.quantity *bl.unit_price) / (bl.quantity +tx.qty_in) ) vSAB
inventory-# from items it
inventory-# join item_balances bl on it.id = bl.item_id
inventory-# left join (
inventory(# select item_id
inventory(# , date_trunc('month',tx_date) tx_date
inventory(# , sum(case when tx_type='IN' then quantity else 0 end) qty_in
inventory(# , sum(case when tx_type='IN' then quantity*unit_price else 0 end) value_in
inventory(# , sum(case when tx_type='OUT' then quantity else 0 end) qty_out
inventory(# from transactions
inventory(# group by item_id
inventory(# , date_trunc('month',tx_date)
inventory(# ) tx
inventory-# on bl.item_id = tx.item_id
inventory-# and bl.period = tx.tx_date
inventory-# ;
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....
^_^
- Log in to post comments
- 44623 kali dilihat