March 12, 2009 at 9:28 am
I have to write a function to return a due date. The way the system works is if I have a due time of 180 minutes or 3 hours then I add it to a create date and get the due date back. The problem is the 180 minutes is during business hours of 8:30 AM to 5:30 PM. If the create date is at 4:30 PM and the due time is 3 hours then I use 1 hour the first day (4:30 to 5:30) then the other 2 hours would go to the next business day. In this case the due date would be 8:30 AM + the 2 remaining hours so the due date is 10:30 AM the next business day. The problem I am running into is that I have to account for weekends and holidays. In the above example if we used the first hour on a Friday the due date would be 10:30 AM the next Monday (assuming both Friday and Monday are non-holidays). Any help with this function would be greatly appreciated.
March 12, 2009 at 9:51 am
Are your business hours the same whatever the day?
i.e. is there any such thing as a half-day holiday?
Given that you need to take account of weekends and holidays, you will need a calendar table. If you can eliminate non-working days using a calendar table and the working hours are consistent then the solution becomes quite simple.
March 12, 2009 at 9:54 am
I don't have to worry about half days. All days have the same start and end time. What would the date calculation table look like?
March 12, 2009 at 10:23 am
There are a number of versions for what I've heard called a dates table, a calendar table, or a holidays table. They are prepopulated in advance and come in very handy for date related problems.
A holidays table, or a date table is generally just a table with a date column (which is the primary key). Sometimes it is extended with other information such as the day of the week, what would be the first and last day of the month etc, whether or not a day is a weekday or a holiday, etc, etc. and I've heard these referred to as TimeDimension tables.
For your problem, you only need to know dates of holidays and weekends, so you only need to put those dates into the table. Like so.
create table calendar (cDate datetime not null , daytype char(1) not null)
insert into calendar
select '1/1/2008', 'H' union all
select '1/3/2008', 'W' union all
select '1/4/2008', 'W'
-- etc, etc
alter table calendar
add constraint pk_calendar primary key ( cdate )
select left(cdate,11) as cdate,daytype from calendar
drop table calendar
There are some good discussions close to this topic already. You can also just do a search on "Business Hours", "Workdays", "Calendar", "Date Dimension" or "Time Dimension".
http://www.sqlservercentral.com/articles/Advanced+Querying/calculatingworkdays/1660/
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 12, 2009 at 1:16 pm
[font="Verdana"]Reiterate: expecially where holidays are concerned, use a Calendar table.
Although having said that, holidays can differ depending on location. They get a little tricky.
[/font]
March 12, 2009 at 1:30 pm
Thanks for the help. Luckly (for programming purposes) we only have the major USA holidays off.
March 12, 2009 at 7:26 pm
Using the flavour of Calendar table defined in the previous post by Bob Hovious, the following code will return the due date/time given the create date/time and the due time range in minutes.
DECLARE @createDateTime datetime
DECLARE @dueTimeRange int
SELECT @createDateTime = '20090316 17:26',
@dueTimeRange = 545
DECLARE @workStart int /* start of working day as minutes since midnight */
DECLARE @workEnd int /* end of working day as minutes since midnight */
DECLARE @workRange int /* length of working day in minutes */
SELECT @workStart = DATEDIFF(minute, 0, '08:30'), /* =510 */
@workEnd = DATEDIFF(minute, 0, '17:30'), /* =1050 */
@workRange = @workEnd - @workStart /* =540 */
DECLARE @createDate datetime /* date-only portion of @createDateTime */
DECLARE @createTime int /* time-only portion of @createDateTime */
DECLARE @t int /* due time range in (working) minutes relative to the end of @createDay. */
DECLARE @isWorkDay char(1)
SELECT @createDate = DATEADD(day, DATEDIFF(day, 0, @createDateTime), 0),
@createTime = DATEDIFF(minute, @createDate, @createDateTime)
SELECT @isWorkDay = [dayType] FROM dbo.Calendar WHERE ([cDate] = @createDate)
SELECT @t = @dueTimeRange - (
CASE WHEN (@isWorkDay = 'W') THEN CASE
WHEN (@createTime < @workStart) THEN @workRange
WHEN (@createTime < @workEnd) THEN (@workEnd - @createTime)
ELSE 0 END
ELSE 0 END)
IF (@isWorkDay = 'W' AND @t <= 0 AND @createTime <= @workEnd) BEGIN
/* @t will be negative if the due time falls on the same day as the create date. */
SELECT DATEADD(minute, @workEnd + @t, @createDate)
END
ELSE BEGIN
/* use calendar table */
SELECT DATEADD(minute, (@t - 1) % @workRange + @workStart + 1, MAX([cDate]))
FROM (
SELECT TOP ((@t - 1) / @workRange + 1) [cDate]
FROM dbo.Calendar
WHERE ([cDate] > @createDate)
AND ([dayType] = 'W')
ORDER BY [cDate]) C
END
EDIT: Fixed minor problem with code
March 13, 2009 at 9:26 am
Thanks Andrewd. I almost got it. Only thing I don't have working now is the createdate can be created on the weekend or after 5:30 PM. If this happens the duetime starts at 8:30 am the next work day. I think I can get it from here. Thanks everyone.
March 13, 2009 at 9:58 am
Only thing I don't have working now is the createdate can be created on the weekend or after 5:30 PM. If this happens the duetime starts at 8:30 am the next work day.
I did check this scenario with my code and think that it does start the due time calculation from the beginning of the next working day. There was a problem with my original code if the createDate was after 17:30, but I corrected it later.
Or am I misunderstanding your requirements?
March 13, 2009 at 11:02 am
Maybe I setup my Calandar table wrong. I have 'w' for weekend and 'h' for holiday and no entry in table for a normal work day.
March 13, 2009 at 11:12 am
That would explain it. My Calendar table contains sequential dates and row with dayType = 'W' indicates a working day. Sorry if I didn't make this clear. There are many variations of Calendar tables.
March 13, 2009 at 11:19 am
Thanks again. I been swamped at work and everytime I try to think about the problem I get drag into something else. It does exactly what I need it to do.
March 13, 2009 at 12:14 pm
Hi again,
This version uses a Holiday table, which does not need to include weekends. The presence of a row with a particular date in this table indicates a national holiday on that date.
CREATE TABLE dbo.Holiday (
[Date] datetime NOT NULL PRIMARY KEY
)
This solution also requires a Tally table containing sequential positive integers.
CREATE TABLE dbo.Tally (
N int NOT NULL PRIMARY KEY
)
I think the code behaves correctly but I haven't fully tested it yet
DECLARE @createDateTime datetime
DECLARE @dueTimeRange int
SELECT @createDateTime = '20090316 17:26',
@dueTimeRange = 545
DECLARE @workStart int /* start of working day as minutes since midnight */
DECLARE @workEnd int /* end of working day as minutes since midnight */
DECLARE @workRange int /* length of working day in minutes */
SELECT @workStart = DATEDIFF(minute, 0, '08:30'), /* =510 */
@workEnd = DATEDIFF(minute, 0, '17:30'), /* =1050 */
@workRange = @workEnd - @workStart /* =540 */
DECLARE @createDate datetime /* date-only portion of @createDateTime */
DECLARE @createTime int /* time-only portion of @createDateTime */
DECLARE @t int /* due time range in (working) minutes relative to the end of @createDay. */
DECLARE @createDayIndex int /* number of days between 1900-01-01 and @createDate */
DECLARE @isWorkDay bit /* if working date then 1 else 0 */
SELECT @createDayIndex = DATEDIFF(day, 0, @createDateTime),
@createDate = DATEADD(day, @createDayIndex, 0),
@createTime = DATEDIFF(minute, @createDate, @createDateTime)
SELECT @isWorkDay = CASE
WHEN EXISTS(SELECT 1 FROM dbo.Holiday WHERE ([Date] = @createDate)) THEN 0 /* Holiday */
WHEN ((@createDayIndex % 7) IN (5, 6)) THEN 0 /* Saturday or Sunday */
ELSE 1 END /* Working day */
SELECT @t = @dueTimeRange - (
CASE WHEN (@isWorkDay = 1) THEN CASE
WHEN (@createTime < @workStart) THEN @workRange
WHEN (@createTime < @workEnd) THEN (@workEnd - @createTime)
ELSE 0 END
ELSE 0 END)
IF (@isWorkDay = 1 AND @t <= 0 AND @createTime <= @workEnd) BEGIN
/* @t will be negative if the due time falls on the same day as the create date. */
SELECT DATEADD(minute, @workEnd + @t, @createDate)
END
ELSE BEGIN
/* use tally table */
SELECT DATEADD(minute, (@t - 1) % @workRange + @workStart + 1, DATEADD(day, MAX(C.N), @createDate))
FROM (
SELECT TOP ((@t - 1) / @workRange + 1) T.N
FROM dbo.Tally T
LEFT OUTER JOIN dbo.Holiday H
ON (H.[Date] = DATEADD(day, T.N, @createDate))
WHERE (T.N > 0)
AND ((@createDayIndex + T.N) % 7 NOT IN (5, 6)) /* Exclude weekends */
AND (H.[Date] IS NULL) /* Exclude national holidays */
ORDER BY T.N
) C
END
March 13, 2009 at 3:38 pm
There are a lot of flavors of these kinds of tables, and I've seen much debate about them. If you are limited to problems involving workdays then (in my opinion) you really need to have workdays as rows. Workdays between two dates will always be DATEDIFF(dd,startDate,endDate) minus however many weekend days and holidays appear in the calendar table between those two dates. However, it turns out that for a lot of date calculations, it's faster, and easier, to just have all the results in a prepopulated table with one row for each and every date. I can't squeeze in an exhaustive discussion here, but I will urge you to do some more research in the articles and forums under the topic headings I suggested earlier.
Good luck.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply