asked
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
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
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?
9 People are following this question.
