difference of Hour and minutes in working hours

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)

difference of Hour and minutes in working hours

Author

Topic

abhit_kumar
Posting Yak Master

147 Posts

Posted - 2011-08-19 : 04:15:53

Hi All Experts,

I want the difference of Hour and minutes in working hours !

Our Working hours are 9.30 am to 6.00 pm

So for example:-

Date 1:- '2011-08-06 14:30' [6th August 2011, 2.30 pm]

Date 2:- '2011-08-08 10:25' [8th August 2011, 10.25 am]

Now i want the difference of above two date, with in above said working hours only.

so total difference of working hours should be 12 hr 55 min.

And in Total minutes its should be 775 mins

Please let me know how to do this?

Regards,
ABHI

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-19 : 04:21:02

How about Saturday / Sunday ?

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-19 : 04:29:27

see

http://visakhm.blogspot.com/2010/03/calculating-business-hours.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

abhit_kumar
Posting Yak Master

147 Posts

Posted - 2011-08-19 : 04:37:33

Nice question ! :)

Actually we shouldnt consider Sunday in working hours, and for saturday if first date is saturday then we have to include the Saturday, if first date is not saturday then Saturday also treat as non working hours. because in our office we have alternate saturday off in some months, and in some months there is no saturday off.

quote:
Originally posted by khtan

How about Saturday / Sunday ?

KH
[spoiler]Time is always against us[/spoiler]

abhit_kumar
Posting Yak Master

147 Posts

Posted - 2011-08-19 : 05:05:32

Thanks Visakh,

This function is very useful, i did some changes in that and my problem has been resolved now.

Thanks,
Abhi

quote:
Originally posted by visakh16

see

http://visakhm.blogspot.com/2010/03/calculating-business-hours.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-19 : 05:10:50

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

abhit_kumar
Posting Yak Master

147 Posts

Posted - 2011-08-19 : 08:43:12

Dear visakh,

One problem found in this function, when i have tried to put same date then hr is coming incorrect.

For ex:-
SELECT dbo.BusinessHours('2011-08-04 10:00:00.000','2011-08-04 11:00:00.000','08:00','17:00')

It returns me 7, actually its should be 1 hr.

Please let me know in which part of code, needs to do change.

Regards,

quote:
Originally posted by visakh16

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-19 : 12:01:15

quote:
Originally posted by abhit_kumar

Dear visakh,

One problem found in this function, when i have tried to put same date then hr is coming incorrect.

For ex:-
SELECT dbo.BusinessHours('2011-08-04 10:00:00.000','2011-08-04 11:00:00.000','08:00','17:00')

It returns me 7, actually its should be 1 hr.

Please let me know in which part of code, needs to do change.

Regards,
quote:
Originally posted by visakh16

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

i've edited now. please use the edited version.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-19 : 12:16:43

it should be like


IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME ='BusinessHours' AND ROUTINE_SCHEMA = 'dbo' AND ROUTINE_TYPE='FUNCTION')
DROP FUNCTION dbo.BusinessHours
GO
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=SUM(
   CASE 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

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

abhit_kumar
Posting Yak Master

147 Posts

Posted - 2011-08-20 : 01:06:25

I have created one holiday table, in that i have defined holidays..

Holiday has been defined based upon the area.

Means Area 1 has holiday on 15th Aug 2011
But Area 2 didnt have holiday on 15th Aug 2011.

So can i put a logic where it consider Area, and then its holiday.

If i pass Area "ALL", then its means then its not go for any area logic, it will remain same as per your above said code logic.

Please guide.

Sorry to disturb you again.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-20 : 01:12:40

then you need to change the join with holiday table to also include the area field to check if its a holiday for that area

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

abhit_kumar
Posting Yak Master

147 Posts

Posted - 2011-08-23 : 03:06:21

Thanks Visakh,

I have tried to join Area logic too, but not successding.

I want like that:-

Area 1 :- its have holiday on 15th Aug 2011 [so include business hr]

Area 2 :- its doesnt have holiday on 15th Aug 2011 [so dont include business hr]

Please help me.

quote:
Originally posted by visakh16

then you need to change the join with holiday table to also include the area field to check if its a holiday for that area

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources