Fungsi SQL untuk mendapatkan jumlah efektif jam kerja antara dua tanggal


Berikut adalah fungsi MS SQL untuk mendapatkan jumlah efektif jam kerja antara dua tanggal. Jam efektif ini dihitung hanya sesuai jam kerja dikurangi dengan waktu istirahat. Fungsi serupa dapat kita gunakan terutama untuk evaluasi waktu kerja (SLA, misalnya). Script di bawah telah diuji pada pada SQL Server 2012, namun harusnya dapat juga digunakan pada SQL Server 2005 ke atas karena pada dasarnya hanya menggunakan fungsi penganggalan yang telah umum.

Untuk mempermudah pemahaman, sekaligus memberikan kemungkinan penggunaan yang lebih luas, fungsi ini dibagi menjadi dua. Fungsi pertama adalah fungsi yang menghitung jumlah jam efektif kerja antar dua tanggal, sebagai berikut:

-- =============================================
-- Author:      Baran Kaynak (modified by Kodak 2012-04-18) (Modified by Blue Spy 2014-08-26)
-- Create date: 14.03.2011
-- Description: 09:30 ile 17:30 arasındaki iş saatlerini hafta sonlarını almayarak toplar.
-- =============================================
CREATE FUNCTION [dbo].[EtoWorkTime] 
(
    @StartDate DATETIME,
    @FinishDate DATETIME
)
RETURNS BIGINT
AS
BEGIN
	-- Lets hardcode the time values for a while

    DECLARE @Temp BIGINT
    SET @Temp=0

    DECLARE @FirstDay DATE
    SET @FirstDay = CONVERT(DATE, @StartDate, 112)

    DECLARE @LastDay DATE
    SET @LastDay = CONVERT(DATE, @FinishDate, 112)

    DECLARE @StartTime TIME
    SET @StartTime = CONVERT(TIME, @StartDate)

    DECLARE @FinishTime TIME
    SET @FinishTime = CONVERT(TIME, @FinishDate)

    DECLARE @WorkStart TIME
    SET @WorkStart = '08:00'

    DECLARE @WorkFinish TIME
    SET @WorkFinish = '16:00'

	DECLARE @BreakStart TIME
	SET @BreakStart = '12:00'

	DECLARE @BreakFinish TIME
	SET @BreakFinish = '13:00'

	DECLARE @BreakTime BIGINT
	SET @BreakTime = DATEDIFF(MINUTE, @BreakStart, @BreakFinish)

    DECLARE @DailyWorkTime BIGINT
    SET @DailyWorkTime = DATEDIFF(MINUTE, @WorkStart, @WorkFinish) - @BreakTime

    IF (@StartTime<@WorkStart)
    BEGIN
        SET @StartTime = @WorkStart
    END
	IF (@StartTime > @BreakStart AND @StartTime < @BreakFinish)
	BEGIN
		SET @StartTime = @BreakFinish
	END
    IF (@FinishTime>@WorkFinish)
    BEGIN
        SET @FinishTime=@WorkFinish
    END
	IF (@FinishTime > @BreakStart AND @FinishTime < @BreakFinish)
	BEGIN
		SET @FinishTime = @BreakStart
	END

    DECLARE @CurrentDate DATE
    SET @CurrentDate = @FirstDay
    DECLARE @LastDate DATE
    SET @LastDate = @LastDay

    WHILE(@CurrentDate<=@LastDate)
    BEGIN
		--If it is not sunday, recurse
        IF (DATEPART(dw, @CurrentDate)!=1)
        BEGIN
			--If it is saturday, change work finish time
			IF (DATEPART(dw, @CurrentDate)=7)
			BEGIN
				SET @WorkFinish = '14:00'
			END
			ELSE
			BEGIN
				SET @WorkFinish = '16:00'
			END

            IF (@CurrentDate!=@FirstDay) AND (@CurrentDate!=@LastDay)
            BEGIN
                SET @Temp = @Temp + @DailyWorkTime
            END
            --IF it starts at startdate and it finishes not this date find diff between work finish and start as minutes
            ELSE IF (@CurrentDate=@FirstDay) AND (@CurrentDate!=@LastDay)
            BEGIN
                SET @Temp = @Temp + dbo.[EtoWorkHour](@StartTime,@WorkFinish,@BreakStart,@BreakFinish)
            END

            ELSE IF (@CurrentDate!=@FirstDay) AND (@CurrentDate=@LastDay)
            BEGIN
                SET @Temp = @Temp + dbo.[EtoWorkHour](@WorkStart, @FinishTime,@BreakStart,@BreakFinish)
            END
            --IF it starts and finishes in the same date
            ELSE IF (@CurrentDate=@FirstDay) AND (@CurrentDate=@LastDay)
            BEGIN
                SET @Temp = dbo.[EtoWorkHour](@StartTime, @FinishTime,@BreakStart,@BreakFinish)
            END
        END
        SET @CurrentDate = DATEADD(day, 1, @CurrentDate)
    END

    -- Return the result of the function
    IF @Temp<0
    BEGIN
        SET @Temp=0
    END
    RETURN @Temp

END

Fungsi kedua berfungsi untuk mendapatkan jam kerja efektif dalam hari yang sama, sebagai berikut:

-- =============================================
-- Author:      Blue Spy
-- Create date: 26.08.2014
-- Description: Count hours between two time, excluding a certain period
-- =============================================
CREATE FUNCTION [dbo].[EtoWorkHour] 
(
    @StartTime TIME,
    @FinishTime TIME,
	@ExcludeStartTime TIME,
	@ExcludeFinishTime TIME
)
RETURNS BIGINT
AS
BEGIN
	DECLARE @Temp BIGINT
    SET @Temp=0

	--SET @ExcludeStartTime=DATEADD(MINUTE,1,@ExcludeStartTime)
	--SET @ExcludeFinishTime=DATEADD(MINUTE,1,@ExcludeFinishTime)

	IF @StartTime < @ExcludeFinishTime AND @StartTime > @ExcludeStartTime
	BEGIN
		SET @StartTime = @ExcludeFinishTime
	END

	IF @FinishTime < @ExcludeFinishTime AND @FinishTime > @ExcludeStartTime
	BEGIN
		SET @FinishTime = @ExcludeStartTime
	END

	--All times are located before break
	IF @StartTime < @ExcludeStartTime AND @FinishTime <= @ExcludeStartTime
	BEGIN
		SET @Temp = DATEDIFF(MINUTE, @StartTime, @FinishTime)
	END

	--All times are located after break
	ELSE IF @StartTime >= @ExcludeFinishTime AND @FinishTime > @ExcludeFinishTime
	BEGIN
		SET @Temp = DATEDIFF(MINUTE, @StartTime, @FinishTime)
	END

	--Times are splitted
	ELSE
	BEGIN
		SET @Temp = DATEDIFF(MINUTE, @StartTime, @ExcludeStartTime) + DATEDIFF(MINUTE, @ExcludeFinishTime,@FinishTime)
	END

    -- Return the result of the function
    IF @Temp<0
    BEGIN
        SET @Temp=0
    END
    RETURN @Temp

END

Adapun penggunaannya adalah sebagai berikut:

SELECT EtoWorkTime('2014-18-06 00:00:01', '2014-18-06 00:00:01')

Tinggalkan Balasan

Isikan data di bawah atau klik salah satu ikon untuk log in:

Logo WordPress.com

You are commenting using your WordPress.com account. Logout / Ubah )

Gambar Twitter

You are commenting using your Twitter account. Logout / Ubah )

Foto Facebook

You are commenting using your Facebook account. Logout / Ubah )

Foto Google+

You are commenting using your Google+ account. Logout / Ubah )

Connecting to %s