x

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....

Thanks
more ▼

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

srini4573 gravatar image

srini4573
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

jjaroska gravatar image

jjaroska
170 2

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x10

asked: Sep 08, 2012 at 07:52 PM

Seen: 553 times

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