SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Business Hour Problem
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sanjay5219
Posting Yak Master

228 Posts

Posted - 09/03/2013 :  11:23:59  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3651 Posts

Posted - 09/03/2013 :  13:27:21  Show Profile  Reply with Quote
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.

Edited by - James K on 09/03/2013 13:33:00
Go to Top of Page

sanjay5219
Posting Yak Master

228 Posts

Posted - 09/03/2013 :  15:03:30  Show Profile  Reply with Quote
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);
Go to Top of Page

sanjay5219
Posting Yak Master

228 Posts

Posted - 09/03/2013 :  15:17:56  Show Profile  Reply with Quote
Sorry it should give 0
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3651 Posts

Posted - 09/03/2013 :  15:17:58  Show Profile  Reply with Quote
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);
Go to Top of Page

sanjay5219
Posting Yak Master

228 Posts

Posted - 09/03/2013 :  15:33:24  Show Profile  Reply with Quote
Thanks Buddy you Rock
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000