Need help please - 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)

Need help please

Author

Topic

kkmurthy
Starting Member

41 Posts

Posted - 2010-03-02 : 09:06:36

I need to calculate difference of two datetime fields in relation to hours,only during business days and business hours ( that is Mon through Fri 8 AM - 5 PM). For example if the Help desk ticket is open at 4 PM today and closed at 9 am tomorrow then the duration of the ticket is 2 Hours. Can some one share any SQL function which calculates duration during business days and hours between the specified date range

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-02 : 09:17:01

Make use of this logic


declare @start datetime, @end datetime
select @start='2000-01-01 16:00:00',@end='2000-01-02 9:00:00'
select 
datediff(hour,@start,dateadd(day,datediff(day,0,@start),'17:00:00'))
+
datediff(hour,dateadd(day,datediff(day,0,@end),'8:00:00'),@end)

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-02 : 09:51:26

if it spans multiple days


declare @start datetime, @end datetime
select @start='2000-01-01 12:00:00',@end='2000-02-05 13:00:00'
select 
datediff(hour,DATEADD(dd,-datediff(dd,0,@start),@start),'17:00:00')
+ ((SELECT COUNT(*)-1 FROM dbo.CalendarTable(@start,@end,1)) * DATEDIFF(HH,'08:00','17:00')) +
datediff(hour,'08:00:00',DATEADD(dd,-datediff(dd,0,@end),@end))

dbo.CalendarTable can be found below

http://visakhm.blogspot.com/2010/02/generating-calendar-table.html

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

kkmurthy
Starting Member

41 Posts

Posted - 2010-03-02 : 10:00:14

quote:
Originally posted by visakh16

if it spans multiple days

declare @start datetime, @end datetime
select @start='2000-01-01 12:00:00',@end='2000-02-05 13:00:00'
select
datediff(hour,DATEADD(dd,-datediff(dd,0,@start),@start),'17:00:00')
+ ((SELECT COUNT(*)-1 FROM dbo.CalendarTable(@start,@end,1)) * DATEDIFF(HH,'08:00','17:00')) +
datediff(hour,'08:00:00',DATEADD(dd,-datediff(dd,0,@end),@end))

dbo.CalendarTable can be found below

http://visakhm.blogspot.com/2010/02/generating-calendar-table.html

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

kkmurthy
Starting Member

41 Posts

Posted - 2010-03-02 : 10:04:39

declare @start datetime, @end datetime
select @start='2010-02-25 16:00:00',@end='2010-03-01 9:00:00'
select
datediff(minute,@start,dateadd(day,datediff(day,0,@start),'17:00:00'))
+
datediff(minute,dateadd(day,datediff(day,0,@end),'8:00:00'),@end

In the above situation ( for the date values chosen) the answer is : 600 minutes since we have to exclude saturday and sunday right?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-02 : 10:06:02

didnt that work for you?

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

kkmurthy
Starting Member

41 Posts

Posted - 2010-03-02 : 10:09:47

In my situation, I have incident table whihc records ticket open time and ticket end time. I will have to calculate the the duration in which the ticket is open in Hours( in decimals ) or Minutes. This includes only business hours and business days excluding saturday and Sunday.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-02 : 10:18:42

quote:
Originally posted by kkmurthy

declare @start datetime, @end datetime
select @start='2010-02-25 16:00:00',@end='2010-03-01 9:00:00'
select
datediff(minute,@start,dateadd(day,datediff(day,0,@start),'17:00:00'))
+
datediff(minute,dateadd(day,datediff(day,0,@end),'8:00:00'),@end

In the above situation ( for the date values chosen) the answer is : 600 minutes since we have to exclude saturday and sunday right?

this is not what i posted. see my suggestion before
also how ould it be 600
it should be 660
25th 16:00-17:00 (60 mts)
26th 08:00-17:00 (540 mts)
1 st 08:00 - 09:00 (60 mts)

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-02 : 10:21:50

quote:
Originally posted by kkmurthy

In my situation, I have incident table whihc records ticket open time and ticket end time. I will have to calculate the the duration in which the ticket is open in Hours( in decimals ) or Minutes. This includes only business hours and business days excluding saturday and Sunday.

if you want in minutes, change suggestion accordingly


declare @start datetime, @end datetime
select @start='2010-02-25 16:00:00',@end='2010-03-01 9:00:00'
select 
datediff(minute,DATEADD(dd,-datediff(dd,0,@start),@start),'17:00:00')
+ ((SELECT COUNT(*)-1 FROM dbo.CalendarTable(@start,@end,1)) * DATEDIFF(minute,'08:00','17:00')) +
datediff(minute,'08:00:00',DATEADD(dd,-datediff(dd,0,@end),@end))


output
-----------------------
660

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

kkmurthy
Starting Member

41 Posts

Posted - 2010-03-02 : 10:23:24

declare @start datetime, @end datetime
select @start='2010-02-25 16:00:00',@end='2010-03-01 16:00:00'
select
datediff(hour,DATEADD(dd,-datediff(dd,0,@start),@start),'17:00:00')
+ ((SELECT COUNT(*)-1 FROM dbo.CalendarTable(@start,@end,1)) * DATEDIFF(HH,'08:00','17:00')) +
datediff(hour,'08:00:00',DATEADD(dd,-datediff(dd,0,@end),@end))

I used the above query as is after creating the calendertable function. I should get in the above situation 18 hours instead I am getting 27 hours
Can you please verify this

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-02 : 10:34:21

i think you need this tweak


declare @start datetime, @end datetime
select @start='2010-02-25 16:00:00',@end='2010-03-01 9:00:00'
select 
datediff(hh,DATEADD(dd,-datediff(dd,0,@start),@start),'17:00:00')
+ ((SELECT COUNT(*) FROM dbo.CalendarTable(@start+1,@end-1,1)) * DATEDIFF(hh,'08:00','17:00')) +
datediff(hh,'08:00:00',DATEADD(dd,-datediff(dd,0,@end),@end))

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

kkmurthy
Starting Member

41 Posts

Posted - 2010-03-02 : 12:19:36

declare @start datetime, @end datetime
select @start='2010-02-25 16:00:00',@end='2010-03-01 9:00:00'
select
datediff(hh,DATEADD(dd,-datediff(dd,0,@start),@start),'17:00:00')
+ ((SELECT COUNT(*) FROM dbo.CalendarTable(@start+1,@end-1,1)) * DATEDIFF(hh,'08:00','17:00')) +
datediff(hh,'08:00:00',DATEADD(dd,-datediff(dd,0,@end),@end))
This did not work. I am looking into syntax

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-02 : 12:23:45

why? it gives you 11 hours which is what you want as per your earlier explanation

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

kkmurthy
Starting Member

41 Posts

Posted - 2010-03-02 : 12:46:27

I meant syntax error. database did not like +1 and -1 for the dates (@start+1,@end-1,1 ).

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-02 : 12:50:18

quote:
Originally posted by kkmurthy

I meant syntax error. database did not like +1 and -1 for the dates (@start+1,@end-1,1 ).

its working for me.
is @start,@end datetime fields in your case
it should be datetime

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

kkmurthy
Starting Member

41 Posts

Posted - 2010-03-02 : 12:57:47

I am using exactly the same code after declaring the start and end date given by you I am getting syntax which did not make any sense:

declare @start datetime, @end datetime
select @start='2010-02-25 16:00:00',@end='2010-03-01 9:00:00'
select
datediff(hh,DATEADD(dd,-datediff(dd,0,@start),@start),'17:00:00')
+ ((SELECT COUNT(*) FROM dbo.CalendarTable(@start+1,@end-1,1)) * DATEDIFF(hh,'08:00','17:00')) +
datediff(hh,'08:00:00',DATEADD(dd,-datediff(dd,0,@end),@end))

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-02 : 13:00:47

whats the error?

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

kkmurthy
Starting Member

41 Posts

Posted - 2010-03-02 : 13:04:27

Incorrect syntax near '+'.
in this code
CalendarTable(@start+1,@end-1,1))

kkmurthy
Starting Member

41 Posts

Posted - 2010-03-02 : 13:22:07

Did you get it OK. I am still having syntax error problem. I greatly appreciate your help.Thank you for spending time on this

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-03 : 09:26:07

i'm not getting it. so i doubt you're using ms sql server itself? whats the editor you're using?

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

kkmurthy
Starting Member

41 Posts

Posted - 2010-03-03 : 09:36:08

Query editor in SQL Server Management studio. Is it good to try using some other editor other than the management studio to overcome situations like this ?

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources