20 February 2010

SQL Reloaded: More Date-related Functionality I

Date Intervals Generation 
 
     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.
Date Calendar

No comments:

Related Posts Plugin for WordPress, Blogger...

About Me

My photo
IT Professional with more than 24 years experience in IT in the area of full life-cycle of Web/Desktop/Database Applications Development, Software Engineering, Consultancy, Data Management, Data Quality, Data Migrations, Reporting, ERP implementations & support, Team/Project/IT Management, etc.