Di Opera kita bisa melihat history bookkeeping rate di dalam menu cashiering
Data mentah tersebut dapat diambil dengan melakukan query berikut:
|
1 2 3 4 5 |
SELECT begin_date, ROUND(1/exchange_rate,4) as "RATE" FROM currency_exchange_rates WHERE exchange_rate_type='ROOM' AND currency_code='USD' ORDER BY begin_date |
Maka kita akan mendapatkan hasil:
| UPDATE_DATE | RATE |
|---|---|
| 12-20-2010 12:02:31 PM | 9000 |
| 1-14-2011 10:51:48 PM | 9000 |
| 02-01-2011 8:38:29 PM | 9000 |
| 02-01-2011 8:38:45 PM | 9000 |
| 02-01-2011 8:38:59 PM | 9000 |
| 2-21-2011 4:48:15 PM | 8850 |
| 3-15-2011 10:50:58 AM | 8526 |
| 3-15-2011 2:34:11 PM | 8800 |
| 04-06-2011 12:33:32 PM | 8600 |
| 9-14-2011 11:29:09 AM | 8700 |
tujuan yang ingin kita capai adalah menampilkan tabel per hari dan nilai bookkeeping rate nya dari yang paling lama sampai hingga hari ini, seperti tabel di bawah:
| UPDATE_DATE | RATE |
|---|---|
| 20-12-2010 | 9000 |
| 21-12-2010 | 9000 |
| 22-12-2010 | 9000 |
| ... | ... |
| ... | ... |
| 04-07-2018 | 14400 |
| 05-07-2018 | 14400 |
| 06-07-2018 | 14400 |
Langkah pertama adalah kita harus mendapatkan tanggal terlama di tabel
|
1 2 3 |
SELECT TRUNC(MIN(begin_date)) AS mindate FROM currency_exchange_rates WHERE exchange_rate_type='ROOM' AND currency_code='USD' |
setelah itu kita akan membuat table daftar tanggal dari tanggal terlama hingga tanggal hari ini
|
1 2 3 4 5 6 7 8 9 10 |
WITH mindate AS ( SELECT TRUNC(MIN(begin_date)) AS mindate FROM currency_exchange_rates WHERE exchange_rate_type='ROOM' AND currency_code='USD' ) ,dates AS ( SELECT mindate.mindate + row_number() OVER (ORDER BY 1) - 1 AS thedate FROM mindate, dual d CONNECT BY LEVEL <= FLOOR(SYSDATE - mindate.mindate) + 1 ) SELECT * FROM dates |
Sekarang part yang rada tricky kita akan mix and match dari daftar tanggal harian dengan harga bookkeeping, logikanya bisa kita buat dari range date tersebut masing-masing tanggal akan kita masukan flag tanggal terakhir harga bookkeeping rate, jika pada range tanggal terjadi set harga bookkeeping maka kita beri flag dengan tanggal yang sama.
Contohnya:
Pada tanggal 2010-12-20, harga bookkeeping rate diset IDR 9000 kemudian pada tanggal 2011-01-14, harga bookkeeping rate diset menjadi 9000
Maka:
- Tanggal 2010-12-20 diberi flag tanggal 2010-12-20
- dari tanggal 2010-12-21 hingga 2011-01-13 kita beri flag tanggal 2010-12-20
- Tanggal 2011-01-14 diberi flag tanggal 2011-01-14
dalam bentuk query seperti berikut:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
WITH mindate AS ( SELECT TRUNC(MIN(begin_date)) AS mindate FROM currency_exchange_rates WHERE exchange_rate_type='ROOM' AND currency_code='USD' ) ,dates AS ( SELECT mindate.mindate + row_number() OVER (ORDER BY 1) - 1 AS thedate FROM mindate, dual d CONNECT BY LEVEL <= FLOOR(SYSDATE - mindate.mindate) + 1 ) ,ranges AS ( SELECT thedate, (SELECT TRUNC(MAX(begin_date)) FROM currency_exchange_rates WHERE TRUNC(begin_date) <= thedate AND exchange_rate_type='ROOM' AND currency_code='USD') AS mindate FROM dates ) SELECT * FROM ranges |
Sekarang masalah yang muncul adalah ternyata di Opera terjadi duplikasi bookkeeping rate dimana pada tanggal yang sama terjadi beberapa settingan bookkeeping rate
| UPDATE_DATE | RATE |
|---|---|
| 02-01-2011 8:38:29 PM | 9000 |
| 02-01-2011 8:38:45 PM | 9000 |
| 02-01-2011 8:38:59 PM | 9000 |
| ... | ... |
| 3-15-2011 10:50:58 AM | 8526 |
| 3-15-2011 2:34:11 PM | 8800 |
Di mana pada tanggal 1 February 2011 dan 15 Maret 2011 terdapat duplikasi dengan jam yang berbeda sementara untuk mix and match dengan flag di tabel range yang sudah kita buat duplikasi tersebut akan terbawa dan itu tidak boleh terjadi. Secara best practice seharusnya hal ini tidak boleh terjadi karena Opera pun pada akhirnya akan mengambil perubahan terakhir pada hari itu dalam memposting room rate. Berarti kita harus “membersihkan” sampah di tabel Opera dengan cara jika ada duplikasi maka kita akan mengambil jam yang terakhir. Kita bisa lakukan dengan cara memberi flag.
|
1 2 3 4 5 |
SELECT begin_date, ROUND(1/EXCHANGE_RATE,4) AS "RATE", MAX(begin_date) OVER (PARTITION BY TRUNC(begin_date)) max_my_date FROM currency_exchange_rates WHERE exchange_rate_type='ROOM' AND CURRENCY_CODE='USD' |
Setelah berhasil melakukan flag maka kita filter dengan mengambil nilai flag sehingga tabel baru tidak ada duplikasi
|
1 2 3 4 5 6 7 8 9 10 |
SELECT begin_date, rate FROM ( SELECT begin_date, ROUND(1/EXCHANGE_RATE,4) AS "RATE", MAX(begin_date) OVER (PARTITION BY TRUNC(begin_date)) max_my_date FROM currency_exchange_rates WHERE exchange_rate_type='ROOM' AND CURRENCY_CODE='USD' ) WHERE begin_date = max_my_date ORDER BY begin_date |
Dengan demikian maka kita hanya tinggal menggabungkan tabel ranges yang sebelumnya sudah dibuat dengan tabel dari Opera yang sudah difilter dan bebas dari duplikasi. Hasil akhirnya adalah sebagai berikut:
|
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 |
WITH mindate AS ( SELECT TRUNC(MIN(begin_date)) AS mindate FROM currency_exchange_rates WHERE exchange_rate_type='ROOM' AND currency_code='USD' ) ,dates AS ( SELECT mindate.mindate + row_number() OVER (ORDER BY 1) - 1 AS thedate FROM mindate, dual d CONNECT BY LEVEL <= FLOOR(SYSDATE - mindate.mindate) + 1 ) ,ranges AS ( SELECT thedate, (SELECT TRUNC(MAX(begin_date)) FROM currency_exchange_rates WHERE TRUNC(begin_date) <= thedate AND exchange_rate_type='ROOM' AND currency_code='USD') AS mindate FROM dates ) SELECT TO_CHAR(r.thedate,'yyyy-mm-dd') AS "DATE", bkr.rate FROM ranges r JOIN ( SELECT begin_date, rate FROM ( SELECT begin_date, ROUND(1/EXCHANGE_RATE,4) AS "RATE", MAX(begin_date) OVER (PARTITION BY TRUNC(begin_date)) max_my_date FROM currency_exchange_rates WHERE exchange_rate_type='ROOM' AND CURRENCY_CODE='USD' ) WHERE begin_date = max_my_date ) bkr ON r.mindate = TRUNC(bkr.begin_date) ORDER BY r.thedate |
David B
Latest posts by David B (see all)
- XAMPP PHP 7.3 with Oracle 11g integration - January 24, 2019
- Will the mystery uncovered behind Satoshi? - December 1, 2018
- Being Satoshi Nakamoto - November 14, 2018