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.
Go to Top of Page

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);
Go to Top of Page

sanjay5219
Posting Yak Master

240 Posts

Posted - 2013-09-03 : 15:17:56
Sorry it should give 0
Go to Top of Page

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);
Go to Top of Page

sanjay5219
Posting Yak Master

240 Posts

Posted - 2013-09-03 : 15:33:24
Thanks Buddy you Rock
Go to Top of Page
   

- Advertisement -