May 28, 2010 at 9:42 am
I have a time dimension table in my master database that I use as a multi-purpose date table. I use it as a tally table for dates, as well as to support several date-based user-defined functions. There is far too much code required to recreate the table to try and reproduce here, but the table contains every date from 1990-2050 (I could expand it if I needed to), along with a large number of attributes for each date (day of week/month/year, month/quarter/year, fiscal quarter/fiscal year, isWeekday/isHoliday).
The most recent addition was the isHoliday field. This allowed me to calculate business days omitting holidays for a scheduling process that scheduled in business days, where nothing should be scheduled for a holiday. The holidays themselves were calculated based on fixed days (12/25) or weekday occurance in a month (last thursday in April, last monday in May, et cetera).
With this calendar, it was fairly easy to create a DATEDIFF function that returned business days rather than calendar days:
ALTER FUNCTION [date].[ufnGetBusinessDays](
@startdate DATETIME,
@enddate DATETIME
)
RETURNS INT
AS
BEGIN
DECLARE @return INT
SELECT @return = COUNT(*) - SUM(Counter) - 1 FROM
(SELECT
Date,
CASE WHEN isWorkingDay = 1 THEN 0 ELSE 1 END AS Counter
FROM dim.Time WHERE Date BETWEEN master.dbo.fn_DATE_FirstDayOfDay(@startdate, 6) AND master.dbo.fn_DATE_FirstDayOfDay(@enddate, 6)) A
RETURN @return
END
This works very well. But the best code I've come up with for a DATEADD business day function ended up requiring a cursor:
ALTER FUNCTION [date].[ufnAddBusinessDays](
@date DATETIME,
@business_days INT
)
RETURNS DATETIME
AS
BEGIN
DECLARE @datecounter DATETIME
DECLARE @daycounter INT
SET @datecounter = master.dbo.fn_DATE_FirstDayOfDay(@date,6)
SET @daycounter = 0
WHILE @daycounter < @business_days
BEGIN
SET @datecounter = DATEADD(dd,1,@datecounter)
IF (SELECT isWorkingDay FROM dim.Time WHERE Date = @datecounter) = 1
SET @daycounter = @daycounter + 1
END
RETURN @datecounter
END
The problem is that the addition doesn't scale well. Adding 5 business days is easy and quick. Adding 1000 business days is far less quick. If I program error causes it to pick something out of range, it just hangs for a long period of time.
I'd like something that uses set-based operations, but I need to add some number of days to DATEADD(dd,@businessdays,@date) before joining with dim.Time. I presume I would need to perform a calculation on every row and compare that number to @businessdays and return the date for the row where they are equal. If I choose too many rows, I perform unneccessary calculations. If I choose too few, the date won't be part of the set and it will error. I'm not sure how I would select x number of days to add. Since we have exactly 8 holidays every year, perhaps adding make x = 8 * @businessdays/254 (approximate number of business days per year). Picking a few extra rows shouldn't be too inefficient...
Can someone help me arrive at a query that will do what I am looking for? I am assuming set-based operation is more efficient than the cursor, but will that hold true in this operation. It seems expensive to perform a calculation for every row if @businessdays is large. But then, the cursor is probably much more expensive.
--J
May 28, 2010 at 11:00 am
Hi J,
It really does help to have some test data to work against. See the first link in my signature for how to do this.
But, to help you out, here are 10,000 dates since 19991231. Some are being marked as "IsHoliday".
if OBJECT_ID('dbo.Dates') IS NOT NULL DROP TABLE dbo.Dates
-- See Jeff Moden's article The "Numbers" or "Tally" Table: What it is and how it replaces a loop.
-- at http://qa.sqlservercentral.com/articles/T-SQL/62867/.
;WITH
Tens (N) AS (SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0),
Thousands(N) AS (SELECT 1 FROM Tens t1 CROSS JOIN Tens t2 CROSS JOIN Tens t3),
Millions (N) AS (SELECT 1 FROM Thousands t1 CROSS JOIN Thousands t2),
Tally (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM Millions)
SELECT [Date] = DateAdd(day, N, '19991231'), IsHoliday = CONVERT(bit, 0)
INTO dbo.Dates
FROM Tally
WHEREN between 1 and 10000
UPDATE dbo.Dates
SET IsHoliday = CASE WHEN MONTH([Date]) = 12 and DAY([Date]) = 25 THEN 1 -- Christmas - Dec 25th
WHEN MONTH([Date]) = 7 and DAY([Date]) = 4 THEN 1 -- July 4th
WHEN MONTH([Date]) = 1 and DAY([Date]) = 1 THEN 1 -- Jan 1st
WHEN MONTH([Date]) = 11 and DAY([Date]) = 11 THEN 1 -- Veteran's day - Nov 11th
ELSE 0
END
Now, using the above data, let's create some variables:
declare @TestDate datetime,
@DayInterval int
set @TestDate = '20000501'
set @DayInterval = 1000
Get the 1000th date after the specified date:
select DATEADD(day, @DayInterval, @TestDate)
And finally, get the 1000th day that isn't a holiday after the specified date:
SELECT [Date]
FROM (SELECT [Date], N = ROW_NUMBER() OVER (ORDER BY [Date])
FROM dbo.Dates
WHERE IsHoliday = 0
AND [Date] > @TestDate ) a
WHERE N = @DayInterval
You can easily make this into an in-line table-valued function (ITV):
CREATE FUNCTION dbo.GetBusinessDay(@DayInterval int, @TestDate datetime)
RETURNS TABLE
AS
RETURN
(SELECT [Date]
FROM (SELECT [Date], N = ROW_NUMBER() OVER (ORDER BY [Date])
FROM dbo.Dates
WHERE IsHoliday = 0
AND [Date] > @TestDate ) a
WHERE N = @DayInterval)
GO
which would be called by:
select [Date]
from dbo.GetBusinessDay(1000, '20000501')
Check out the link for "Using APPLY" in my signature to see how to combine this with data in other tables.
Edit: corrected name misspelling
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply