In reports is sometimes requested to aggregate transactional data at day level, showing all existing data for a given time interval. This can be easily solved by limiting the time interval in the WHERE clause, though the problem is that it might happen that there are no data in the system for one or more dates and the users want to see that in the report. The easiest approach is to do a left join between the table in which are stored the calendar days and the actual data aggregation, though such a table it’s not always available. What shall we do then? So we need a table-valued function that takes two parameters – the start and end date – and creates all the dates in between. Actually it would be enough to have a sequence of numbers between 0 and the number of days between the two dates, and use the DateAdd function on them. Both problems could be easily solved with the help of common table expressions introduced with SQL Server 2005, while for previous versions could be used a simple loop.
Here’s the multi-statement table valued function supposed to create the sequence of dates:
-- SQL Server 2005+: Date Sequence in Interval
CREATE FUNCTION dbo.fGetDatesInInterval(
@StartDate
date , @EndDate date)
RETURNS @Dates TABLE (
DateSequence date)
AS
BEGIN
DECLARE @Sign smallint
SET @Sign = Sign(DateDiff(d, @StartDate, @EndDate));
WITH CTE(DateSequence)
AS (
SELECT @StartDate DateSequence
UNION ALL
SELECT Cast(DATEADD(d, @Sign, CTE.DateSequence) as Date) DateSequence
FROM CTE
WHERE DATEDIFF(d, CTE.DateSequence, @EndDate)*@Sign>0 )
INSERT @Dates
SELECT DateSequence
FROM CTE
OPTION(MAXRECURSION 0)
RETURN
END
Note:
The OPTION (MAXRECURSION 0) has been introduced in order to avoid the 100 default limit for the number of recursions allowed for a query, a value of 0 equating with an unlimited number of recursions. The function might run also without this option, though if the number of days between the two input dates is greater or equal than 100 then you’ll get the following error:
Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.
For example in order to get the dates within a year the function call would be:
-- days of a given year (positive increment)
SELECT DateSequence
, DATEPART(wk, DateSequence) WeekOfYear
, DATEPART(mm, DateSequence) MonthOfYear
, DATENAME(w, DateSequence) DayOfWeekName
, DATEPART(dw, DateSequence) DayOfWeek
FROM dbo.fGetDatesInInterval('2010-01-01', '2010-12-31')
The DatePart and DateName functions could be used to get the various date parts of a date. As can be seen in this case the EndDate is greater than the StartDate, what happens if the users inverse the two dates? The above function already handles this situation with the help of @Sign variable, the following query in which the two parameters are inversed returning the same date sequence as above:
-- days of a given year (negative increment)
SELECT DateSequence
FROM dbo.fGetDatesInInterval('2010-12-31', '2010-01-01')
The function handles also NULL values, returning the not-NULL value, and in case both parameters are NULL then it returns the current date:
-- second parameter is NULL
SELECT DateSequence
FROM dbo.fGetDatesInInterval('2010-12-31', NULL)
-- first parameter is NULL
SELECT DateSequence
FROM dbo.fGetDatesInInterval(NULL, '2010-12-31')
-- both parameters are NULL
SELECT DateSequence
FROM dbo.fGetDatesInInterval(NULL, NULL)
The above function can’t be used in earlier versions than SQL Server 2005, though the same output could be obtained using a loop – here’s the modified function:
-- SQL Server 2000: Date Sequence in Interval
CREATE FUNCTION dbo.fGetDatesInIntervalOld(
@StartDate
date , @EndDate date)
RETURNS @Dates TABLE
(DateSequence date)
AS
BEGIN
DECLARE @NumberDays int
DECLARE @Counter int
DECLARE @Sign smallint
SET @NumberDays = IsNull(DateDiff(d, IsNull(@StartDate, @EndDate), @EndDate), 0)
SET @Counter = 0
SET @Sign = IsNull(NUllIf(Sign(@NumberDays), 0), 1)
WHILE(@Counter* @Sign<=@NumberDays * @Sign)
BEGIN
INSERT @Dates
SELECT DateAdd(d, @Counter, COALESCE(@StartDate, @EndDate, GetDate()))
SET @Counter = @Counter + @Sign
END
RETURN
END
As can be seen from the below test cases the function returns the same output as the common table expression-based implementation:
-- days of a given year (positive increment)
SELECT DateSequence
FROM dbo.fGetDatesInIntervalOld('2010-01-01', '2010-12-31')
-- days of a given year (negative increment)
SELECT DateSequence
FROM dbo.fGetDatesInIntervalOld('2010-12-31', '2010-01-01')
-- second parameter is NULL
SELECT DateSequence
FROM dbo.fGetDatesInIntervalOld('2010-12-31', NULL)
-- first parameter is NULL
SELECT DateSequence
FROM dbo.fGetDatesInIntervalOld(NULL, '2010-12-31')
-- both parameters are NULL
SELECT DateSequence
FROM dbo.fGetDatesInIntervalOld(NULL, NULL)
Number of Working Days
Given the fact that for each date could be obtained the day of the week, any of the two above functions could be used also to determine the number of (not) working days in a certain time interval:
-- number of (not) working days
SELECT SUM(CASE WHEN DATEPART(dw, DateSequence) BETWEEN 2 AND 6 THEN 1 ELSE 0 END) WorkingDays
, SUM(CASE WHEN DATEPART(dw, DateSequence) IN (1,7) THEN 1 ELSE 0 END) WorkingDays
, COUNT(1) NumberDays
FROM dbo.fGetDatesInIntervalOld('2010-01-01', '2010-12-31')
Even if handy maybe this isn’t the best approach in order to get the number of (not) working days. Some time ago I developed a similar function in VBA, the respective function subtracting the number of weekends falling in the extended time interval. Because I’ve calculated the number of weekends falling in the extended time interval (including first day of the week in which the Start Date falls, respectively the last day of the week in which the End Date falls), I have to add them again – this being the meaning of the two CASE uses.
-- SQL Server 2000+: Date Sequence in Interval
CREATE FUNCTION dbo.fGetWorkingDays(
@StartDate
date , @EndDate date)
RETURNS int
AS
BEGIN
DECLARE @NumberDays int
SET @NumberDays = IsNull(DateDiff(d, IsNull(@StartDate, @EndDate), @EndDate), 0)
RETURN @NumberDays -- difference days
- 2 * (@NumberDays + DatePart(dw, @StartDate) + (7 - DatePart(dw, @EndDate))) / 7 -- number weekends in extended weeks
+ (1 - CASE DatePart(dw, @StartDate) WHEN 1 THEN 1 ELSE 0 END) -- weekend to be added (first week)
+ (1 - CASE DatePart(dw, @EndDate) WHEN 7 THEN 1 ELSE 0 END) -- weekend to be added (last week)
+ 1 -- first day
END
Should be enough to test the week for the various combinations of weekend/working days for 2-3 consecutive weeks:
SELECT dbo.fGetWorkingDays('2010-01-30', '2010-02-13') -- Sa-So (10 days)
SELECT dbo.fGetWorkingDays('2010-01-31', '2010-02-13') -- So-So (10 days)
SELECT dbo.fGetWorkingDays('2010-02-01', '2010-02-13') -- Mo-So (10 days)
SELECT dbo.fGetWorkingDays('2010-02-01', '2010-02-12') -- Mo-Sa (10 days)
SELECT dbo.fGetWorkingDays('2010-01-30', '2010-02-12') -- So-Sa (10 days)
SELECT dbo.fGetWorkingDays('2010-01-31', '2010-02-12') -- So-Sa (10 days)
SELECT dbo.fGetWorkingDays('2010-02-06', '2010-02-12') -- Sa-Sa (5 days)
SELECT dbo.fGetWorkingDays('2010-02-06', '2010-02-13') -- Sa-Sa (5 days)
SELECT dbo.fGetWorkingDays('2010-02-06', '2010-02-11') -- Sa-Th (4 days)
SELECT dbo.fGetWorkingDays('2010-02-07', '2010-02-11') -- So-Th (4 days)
SELECT dbo.fGetWorkingDays('2010-02-06', '2010-02-07') -- Sa-So (0 days)
SELECT dbo.fGetWorkingDays('2010-02-06', '2010-02-08') -- Sa-Mo (1 day)
SELECT dbo.fGetWorkingDays('2010-02-05', '2010-02-08') -- Fr-Mo (2 days)
SELECT dbo.fGetWorkingDays('2010-02-05', '2010-02-05') -- same day (1 day)
In case of doubts the dbo.fGetDatesInIntervalOld could be used to check whether the results are correct. For easier checking of the output of the above test queries here’s the calendar covering the 2-3 weeks considered.
No comments:
Post a Comment