Membuat Function Holiday Melalui SQL

Oleh: Erwin Sugiawan


Seringkali kita mendapat pertanyaan dari bos ataupun client, mengenai hal2 tentang manipulasi tanggal. Berikut dibawah ini saya mencoba membuat fungsi2 tanggal melalu sql :

1. membuat manipulasi tanggal dengan cara melakukan penambahan/pengurangan beberapa hari dengan memperhitungan hari libur nasional.

a. kita wajib mendefinisikan table holiday (tblDateHoliday ), yang berisi field2 sbb

CREATE TABLE [tblDateHoliday] (
[CountryCode] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[YR] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Holiday] [datetime] NOT NULL ,
[Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastUpdate] [datetime] NULL CONSTRAINT [DF_tblDateHoliday_LastUpdate] DEFAULT (getdate()),
[OperatorID] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_tblDateHoliday] PRIMARY KEY NONCLUSTERED
(
[CountryCode],
[YR],
[Holiday]
) ON [PRIMARY]
) ON [PRIMARY]

lalu masukan data2 dibawah ini
- CountryCode YR Holiday Description LastUpdate OperatorID
IDR 2008 10/1/2008 Tahun Baru Hidriyah 12/27/2007 9:22:47 AM Erwin
IDR 2008 11/1/2008 Cuti Bersama 12/27/2007 9:22:47 AM Erwin



b. membuat function fnDateHoliday,seperti dibawah ini

Create Function fnDateHoliday (
@pdate datetime,
@DateAddDays integer,
@Country varchar(3),
@SignPlusMinus char(1) ) Returns Datetime
-- with encryption
as
Begin
Declare @vDayWork Int
Declare @vDayCount Int
Declare @NewDateDay datetime
Declare @hitung int

Set @vDayWork = 0
Set @vDayCount= 0
Set @hitung= 0
Set @NewDateDay = @pDate

While @vDayCount < @DateAddDays
Begin
if @SignPlusMinus='+'
select @vDayWork = @vDayWork + 1
else
select @vDayWork = @vDayWork - 1

Select @NewDateDay = @pDate + @vDayWork

-- 6=hari sabtu
if DATEPART(dw, @NewDateDay)-1 = 6
Begin
Goto CSDLoop
End
-- 0=hari Minggu
If DATEPART(dw, @NewDateDay)-1 = 0
Begin
Goto CSDLoop
End

Select @hitung=Count(Holiday)
From NamaDatabase.dbo.tblDateHoliday
where (holiday=@NewDateDay and CountryCode=@Country )

If @hitung <> 0
Goto CSDLoop
else
Select @vDayCount = @vDayCount+1

CSDLoop:
continue
End

return (@NewDateDay)
End


c. cara pengunaan nya

- melalui SQL Query Analyzer
select NamaDatabase.dbo.fnDateHoliday ('9-Jan-2008',1,'IDR','+')
tekan F5
lalu akan muncul hasil 2008-01-14 00:00:00.000

kenapa muncul tanggal 14 ? karena dalam tabel holiday sudah saya definisikan bawah tanggal 10 dan 11 Jan 2008 adalah hari libur serta tanggal 12 dan 13 jan 2008 adalah hari sabtu dan minggu yang oleh function fnDateHoliday telah di definisikan kalo hari sabtu minggu tidak di hitung.


- melalui Module Ms.Access
karena function di SQL hanya menghasil sebuah nilai dan nilai tersebut tidak bisa diambil secara langsung oleh Ms.Access, maka kita buat sebuah Store Procedure

Create PROCEDURE spGetHoliday

@pdate datetime,
@DateAddDays integer,
@Country varchar(3),
@SignPlusMinus char(1)

-- WITH ENCRYPTION
AS
Select NamaDatabase.dbo.fnDateHoliday
(@pdate,@DateAddDays,@Country,@SignPlusMinus ) as GetCurDate

return


lalu copy paste function dibawah dalam module Ms.Access nya


Function FnHoliday()
Dim rst As New ADODB.Recordset
Set rst = Nothing
rst.CursorLocation = adUseServer
rst.Open "dbo.spGetHoliday '9-Jan-2008','" & 3 & "','IDR','-' ", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
FnHoliday = rst!newDate
Set rst = Nothing

End Sub

terakhir buat sebuah form, yang berisi textbox dan command button, dalam event on click command button di kasih perintah sebagai berikut :

txtDate=fnHoliday


silahkan bila anda ingin melakukan perubahan agar semakin baik dan sesuai dgn kebutuhan anda

Tidak ada komentar: