Bookkeeping Rate Daily

4 min read

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:

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

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

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.

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.

Eligible Blog

Technology, Go games talk and Project Update

Bookkeeping Rate Daily

Share this: