12 Replies - 2489 Views - Last Post: 01 July 2013 - 07:20 AM

#1 bcusick  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 26
  • Joined: 27-November 12

How to count business hours in sql

Posted 21 June 2013 - 11:38 AM

I am trying to count 2 business days (in hours) from a certain time.

I know how to count business days, but I need to count business hours...

Basically, "if EVENT happens at 4:30PM Friday...then reaction happens at 4:30PM Tuesday" by counting 48 business day hours. I don't mean 9-5, I basically mean two business days but exactly from 3:33PM Thursday to 3:33PM Monday.

I have code that counts business days here.


DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = '2008/10/01'
SET @EndDate = '2008/10/31'


SELECT
   (DATEDIFF(dd, @StartDate, @EndDate) + 1)
  -(DATEDIFF(wk, @StartDate, @EndDate) * 2)
  -(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)
  -(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)



Is This A Good Question/Topic? 0
  • +

Replies To: How to count business hours in sql

#2 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 9574
  • View blog
  • Posts: 36,262
  • Joined: 12-June 08

Re: How to count business hours in sql

Posted 21 June 2013 - 02:52 PM

It's going to be a tough solution. My first gut reaction is to advocate using CLR stored procedures. Where you would write .NET code (be it VB.NET or C#) in an assembly, and then load that as a stored procedure in MSSQL... you would interact with it the same way, but it would be able to contain the logic for getting 'business days' and return the hours used.
Was This Post Helpful? 0
  • +
  • -

#3 andrewsw  Icon User is online

  • It's just been revoked!
  • member icon

Reputation: 3822
  • View blog
  • Posts: 13,544
  • Joined: 12-December 12

Re: How to count business hours in sql

Posted 21 June 2013 - 03:05 PM

View Postbcusick, on 21 June 2013 - 06:38 PM, said:

I am trying to count 2 business days (in hours) from a certain time.

I know how to count business days, but I need to count business hours...

Basically, "if EVENT happens at 4:30PM Friday...then reaction happens at 4:30PM Tuesday" by counting 48 business day hours. I don't mean 9-5, I basically mean two business days but exactly from 3:33PM Thursday to 3:33PM Monday.

So what should the answer be, and in what format? 48 business hrs? And what logic does it require? That is, explain how it is calculated.

But, as modi123_1 suggests, it might require a CLR stored procedure.

This post has been edited by andrewsw: 21 June 2013 - 03:12 PM

Was This Post Helpful? 0
  • +
  • -

#4 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 574
  • View blog
  • Posts: 2,989
  • Joined: 19-May 09

Re: How to count business hours in sql

Posted 21 June 2013 - 09:31 PM

The logic doesn't seem too difficult. Why can't you just treat each day in your calculation as 24 hours? The only other thing to work out is the number of partial hours in the first and last days and add them together. So difference between start day + 1 and end day - 1 times 24, less 48 if there's a saturday and sunday between the those days, plus the fractional number of hours in the start day (e. g. if start time is 3 pm then 9) plus the fractional number of hours in the end day.

I'm not going to take the hour to put that in sql, but the logic doesn't sound very tough. Am I missing some big complication?
Was This Post Helpful? 0
  • +
  • -

#5 andrewsw  Icon User is online

  • It's just been revoked!
  • member icon

Reputation: 3822
  • View blog
  • Posts: 13,544
  • Joined: 12-December 12

Re: How to count business hours in sql

Posted 22 June 2013 - 12:21 AM

I was thinking the same @Bob: I must be missing something.

@OP You already have 'code' that works out the number of business-days. So I assume you could multiply this number by the number of business-hours in a business-day, then add the extra hours from the days either side.

It is the statement "I don't mean 9-5" that puzzles. Is a business-day 8 hours or is it 24 hours? Clarification required ;)

I have a meeting on Monday. Can I count this as a full day :bigsmile:
Was This Post Helpful? 0
  • +
  • -

#6 bcusick  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 26
  • Joined: 27-November 12

Re: How to count business hours in sql

Posted 22 June 2013 - 09:21 AM

View Postandrewsw, on 22 June 2013 - 12:21 AM, said:

I was thinking the same @Bob: I must be missing something.

@OP You already have 'code' that works out the number of business-days. So I assume you could multiply this number by the number of business-hours in a business-day, then add the extra hours from the days either side.

It is the statement "I don't mean 9-5" that puzzles. Is a business-day 8 hours or is it 24 hours? Clarification required ;)/>

I have a meeting on Monday. Can I count this as a full day :bigsmile:/>



Lol yes, you can count that as a business day. That's the point! I am aiming for Monday-Friday..but 24 hours, not just 9-5.
Was This Post Helpful? 0
  • +
  • -

#7 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 9574
  • View blog
  • Posts: 36,262
  • Joined: 12-June 08

Re: How to count business hours in sql

Posted 22 June 2013 - 09:33 AM

Another question - is the shop even open 24 hours? Case in point - if someone shows up with an issue at 4.30 Friday, the shop closes at 5 so there's 30 business minutes. They get it fixed and back at 9.30am Monday.. and the shop opens at 9.. that would be a total of one business hour.
Was This Post Helpful? 0
  • +
  • -

#8 bcusick  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 26
  • Joined: 27-November 12

Re: How to count business hours in sql

Posted 24 June 2013 - 06:37 AM

View Postmodi123_1, on 22 June 2013 - 09:33 AM, said:

Another question - is the shop even open 24 hours? Case in point - if someone shows up with an issue at 4.30 Friday, the shop closes at 5 so there's 30 business minutes. They get it fixed and back at 9.30am Monday.. and the shop opens at 9.. that would be a total of one business hour.



No, that's what I mean when I say 24 hour business days. The shop is only open 9-5, but that is irrelevant in this situation. Policy states there are 48 hours (Mon-Fri) to get something done. Basically I am counting 5 days a week, Mon-Fri, but down to the hour. So instead of just counting 5 days with an initial time of 00:00:00, I want the time of the event to matter, not just initialize at midnight of that day. I want the program to be smart enough to know what hour/minute to set off the alert. Make sense?
Was This Post Helpful? 0
  • +
  • -

#9 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 9574
  • View blog
  • Posts: 36,262
  • Joined: 12-June 08

Re: How to count business hours in sql

Posted 24 June 2013 - 06:48 AM

No - I am not clear on what's up. You have five, eight-hour days but that is 48 hours?

Just so I get a handle on it - in my example is that one business hour or a different number?
Was This Post Helpful? 0
  • +
  • -

#10 bcusick  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 26
  • Joined: 27-November 12

Re: How to count business hours in sql

Posted 24 June 2013 - 10:46 AM

5 business days. 4:30-5 would be 30 business minutes. But for international purposes, we will say that the shop is open 24 hours a day, 5 days a week. The only thing I want to exclude is weekends (not worried about holidays).

So, say something happens at 7:30PM on a Friday, the alert won't fire until 7:30PM the following Tuesday. I want to count 24 hours in a day, but only 5 days a week.

I am having trouble trying to come up with a solution that will let this fire on the exact hour (i.e 7:30PM). The example I gave would fire 2 days later at the stroke of midnight (when the day-date changes, but I want to make it more intricate in a sense of waiting to fire until it's been exactly 48 hours (excluding weekends).
Was This Post Helpful? 0
  • +
  • -

#11 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 9574
  • View blog
  • Posts: 36,262
  • Joined: 12-June 08

Re: How to count business hours in sql

Posted 24 June 2013 - 11:31 AM

To be blunt - the whole thing of mixing actual eight hour work days and twenty-four hour days is just.. silly, but what ever. Then of course that doesn't exclude what the heck happens on Saturday or Sunday.. does this fire off (for both days) on Tuesday?

Regardless you can cut past all the cruft with just thinking about this in a case statement.. if today is day X, and I want add two days out from today (excluding Saturday and Sunday) then perhaps I should pay attention to making a special dateadd list of cases.

1: if today is Sunday then what is two business days from now?
2-4: if today is Monday->Wed I can add two days and not have an issue.
5: If today is Thursday and I add two business days - the first takes me to Friday, and the second through two Monday.. so we need to add - ding,ding, ding FOUR days (two plus the two days we look to skip)
6: If today is Friday - we still need to add four, right? I mean that's two business days and two weekends..
7: If today Saturday when are you expecting? Tuesday to have the alarm go off?

See - not a problem, but I would suggest sitting down (with a piece of paper) - or look at a calendar - and just see when you expect to have a notice popup for any given day of the week. Sure it's droll and boring, but with a convoluted system of eight hours being twenty four, etc.. etc.. you can build a quick formula in no time. Critical thinking skills and what not!

declare @dt datetime
set @dt = '06/30/2013 12:00'

select @dt
,DATEADD(d, 2, @dt) as two_days_out_actual
, DATENAME(weekday,@dt)
, datepart(weekday,@dt)
,case datepart(weekday,@dt)
      when 5 then DATEADD(d, 4, @dt) 
      when 6 then DATEADD(d, 4, @dt) 
      when 7 then DATEADD(d, 3, @dt) 
      when 1 then DATEADD(d, 2, @dt) 
      else DATEADD(d, 2, @dt)
      end  as two_days_out_modified
      

Was This Post Helpful? 0
  • +
  • -

#12 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 574
  • View blog
  • Posts: 2,989
  • Joined: 19-May 09

Re: How to count business hours in sql

Posted 30 June 2013 - 09:09 PM

So, is my logic correct or isn't it?
Was This Post Helpful? 0
  • +
  • -

#13 bcusick  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 26
  • Joined: 27-November 12

Re: How to count business hours in sql

Posted 01 July 2013 - 07:20 AM

View PostBobRodes, on 30 June 2013 - 09:09 PM, said:

So, is my logic correct or isn't it?



Yes, that works thanks. Haven't gotten a chance to put the time into the project, but logically your proposal is sound. Thanks for the assisstance. I will post to the topic with results.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1