Business Hour Problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Business Hour Problem

Author

Topic

sanjay5219
Posting Yak Master

240 Posts

Posted - 2013-09-03 : 11:23:59

Hi All,

I have created this script by using some help from Internet. But this articular script is not working for below

SELECT DBO.BUSINESSHOURS('2013-08-12 17:47:52.697','2013-08-13 07:48:08.207','08:30','16:30')

Actually this should give 0 but it is actually returning 8 hrs

Create FUNCTION dbo.BusinessHours
(
@StartDate datetime, --start of period of consideration for calculation of business hours
@EndDate datetime, --end of period of consideration for calculation of business hours
@BusinessStart datetime, --start of business hours
@BusinessEnd datetime --end of business hours
)
RETURNS int
AS
BEGIN
DECLARE @TotalHours int
IF DATEDIFF(dd,0,@StartDate)=DATEDIFF(dd,0,@EndDate)
BEGIN
IF @BusinessStart>@BusinessEnd
BEGIN
SET @TotalHours= -1
END
ELSE
BEGIN
SELECT @BusinessStart=CASE WHEN DATEADD(dd,-DATEDIFF(dd,0,@StartDate),@StartDate) > @BusinessStart AND DATEADD(dd,-DATEDIFF(dd,0,@StartDate),@StartDate) < @BusinessEnd
THEN DATEADD(dd,-DATEDIFF(dd,0,@StartDate),@StartDate) ELSE @BusinessStart END,
@BusinessEnd =CASE WHEN DATEADD(dd,-DATEDIFF(dd,0,@EndDate),@EndDate) > @BusinessStart AND DATEADD(dd,-DATEDIFF(dd,0,@EndDate),@EndDate) < @BusinessEnd
THEN DATEADD(dd,-DATEDIFF(dd,0,@EndDate),@EndDate) ELSE @BusinessEnd END
SET @TotalHours= CASE WHEN DATEADD(dd,-DATEDIFF(dd,0,@StartDate),@StartDate) > @BusinessEnd OR DATEADD(dd,-DATEDIFF(dd,0,@EndDate),@EndDate) < @BusinessStart
THEN 0
ELSE DATEDIFF(mi,@BusinessStart,@BusinessEnd)/60
END
END
END
ELSE
BEGIN
SELECT @StartDate= CASE WHEN DATEADD(dd,-DATEDIFF(dd,0,@StartDate),@StartDate) > @BusinessEnd THEN DATEADD(ss,DATEDIFF(ss,0,@BusinessStart),DATEADD(dd,DATEDIFF(dd,0,@StartDate),1))
WHEN DATEADD(dd,-DATEDIFF(dd,0,@StartDate),@StartDate) < @BusinessStart THEN DATEADD(ss,DATEDIFF(ss,0,@BusinessStart),DATEADD(dd,DATEDIFF(dd,0,@StartDate),0))
ELSE @StartDate
END,
@EndDate= CASE WHEN DATEADD(dd,-DATEDIFF(dd,0,@EndDate),@EndDate) < @BusinessStart THEN DATEADD(ss,DATEDIFF(ss,0,@BusinessEnd),DATEADD(dd,DATEDIFF(dd,0,@EndDate),-1))
WHEN DATEADD(dd,-DATEDIFF(dd,0,@EndDate),@EndDate) > @BusinessEnd THEN DATEADD(ss,DATEDIFF(ss,0,@BusinessEnd),DATEADD(dd,DATEDIFF(dd,0,@EndDate),0))
ELSE @EndDate
END
;With Calendar_CTE (Date,Day,WeekDay)
AS
(
SELECT DATEADD(dd,DATEDIFF(dd,0,@StartDate),0),DATENAME(dw,DATEADD(dd,DATEDIFF(dd,0,@StartDate),0)), CASE WHEN DATENAME(dw,DATEADD(dd,DATEDIFF(dd,0,@StartDate),0)) NOT IN ('Saturday','Sunday') THEN 1 ELSE 0 END
UNION ALL
SELECT DATEADD(dd,1,Date),DATENAME(dw,DATEADD(dd,1,Date)), CASE WHEN DATENAME(dw,DATEADD(dd,1,Date)) NOT IN ('Saturday','Sunday') THEN 1 ELSE 0 END
FROM Calendar_CTE
WHERE DATEADD(dd,1,Date) <= @EndDate
)
SELECT @TotalHours=CEILING(SUM(
CASE WHEN DATEADD(dd,DATEDIFF(dd,0,@StartDate),0)=DATEADD(dd,DATEDIFF(dd,0,@EndDate),0) THEN DATEDIFF(MINUTE,@StartDate,@EndDate)
WHEN c.Date=DATEADD(dd,DATEDIFF(dd,0,@StartDate),0) THEN DATEDIFF(MINUTE,DATEADD(dd,-DATEDIFF(dd,0,@StartDate),@StartDate),@BusinessEnd)
WHEN c.Date=DATEADD(dd,DATEDIFF(dd,0,@EndDate),0) THEN DATEDIFF(MINUTE,@BusinessStart,DATEADD(dd,-DATEDIFF(dd,0,@EndDate),@EndDate))
ELSE DATEDIFF(MINUTE,@BusinessStart,@BusinessEnd)
END
)/60)
FROM Calendar_CTE c
LEFT JOIN Holiday h
ON h.[date]= c.Date
WHERE WeekDay=1
AND h.[date] IS NULL
OPTION (MAXRECURSION 0)
END
RETURN @TotalHours
END

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-03 : 13:27:21

I can sort of see the logic you are trying to implement, but not quite enough to tell you what you might be doing wrong. It seemed too complex for what you want to accomplish - can you try the following code? It seems simpler to me (probably because it was written by yours truly, so feel free to disagree).

It does return 0 for your sample data. But if you choose to use this, please test enough to satisfy yourself that it produces the correct answers. If it does not produce the correct answer, post back and I can fix it for you.

DECLARE @StartDate DATETIME = '2013-08-12 17:47:52.697';
DECLARE @EndDate DATETIME = '2013-08-13 07:48:08.207';
DECLARE @BusinessStart TIME = '08:30'
DECLARE @BusinessEnd TIME = '16:30';

;WITH Calendar([Date],[IsWeekend]) AS
(
	SELECT 
		CAST(@StartDate AS DATE), 
		CASE WHEN DATEDIFF(dd,0,@StartDate)%7 >= 5 THEN 1 ELSE 0 END 
	UNION ALL
	SELECT 
		DATEADD(dd,1,[Date]),
		CASE WHEN DATEDIFF(dd,0,DATEADD(dd,1,[Date]))%7 >= 5 THEN 1 ELSE 0 END 
	 FROM Calendar
	 WHERE 
		[Date] < CAST(@EndDate AS DATE)
),
StartTimes(StartTime, EndTime) AS
(
	SELECT 
		CASE 
			WHEN CAST(@StartDate AS TIME) <= @BusinessStart THEN CAST(@BusinessStart AS TIME)
			WHEN CAST(@StartDate AS TIME) >= @BusinessEnd   THEN CAST(@BusinessEnd AS TIME)
			ELSE CAST(@StartDate AS TIME) 
		END,
		CASE
			WHEN CAST(@EndDate AS TIME) <= @BusinessStart THEN CAST(@BusinessStart AS TIME)
			WHEN CAST(@EndDate AS TIME) >= @BusinessEnd   THEN CAST(@BusinessEnd AS TIME)
			ELSE CAST(@EndDate AS TIME) 
		END
)
SELECT
	SUM(
	CASE
		WHEN c.[Date] = CAST(@StartDate AS DATE) THEN  DATEDIFF(mi, StartTime, @BusinessEnd)
		WHEN c.[Date] = CAST(@EndDate AS DATE)   THEN  DATEDIFF(mi, @BusinessStart, EndTime)
		ELSE DATEDIFF(mi,@BusinessStart,@BusinessEnd)
	END)/60.0 BusinessHours
FROM
	Calendar c
	CROSS JOIN StartTimes s
	LEFT JOIN Holiday h ON h.Date = c.Date
WHERE
	h.Date IS NULL
	AND IsWeekend = 0
OPTION (MAXRECURSION 0);

Edit: By the way, I assumed SQL 2008 or later. It is not too hard to modify it to make it work with earlier versions of SQL, but I noticed that you posted in SQL 2005 forum only after I posted this.

sanjay5219
Posting Yak Master

240 Posts

Posted - 2013-09-03 : 15:03:30

Thanks but if you see beloe example it is not working
It should give 57 but it is giving 480 min

DECLARE @StartDate DATETIME = '2013-08-02 02:07:11';
DECLARE @EndDate DATETIME = '2013-08-02 02:57:47';
DECLARE @BusinessStart TIME = '08:30'
DECLARE @BusinessEnd TIME = '16:30';

;WITH Calendar([Date],[IsWeekend]) AS
(
SELECT
CAST(@StartDate AS DATE),
CASE WHEN DATEDIFF(dd,0,@StartDate)%7 >= 5 THEN 1 ELSE 0 END
UNION ALL
SELECT
DATEADD(dd,1,[Date]),
CASE WHEN DATEDIFF(dd,0,DATEADD(dd,1,[Date]))%7 >= 5 THEN 1 ELSE 0 END
FROM Calendar
WHERE
[Date] < CAST(@EndDate AS DATE)
),
StartTimes(StartTime, EndTime) AS
(
SELECT
CASE
WHEN CAST(@StartDate AS TIME) <= @BusinessStart THEN CAST(@BusinessStart AS TIME)
WHEN CAST(@StartDate AS TIME) >= @BusinessEnd THEN CAST(@BusinessEnd AS TIME)
ELSE CAST(@StartDate AS TIME)
END,
CASE
WHEN CAST(@EndDate AS TIME) <= @BusinessStart THEN CAST(@BusinessStart AS TIME)
WHEN CAST(@EndDate AS TIME) >= @BusinessEnd THEN CAST(@BusinessEnd AS TIME)
ELSE CAST(@EndDate AS TIME)
END
)
SELECT
SUM(
CASE
WHEN c.[Date] = CAST(@StartDate AS DATE) THEN DATEDIFF(mi, StartTime, @BusinessEnd)
WHEN c.[Date] = CAST(@EndDate AS DATE) THEN DATEDIFF(mi, @BusinessStart, EndTime)
ELSE DATEDIFF(mi,@BusinessStart,@BusinessEnd)
END) BusinessHours
FROM
Calendar c
CROSS JOIN StartTimes s
LEFT JOIN Holiday h ON h.Date = c.Date
WHERE
h.Date IS NULL
AND IsWeekend = 0
OPTION (MAXRECURSION 0);

sanjay5219
Posting Yak Master

240 Posts

Posted - 2013-09-03 : 15:17:56

Sorry it should give 0

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-03 : 15:17:58

You are right. I did not take into account the case where both start and end are on the same day. See below:

DECLARE @StartDate DATETIME = '2013-08-02 02:07:11';
DECLARE @EndDate DATETIME = '2013-08-02 02:57:47';
DECLARE @BusinessStart TIME = '08:30'
DECLARE @BusinessEnd TIME = '16:30';


;WITH Calendar([Date],[IsWeekend]) AS
(
	SELECT 
		CAST(@StartDate AS DATE), 
		CASE WHEN DATEDIFF(dd,0,@StartDate)%7 >= 5 THEN 1 ELSE 0 END 
	WHERE 
		@StartDate <= @EndDate
	UNION ALL
	SELECT 
		DATEADD(dd,1,[Date]),
		CASE WHEN DATEDIFF(dd,0,DATEADD(dd,1,[Date]))%7 >= 5 THEN 1 ELSE 0 END 
	 FROM Calendar
	 WHERE 
		[Date] < CAST(@EndDate AS DATE)
),
StartTimes(StartTime, EndTime) AS
(
	SELECT 
		CASE 
			WHEN CAST(@StartDate AS TIME) <= @BusinessStart THEN CAST(@BusinessStart AS TIME)
			WHEN CAST(@StartDate AS TIME) >= @BusinessEnd   THEN CAST(@BusinessEnd AS TIME)
			ELSE CAST(@StartDate AS TIME) 
		END,
		CASE
			WHEN CAST(@EndDate AS TIME) <= @BusinessStart THEN CAST(@BusinessStart AS TIME)
			WHEN CAST(@EndDate AS TIME) >= @BusinessEnd   THEN CAST(@BusinessEnd AS TIME)
			ELSE CAST(@EndDate AS TIME) 
		END
)
SELECT
	SUM(
	CASE
		WHEN c.[Date] = CAST(@StartDate AS DATE) AND c.[Date] = CAST(@EndDate AS DATE) 
			THEN  DATEDIFF(mi, StartTime, EndTime)
		WHEN c.[Date] = CAST(@StartDate AS DATE) THEN  DATEDIFF(mi, StartTime, @BusinessEnd)
		WHEN c.[Date] = CAST(@EndDate AS DATE)   THEN  DATEDIFF(mi, @BusinessStart, EndTime)
		ELSE DATEDIFF(mi,@BusinessStart,@BusinessEnd)
	END)/60.0 BusinessHours
FROM
	Calendar c
	CROSS JOIN StartTimes s
	LEFT JOIN Holiday h ON h.Date = c.Date
WHERE
	h.Date IS NULL
	AND IsWeekend = 0
OPTION (MAXRECURSION 0);

sanjay5219
Posting Yak Master

240 Posts

Posted - 2013-09-03 : 15:33:24

Thanks Buddy you Rock

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources