Calculating Business Minutes between 2 datetimes

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)

Calculating Business Minutes between 2 datetimes

Author

Topic

hendothehun
Starting Member

10 Posts

Posted - 2011-09-25 : 13:12:58

Hi Guys,

I was wondering if you could possibly help me...

I am trying to find an SQL script (preferably in function form) which will calculate the number of business MINUTES between 2 dates.

I found on here a post which calculates the number of HOURS between 2 dates but does not extend to MINUTES:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=164466

...My scenario is this - I am trying to calculate the number of service minutes from a service ticket in order to find out if SLA's (Service Level Agreement's) have been breached.......the count should only include minutes between the hours of 8:30-18:00 Mon-Fri excluding Sat/Sun and Christmas day.

Example of Data:

Ticket Opened - 2011-03-02 10:35:00.000
Ticket Closed - 2011-03-03 10:43:00.000

.....by my manual calculation of business hours this should be 578mins

Can someone help me with this?

If you require any further information please let me know.

Thanks in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-25 : 13:46:13

did you try link i posted in that?

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-09-25 : 13:52:39

before that to get two dates you need to something like


SELECT TicketID,
MAX(CASE WHEN Description='Ticket Opened' THEN DateVal ELSE NULL END) AS STartDate,
MAX(CASE WHEN Description='Ticket Closed' THEN DateVal ELSE NULL END) AS EndDate
FROM table
GROUP BY ticketID

I've assumed the column names so make sure you replace them with actual ones

once you do that you can pass the value to udf i created as follows


SELECT TicketID,dbo.BusinessHours(t.STartDate,t.EndDate,'08:30','18:00') AS TimeElapsed
FROM
(
SELECT TicketID,
MAX(CASE WHEN Description='Ticket Opened' THEN DateVal ELSE NULL END) AS STartDate,
MAX(CASE WHEN Description='Ticket Closed' THEN DateVal ELSE NULL END) AS EndDate
FROM table
GROUP BY ticketID
)t

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

hendothehun
Starting Member

10 Posts

Posted - 2011-09-25 : 13:53:51

Hi Visakh16,

Many thanks for your response.

Yes this was the script I was referring to (Sorry, not very clear) which as far as I can see only calculates HOURS.

Could you possibly ammend this for me so that it will calculate minutes?

I did try adding minutes to the end of the @startdate and @enddate strings however this didn't work

Thanks

hendothehun
Starting Member

10 Posts

Posted - 2011-09-25 : 14:49:39

Hi Visakh16,

Did my post above make sense? I need this to be able to calculate minutes....

Thanks for your help so far....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-26 : 00:48:42

quote:
Originally posted by hendothehun

Hi Visakh16,

Did my post above make sense? I need this to be able to calculate minutes....

Thanks for your help so far....

just remove the /60 calculations in last step which populates the Return value in udf

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

hendothehun
Starting Member

10 Posts

Posted - 2011-09-26 : 13:30:25

Thanks mate.

Got some results back. Testing the accuracy just now. Will feed back as soon as I have tested

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-26 : 13:35:35

ok . lets me know how you got on

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

hendothehun
Starting Member

10 Posts

Posted - 2011-09-26 : 13:58:14

Just waiting on some test data to arrive......in the meatime I have one other question I would like to ask if this is ok.....

Currently my select statement looks like this:

SELECT [Priority_Level]
,[Status]
,[Reference]
,[Customer_Reference]
,[Date_Time_Logged]
,[Date_Time_Closed], dbo.BusinessHours(Date_Time_Logged,Date_Time_Closed,'08:30','18:00') as "Total_Open_Time"
,[Summary_of_Issue]
,[Assigned_To_Engineer_Tstp], dbo.BusinessHours(Date_Time_Logged,Assigned_To_Engineer_Tstp,'08:30','18:00') as "Time_To_Assign_Job"
,[First_Debrief_Tstp]
,[Customer_or_our_Clock1], dbo.BusinessHours(Date_Time_Logged,"First_Debrief_Tstp",'08:30','18:00') as "Time_To_First_Debrief"
,[Second_Debrief_Tstp]
,[Customer_or_our_Clock2], dbo.BusinessHours("First_Debrief_Tstp","Second_Debrief_Tstp",'08:30','18:00') as "Time_To_Second_Debrief"
,[Third_Debrief_Tstp]
,[Customer_or_our_Clock3], dbo.BusinessHours("Second_Debrief_Tstp","Third_Debrief_Tstp",'08:30','18:00') as "Time_To_Third_Debrief"
,[Forth_Debrief_Tstp]
,[Customer_or_our_Clock4], dbo.BusinessHours("Third_Debrief_Tstp","Forth_Debrief_Tstp",'08:30','18:00') as "Time_To_Forth_Debrief"
,[Fifth_Debrief_Tstp]
,[Customer_or_our_Clock5], dbo.BusinessHours("Forth_Debrief_Tstp","Fifth_Debrief_Tstp",'08:30','18:00') as "Time_To_Fifth_Debrief"
,[Sixth_Debrief_Tstp]
,[Customer_or_our_Clock6], dbo.BusinessHours("Fifth_Debrief_Tstp","Sixth_Debrief_Tstp",'08:30','18:00') as "Time_To_Sixth_Debrief"
,[Seventh_Debrief_Tstp]
,[Customer_or_our_Clock7], dbo.BusinessHours("Sixth_Debrief_Tstp","Seventh_Debrief_Tstp",'08:30','18:00') as "Time_To_Seventh_Debrief"
,[Eighth_Debrief_Tstp]
,[Customer_or_our_Clock8], dbo.BusinessHours("Seventh_Debrief_Tstp","Eighth_Debrief_Tstp",'08:30','18:00') as "Time_To_Eighth_Debrief"
,[Ninth_Debrief_Tstp]
,[Customer_or_our_Clock9], dbo.BusinessHours("Eighth_Debrief_Tstp","Ninth_Debrief_Tstp",'08:30','18:00') as "Time_To_Ninth_Debrief"
,[Tenth_Debrief_Tstp]
,[Customer_or_our_Clock10], dbo.BusinessHours("Ninth_Debrief_Tstp","Tenth_Debrief_Tstp",'08:30','18:00') as "Time_To_Tenth_Debrief"

FROM Service_Jobs

.......I am now looking to make a SUM of all timestamps (Tstp) where the associated 'Customer_or_our_Clock' value = 'customer'...

e.g. if the 'Customer_or_our_Clock' values looked like this:

Customer_or_our_Clock1 = 'US'
Customer_or_our_Clock2 = 'Customer'
Customer_or_our_Clock3 = 'US'
Customer_or_our_Clock4 = 'Customer'
Customer_or_our_Clock5 = 'US'
Customer_or_our_Clock6 = 'Customer'
Customer_or_our_Clock7 = 'US'
Customer_or_our_Clock8 = 'Customer'
Customer_or_our_Clock9 = 'US'
Customer_or_our_Clock10 = 'Customer'

I would like it only to add the values for Time_To_Second_Debrief, Time_To_Forth_Debrief, Time_To_Sixth_Debrief, Time_To_Eighth_Debrief, Time_To_Eighth_Debrief

I don't mind if the SUM includes the other Time_to_XXXXXXX_Debrief values as long as find they are not 'customer' they are 0

Any ideas?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-26 : 14:08:23

you need to use case when inside max to get only required fields. Between just noticed you're using "" around column names. Please remove it else it will cause error thinking that its string constant value at least inside udf

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-26 : 14:11:55

also why are you using separate columns like Customer_or_our_Clock1,Customer_or_our_Clock2 etc to store values? why not make them rows instead? have you heard about normalisation? This is certainly not scalable especially if values keep on growing

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

hendothehun
Starting Member

10 Posts

Posted - 2011-09-26 : 14:41:26

Hi,

OK I have removed "" the query thanks.

By normalisation do you like using lookup tables? I'm very new to SQL, but I understand Joins etc.

The reason have seperate columns Customer_or_our_Clock1,Customer_or_our_Clock2 is beccause I will be uploading the data from CSV to the table which only has one row per ticket

I did trying using CASE WHEN but I got some errors....can you give me an example of how it should look.

Many thanks in advance...

hendothehun
Starting Member

10 Posts

Posted - 2011-09-26 : 14:41:59

*By normalisation do you mean like

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-26 : 14:53:27

quote:
Originally posted by hendothehun

Hi,

OK I have removed "" the query thanks.

By normalisation do you like using lookup tables? I'm very new to SQL, but I understand Joins etc.

The reason have seperate columns Customer_or_our_Clock1,Customer_or_our_Clock2 is beccause I will be uploading the data from CSV to the table which only has one row per ticket

I did trying using CASE WHEN but I got some errors....can you give me an example of how it should look.

Many thanks in advance...

case... when will be like

SUM(CASE WHEN Customer_or_our_Clock1 = 'Customer' THEN Time_To_First_Debrief ELSE 0 END) + SUM(CASE WHEN Customer_or_our_Clock2 = 'Customer' THEN Time_To_Second_Debrief ELSE 0 END) + SUM(CASE WHEN Customer_or_our_Clock3 = 'Customer' THEN Time_To_Third_Debrief ELSE 0 END) + ....

yep. by normalising you will add a lookup table where you will have column Customer_or_our_Clock and will have multiple values per customer and will be linking through common customer fields

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

hendothehun
Starting Member

10 Posts

Posted - 2011-09-26 : 15:11:50

ok.......

Here is what I have now.....almost there I think but getting a grouping error message.....

SELECT [Priority_Level]
,[Status]
,[Reference]
,[Customer_Reference]
,[Date_Time_Logourd]
,[Date_Time_Closed], dbo.BusinessHours(Date_Time_Logourd,Date_Time_Closed,'08:30','18:00') as Total_Open_Time
,[Summary_of_Issue]
,[Assigned_To_Engineer_Tstp], dbo.BusinessHours(Date_Time_Logourd,Assigned_To_Engineer_Tstp,'08:30','18:00') as Time_To_Assign_Job
,[First_Debrief_Tstp]
,[Customer_or_our_Clock1], dbo.BusinessHours(Date_Time_Logourd,First_Debrief_Tstp,'08:30','18:00') as Time_To_First_Debrief
,[Second_Debrief_Tstp]
,[Customer_or_our_Clock2], dbo.BusinessHours(First_Debrief_Tstp,Second_Debrief_Tstp,'08:30','18:00') as Time_To_Second_Debrief
,[Third_Debrief_Tstp]
,[Customer_or_our_Clock3], dbo.BusinessHours(Second_Debrief_Tstp,Third_Debrief_Tstp,'08:30','18:00') as Time_To_Third_Debrief
,[Forth_Debrief_Tstp]
,[Customer_or_our_Clock4], dbo.BusinessHours(Third_Debrief_Tstp,Forth_Debrief_Tstp,'08:30','18:00') as Time_To_Forth_Debrief
,[Fifth_Debrief_Tstp]
,[Customer_or_our_Clock5], dbo.BusinessHours(Forth_Debrief_Tstp,Fifth_Debrief_Tstp,'08:30','18:00') as Time_To_Fifth_Debrief
,[Sixth_Debrief_Tstp]
,[Customer_or_our_Clock6], dbo.BusinessHours(Fifth_Debrief_Tstp,Sixth_Debrief_Tstp,'08:30','18:00') as Time_To_Sixth_Debrief
,[Seventh_Debrief_Tstp]
,[Customer_or_our_Clock7], dbo.BusinessHours(Sixth_Debrief_Tstp,Seventh_Debrief_Tstp,'08:30','18:00') as Time_To_Seventh_Debrief
,[Eighth_Debrief_Tstp]
,[Customer_or_our_Clock8], dbo.BusinessHours(Seventh_Debrief_Tstp,Eighth_Debrief_Tstp,'08:30','18:00') as Time_To_Eighth_Debrief
,[Ninth_Debrief_Tstp]
,[Customer_or_our_Clock9], dbo.BusinessHours(Eighth_Debrief_Tstp,Ninth_Debrief_Tstp,'08:30','18:00') as Time_To_Ninth_Debrief
,[Tenth_Debrief_Tstp]
,[Customer_or_our_Clock10], dbo.BusinessHours(Ninth_Debrief_Tstp,Tenth_Debrief_Tstp,'08:30','18:00') as Time_To_Tenth_Debrief
,SUM (CASE WHEN Customer_or_our_Clock1 ='Customer' THEN (dbo.BusinessHours(Date_Time_Logourd,First_Debrief_Tstp,'08:30','18:00')) ELSE '0' END) +
SUM(CASE WHEN Customer_or_our_Clock2='Customer' THEN (dbo.BusinessHours(Date_Time_Logourd,Second_Debrief_Tstp,'08:30','18:00')) ELSE '0' END) +
SUM(CASE WHEN Customer_or_our_Clock3='Customer' THEN (dbo.BusinessHours(Date_Time_Logourd,Third_Debrief_Tstp,'08:30','18:00')) ELSE '0' END) +
SUM(CASE WHEN Customer_or_our_Clock4='Customer' THEN (dbo.BusinessHours(Date_Time_Logourd,Forth_Debrief_Tstp,'08:30','18:00')) ELSE '0' END) +
SUM(CASE WHEN Customer_or_our_Clock5='Customer' THEN (dbo.BusinessHours(Date_Time_Logourd,Fifth_Debrief_Tstp,'08:30','18:00')) ELSE '0' END) +
SUM(CASE WHEN Customer_or_our_Clock6='Customer' THEN (dbo.BusinessHours(Date_Time_Logourd,Sixth_Debrief_Tstp,'08:30','18:00')) ELSE '0' END) +
SUM(CASE WHEN Customer_or_our_Clock7='Customer' THEN (dbo.BusinessHours(Date_Time_Logourd,Seventh_Debrief_Tstp,'08:30','18:00')) ELSE '0' END) +
SUM(CASE WHEN Customer_or_our_Clock8='Customer' THEN (dbo.BusinessHours(Date_Time_Logourd,Eighth_Debrief_Tstp,'08:30','18:00')) ELSE '0' END) +
SUM(CASE WHEN Customer_or_our_Clock9='Customer' THEN (dbo.BusinessHours(Date_Time_Logourd,Ninth_Debrief_Tstp,'08:30','18:00')) ELSE '0' END) +
SUM(CASE WHEN Customer_or_our_Clock10='Customer' THEN (dbo.BusinessHours(Date_Time_Logourd,Tenth_Debrief_Tstp,'08:30','18:00')) ELSE '0' END)

Column 'Service Report.dbo.Service_Jobs.Priority_Level' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

....any ideas?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-26 : 15:13:36

you need to add all columns which are not within aggregate function like SUM(),Avg() etc in the GROUP BY at the end of select

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

hendothehun
Starting Member

10 Posts

Posted - 2011-09-26 : 15:29:52

Brilliant.....almost there......

Got one last issue when calculating Time_To_Third_Debrief

Second_Debrief_Tstp = 2011-07-13 17:10:00.000
Third_Debrief_Tstp = NULL (as there was no Third debrief)

however the resultant calculation

dbo.BusinessHours(Second_Debrief_Tstp,Third_Debrief_Tstp,'08:30','18:00') as Time_To_Third_Debrief

.....returns a value of 50.....

I think it may not like the NULL value.....Can you see where the problem is?
Thanks

hendothehun
Starting Member

10 Posts

Posted - 2011-09-26 : 15:57:59

Hi Mate,

Finally I have answered something for myself.......I fixed the above issue by using:

(CASE WHEN Second_Debrief_Tstp IS NOT NULL AND Third_Debrief_Tstp IS NOT NULL THEN (dbo.BusinessHours(Second_Debrief_Tstp,Third_Debrief_Tstp,'08:30','18:00')) ELSE '0' END) as Time_To_Third_Debrief

....will keep you posted on testing.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-27 : 00:40:10

cool...
yep..lets us know how it went!

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources