Bar&Bar Data

-- =============================================
-- Author:      Baran Kaynak
-- Create date: 14.03.2011
-- Description: 09:30 ile 17:30 arasındaki iş saatlerini hafta sonlarını almayarak toplar.
-- =============================================
CREATE FUNCTION [dbo].[WorkTime]
(
@StartDate DATETIME,
@FinishDate DATETIME
)
RETURNS BIGINT
AS
BEGIN
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 = '09:30'

DECLARE @WorkFinish TIME
SET @WorkFinish = '17:30'

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

DECLARE @CurrentDate DATE
SET @CurrentDate = CONVERT(DATE, @StartDate, 112)
DECLARE @LastDate DATE
SET @LastDate = CONVERT(DATE, @FinishDate, 112)

WHILE(@CurrentDate<=@LastDate)
BEGIN
IF (DATEPART(dw, @CurrentDate)!=1 AND DATEPART(dw, @CurrentDate)!=7)
BEGIN
IF (@CurrentDate!=@FirstDay) AND (@CurrentDate!=@LastDay)
BEGIN
SET @Temp = (@Temp + (9*60))
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 + DATEDIFF(MINUTE, @StartTime, @WorkFinish)
END

ELSE IF (@CurrentDate!=@FirstDay) AND (@CurrentDate=@LastDay)
BEGIN
SET @Temp = @Temp + DATEDIFF(MINUTE, @WorkStart, @FinishTime)
END
--IF it starts and finishes in the same date
ELSE IF (@CurrentDate=@FirstDay) AND (@CurrentDate=@LastDay)
BEGIN
SET @Temp = DATEDIFF(MINUTE, @StartDate, @FinishDate)
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

GO