5.17 Using Calendar Information with Durations

5.17.1 Problems

You want to find out which day was 15 working days after January 1, 2001 to correctly determine the end date for a 15-day project. You must not count weekends and holidays.

5.17.2 Solution

Combine the calendar code shown earlier in the Section 5.13 and Section 5.14 recipes with a date stream, count the working days from the date of interest, and report the one that is 15 days out:

SELECT TOP 1 
   CAST(DATEADD(day,p2.i,'2001-1-1') AS CHAR(12)) Date
FROM Pivot p1, Pivot p2
WHERE 
   p1.i<=p2.i AND
   DATEPART(weekday,
      DATEADD(day,p1.i,'2001-1-1')) BETWEEN 2 AND 6 AND
   NOT EXISTS(SELECT * FROM Holidays 
      WHERE holiday=DATEADD(day,p1.i,'2001-1-1')) 
GROUP BY p2.i
HAVING COUNT(*)=15
ORDER BY DATEADD(day,p2.i,'2001-1-1')

Date         
------------ 
Jan 23 2001

In this example, the project plan assumes 15 working days for the project, which is scheduled to start on January 1, 2001, so the job ends on January 23, 2001.

5.17.3 Discussion

Because we have to count 15 working days from a given date, our query has to implement some sequencing function. Otherwise, it's not possible in Transact-SQL to retrieve the Nth row from a result set without using a cursor. The need for a sequence is at the root of the Pivot table's self-join.

The query joins two instances of the Pivot table, which are aliased as p1 and p2. p2 is used to generate a sequence of dates, while p1 is used to generate, for each p2 date, all the dates that are less than the p2 date. The results are then grouped by p2.i, making the COUNT(*) value for each group into our counter. Look, for example, at the output from a slightly modified version of the query that omits the GROUP BY and HAVING clauses. A word of caution: this query is very expensive, though instructive, so give it a little time to return the result:

SELECT
   CAST(DATEADD(day,p2.i,'2001-1-1') AS CHAR(12)) p2i,
   CAST(DATEADD(day,p1.i,'2001-1-1') AS CHAR(12)) p1i
FROM Pivot p1, Pivot p2
WHERE 
   p1.i<=p2.i AND
   DATEPART(weekday,
      DATEADD(day,p1.i,'2001-1-1')) BETWEEN 2 AND 6 AND
   NOT EXISTS(SELECT * FROM Holidays 
      WHERE holiday=DATEADD(day,p1.i,'2001-1-1'))
ORDER BY DATEADD(day,p2.i,'2001-1-1')

p2i          p1i          
------------ ------------ 
Jan  2 2001  Jan  2 2001 
Jan  3 2001  Jan  2 2001 
Jan  3 2001  Jan  3 2001 
Jan  4 2001  Jan  2 2001 
Jan  4 2001  Jan  3 2001 
Jan  4 2001  Jan  4 2001
...

As you can see, we get one row for January 2 (because that is one day past January 1), two rows for January 3 (because that is two days past January 1), three rows for January 4, and so forth. These are all working days. The clauses in the WHERE clause have already eliminated weekends and holidays using techniques you've seen in previous recipes. Group these results by p2i, the date in the first column, and you get the following:

SELECT 
   CAST(DATEADD(day,p2.i,'2001-1-1') AS CHAR(12)) p2i,
   COUNT(*) days_past
FROM Pivot p1, Pivot p2
WHERE 
   p1.i<=p2.i AND
   DATEPART(weekday,
      DATEADD(day,p1.i,'2001-1-1')) BETWEEN 2 AND 6 AND
   NOT EXISTS(SELECT * FROM Holidays 
      WHERE holiday=DATEADD(day,p1.i,'2001-1-1'))
GROUP BY p2.i
ORDER BY p2.i

p2i          days_past   
------------ ----------- 
Jan  2 2001  1
Jan  3 2001  2
Jan  4 2001  3
...

Now, it's simply a matter of using a HAVING clause (HAVING COUNT(*)=15) to restrict the output to those days on which the days_past count is 15, and then using TOP 1 to return the first such date. Days on which days_past=15 are those that are 15 working days past January 1, 2000 (counting January 1, 2000 as one of the working days). Because of weekends and holidays, there can actually be more than one such date. Our query ensures that the first such date will be a working day, because only working days advance the counter; the ORDER BY clause sorts the results in date order.