Di Opera kita bisa melihat history bookkeeping rate di dalam menu cashiering

Data mentah tersebut dapat diambil dengan melakukan query berikut:

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:

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:

Langkah pertama adalah kita harus mendapatkan tanggal terlama di tabel

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

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:

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

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.

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

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:

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

• Author
• Recent Posts

David B

Hi! I am the admin of this blog and I love to watch and learn go even write about it and love to share my programming knowledge.

Latest posts by David B (see all)

• Fix Error 403 WordPress unable to publish or edit a post - December 23, 2020
• Workflow Automation with n8n | BTC Price Alert - December 22, 2020
• Workflow Automation with n8n | Installation - December 22, 2020