
<p><a href="http://pojokprogrammer.net/tags/belajar-sql">Belajar SQL</a> | Semua programmer atau software developer aplikasi perkantoran <strong>pasti pernah menggunakan SQL</strong>. minimal untuk proses CRUD (Create, Read, Update, Delete) sederhana. Namun hanya sedikit yang memanfaatkan <strong>SQL untuk proses perhitungan rumit </strong>seperti penggajian, perhitungan average harga barang, status inventory, dan sebagainya hanya dengan menggunakan <strong>Single SQL Statement</strong>, alias dengan <strong>satu perintah SQL saja</strong>. 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.....</p>
<!--break-->
<pre class="brush:plain">
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
</pre>
<p>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 <a href="http://tkyte.blogspot.com/2006/10/slow-by-slow.html">artikel</a> di blog-nya</p>
<ol>
<li>
Sebisa mungkin, selalu gunakan <strong>satu perintah SQL</strong>.</li>
<li>
Jika tidak bisa dalam satu perintah SQL, maka gunakan Stored Procedure</li>
<li>
Jika tidak bisa menggunakan Stored Procedure, lakukan di aplikasi kita</li>
<li>
Jika masih tidak bisa juga, mungkin kita perlu memikirkan masak-masak, sebenarnya perlu gak sih kita lakukan hal ini? :)</li>
</ol>
<p>Untuk itu <strong>saya akan coba menyelesaikan </strong>problem di atas hanya menggunakan satu perintah SQL saja. Namun saya akan jelaskan langkah-demi-langkah dalam membangun <em><strong>"satu perintah SQL"</strong></em> tersebut, agar teman-teman bisa memahami algoritma dan logika yang ada di dalamnya. Untuk kasus ini saya akan menggunakan <strong><a href="http://pojokprogrammer.net/tags/postgresql">PostgreSQL</a></strong> untuk menyelesaikannya, namun hasil akhir adalah perintah SQL standar yang bisa digunakan di RDBMS lain. Mari kita mulai dengan membuka command line PostgreSQL ....................</p>
<pre class="brush:sql">
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.
</pre>
<p>Untuk menyelesaikan kasus di atas kita memerlukan beberapa buah table, yaitu</p>
<ul>
<li>
table items untuk menampung data master barang.</li>
<li>
table <strong>item_balances </strong>untuk menampung data saldo awal bulanan tiap-tiap barang.</li>
<li>
table <strong>transactions </strong>untuk mencatat transaksi harian, keluar dan masuk barang.</li>
</ul>
<p><strong>Pertama-tama</strong> kita buat struktur table <strong>items</strong>.</p>
<pre class="brush:sql">
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
</pre>
<p><strong>Kemudian </strong>isikan beberapa data menggunakan perintah <strong>insert into</strong>. dan tampilkan hasilnya untuk memastikan.</p>
<pre class="brush:sql">
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)
</pre>
<p><strong>Berikutnya </strong>kita siapkan table item_balances yang digunakan untuk menyimpan saldo awal dan harga barang setiap bulannya. untuk itu perlu field <strong>periode </strong>untuk identifikasi bulan.</p>
<pre class="brush:sql">
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
</pre>
<p><strong>Kemudian </strong>isikan beberapa data menggunakan perintah <strong>insert into</strong>. dan tampilkan hasilnya untuk memastikan.</p>
<pre class="brush:sql">
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)
</pre>
<p>Karena desain database kita sudah mengikuti kaidah <strong>normalisasi</strong>, maka yang tersimpan di table <strong>item_balances </strong>hanya <strong>id </strong>barang saja, Untuk mengetahui nama barang bersangkutan, kita gunakan <a href="http://pojokprogrammer.net/content/representasi-sql-join-secara-visual"><strong>klausa JOIN</strong></a> seperti di bawah ini...</p>
<pre class="brush:sql">
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)
</pre>
<p><strong>Sekarang </strong>buatkan table <strong>transactions </strong>untuk menampung data transaksi harian, meliputi transaksi masuk dan keluar. Untuk membedakan transaksi masuk atau keluar, kita sediakan satu field <strong>tx_type</strong> untuk membedakannya.</p>
<pre class="brush:sql">
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
</pre>
<p><strong>Kemudian </strong>isikan beberapa data menggunakan perintah <strong>insert into</strong>. dan tampilkan hasilnya untuk memastikan. Kita tambahkan klausa JOIN untuk mengetahui informasi lebih jelas tentang barang yang terlibat dalam transaksi yang kita masukkan</p>
<pre class="brush:sql">
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)
</pre>
<p><strong>Langkah berikutnya </strong>adalah membuat query untuk emnghitung summary transaksi. Kita akan menggunakan <a href="http://www.phpindonesia.net/artikel/query-crosstab-di-mysql"><strong>teknik crosstab</strong></a> untuk memindahkan row transaksi IN dan OUT menjadi kolom.</p>
<pre class="brush:sql">
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)
</pre>
<p><strong>Terakhir</strong>...... lakukan JOIN terhadal table items, item_balance dan hasil crosstab transactions untuk proses perhitungan sesuai dengan rumus yang diberikan di bagian awal artikel ini.</p>
<pre class="brush:sql">
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)
</pre>
<p><em><strong>BERHASIL!!!!!!</strong></em></p>
<p>Kita berhasil membuat <strong>satu perintah SQL </strong>untuk perhitungan status stok akhir bulan dan menghitung harga rata-ratanya. hasil dari perintah SQL ini sudah <em><strong>matang</strong></em>, sehingga aplikasi kita - PHP atau VB.net atau lainnya - bisa langsung menampilkan hasilnya tanpa perlu melakukan proses perhitungan lagi........</p>
<p>Semoga berguna....</p>
<p>^_^</p>
<p> </p>
- Log in to post comments
- 45079 kali dilihat