Calculation of business hours from point of time

I have a table which contains the business hours for a week (like following). Business hours definition need not be all weekdays (it may contain few few week days also). Some times it contains all week days.

Monday -- 9:00 AM - 4 PM
TuesDay -- 10:00 AM - 4 PM
Friday -- 8:00 AM - 2 PM.

Now, I have user defined integer value for the approvals. Means if I submit the proofs now admin should approve these proofs by defined value. While submitting proofs I need to send out an email saying that you need to approve the proofs by xxx date.

If I send the file after business hours of any day it should start the count from next business day.

Ex: Above is the business hours data and my defined approval time is 11 hours then I need to send out an email saying that you need to approve the proofs by Friday (with date) 1 P.M...

In the above example if the approve time is 13 hours then I need to say approve by next Monday (with date) 12 P.M

I want to develop this in SQL Server 2008 (query or SP)...

Any help is appriciated....


more ▼

asked Sep 08, 2012 at 07:52 PM in Default

avatar image

10 1 1 1

We can help if you are a little more clear..First what is the actual table definition of the business hours table, is there only one row for the current week? Are you subtracting an hour for lunch each day? It is not clear from your example how Monday at 12pm was reached, when was the proof sent? etc

Sep 08, 2012 at 11:02 PM Scot Hauder
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Here is what I would do. 1) I would create a table that contains 1 row per hour with a identity column, date, start hour, and end hour column. 2) I would then select the row where I am currently at. 3) I would then add the approval integer to the identity column and then select the row with that value. ie) if you are currently at row 320 in the hour table and the approval integer is 13, then just select hour table row 333 and return that date and hour as the approve by values. Does all that make sense?

more ▼

answered Sep 20, 2012 at 12:53 AM

avatar image

180 4 3

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Sep 08, 2012 at 07:52 PM

Seen: 668 times

Last Updated: Sep 20, 2012 at 07:24 AM

Copyright 2018 Redgate Software. Privacy Policy