In a previous article, we created a calendar table for a large number of dates using a tally table. In addition to the dates, we added a number of fields that flagged specific attributes for each date. In this article, we will examine some practical queries that make use of the various flags in our calendar table.

You can read more about tally tables in The "Numbers" or "Tally" Table: What it is and how it replaces a loop

Choosing the Attributes

In our first article, we chose to include these columns as attributes of the dates in the Calendar table:

• calendar day (DayNo)
• month number (MonthNo)
• year (YearNo)
• day of the week (DayofWeek)
• last day of the month (EndOfMonth)
• whether the day is a weekend (Weekend)
• whether the day is a holiday (Holiday)
• week of the year (WeekNoAlt)
• the ordinal position (1^st,2^nd,3^rd,4^th) of a given DayofWeek  (DoWAsc)
• flag for last DayofWeek for the month (DoWInMonth)

Each of these columns has some value. For example, we may use the flag for weekends for quickly determining shipping or receiving times. Some delivery companies do not deliver on weekends, so we would want the next date after some date flagged as a weekend.

If you are unsure of what the calendar table looks like, be sure to review the previous article.

Why not just use date functions?

This is a fair question. After all we used those same date functions to define the columns of the Calendar table. But consider this example. Back in 2014 the following query would have produced that date for Thanksgiving Day.

SELECT DATEADD( DAY, 3, DATEADD( WEEK, -1, DATEADD( MONTH, 11, DATEADD(yy, datediff(yy, 0, GETDATE()), 0))));

If any newcomers would like to see an explanation of all those nested functions, please ask in the discussion section of this article. By comparison, the query from a calendar table would be

SELECT CalendarDate as Thanksgiving
FROM dbo.Calendar
WHERE YearNo = 2014
  and LastDoWInMonth = 1          -- Last
  and DayOfWeek = 5               -- Thursday
  and MonthNo = 11                -- In November

Which is easier to read and understand? Which would be easier to create? And one more thing: which one works for all for all years? Unfortunately, the SELECT DATEADD query doesn’t work every year. So, both simplicity and reliability favor Calendar table queries.

Practical Queries

Let's look at some queries to answer real life questions clients or bosses might have. For example, imagine that you are looking at an employee's work schedule. Suppose we want to verify that the dates the employee worked are correct. We might ask the question:  How many workdays fall between June 27 and July 19th, 2016?

SELECT SUM(1) as Workdays
FROM dbo.Calendar
WHERE CalendarDate BETWEEN '6/27/2016' and '7/19/2016'
  AND WorkDay = 1

When we run this, we get 16 days. However, suppose we want to compare this to the data in the EmployeeSchedule table to determine if all the days an individual worked were actually marked as workdays. We could do this:

SELECT es.EmployeeID
     , es.DateWorked
     , c.Workday
     , c.Weekend
     , c.Holiday
FROM dbo.Calendar c
  INNER JOIN EmployeeSchedule es
    ON c.CalendarDate = es.DateWorked
WHERE CalendarDate BETWEEN '6/27/2016' and '7/19/2016'

This might be of use in finding simple mistakes in recordkeeping, or perhaps even fraud. If a business isn't open on weekends and holidays, we would not normally expect hours to be posted for those days. It might be possible for inventory or maintenance work to be scheduled, but this is information that should be brought to management’s attention. If valid, this could still be useful in calculating the employees’ pay if they get overtime for weekdays and holidays.

Scheduling Work

Perhaps we need to estimate whether or not we can complete a job by a given date. We've estimated that 9 workdays are going to be needed and the client says we can’t start until June 28^th. The following query would list the dates we could work, omitting weekdays and holidays, and tell us that work could not be completed before Monday July 11^th.

DECLARE @JobCosting TABLE (JobNo int, EstWorkdays int, StartDate date)
INSERT INTO @JobCosting 
    VALUES (1,9,'6/28/2016')
SELECT CalendarDate as Workday, DATENAME(DW,CalendarDate) as WorkDayName
FROM @JobCosting j
CROSS APPLY (SELECT Top(EstWorkDays)  CalendarDate
             FROM dbo.Calendar c
             WHERE CalendarDate >= StartDate
               and WorkDay = 1
             ORDER BY CalendarDate) C1

Perhaps we use a six-day workweek (including Saturdays but excluding Sundays and holidays), how many workdays fall between June 27 and July 19. We can easily find out like this.

SELECT CalendarDate as Workdays
FROM CALENDAR
WHERE CalendarDate BETWEEN '6/27/2016' and '7/19/2016'
  AND [DayofWeek] <> 1
  AND Holiday = 0

Suppose you work for Scrooge and Marley. Weekdays and holidays? Bah!  Humbug!  Count yourself fortunate, Bob Cratchit, that I don’t work you on the Sabbath. And no more coal for the stove!

SELECT SUM(1) as Workdays
FROM CALENDAR
WHERE CalendarDate BETWEEN '12/20/2016' and '1/5/2017'
  AND [DayofWeek] <> 1
  -- AND Holiday = 0

Or maybe we run a restaurant that closes on Sunday and Monday. How many days could we be open in February?

SELECT SUM(1) as Workdays
FROM CALENDAR
WHERE YearNo = 2016
  AND MonthNo = 2
  AND [DayofWeek] NOT IN (1,2)
  AND Holiday = 0

Finding All the Meetings

I'm sure most of you don't love meetings, but sometimes we need to schedule a few. Suppose we're scheduling meetings for the second half of 2016, and we want to print out a calendar. Which dates fall on every 2nd and 4th Wednesday?

  SELECT *
  FROM dbo.Calendar 
  WHERE YearNo = 2016
    and MonthNo > 6
    and [DayofWeek] = 4
    and DoWAsc in (2,4)
  ORDER BY CalendarDate

We changed our mind about when and how often to meet. Which dates fall on the last Friday of every month?

SELECT *
FROM dbo.Calendar 
WHERE YearNo = 2016
  and MonthNo > 6
  and [DayofWeek] = 6
  and LastDoWInMonth = 1 
ORDER BY CalendarDate

Total Hours

While we usually need some accurate record keeping system, we might want estimates for prediction or even some KPI dashboard. The #employeetime table has dates and hours worked. For each employee total up their hours per alternate week (7 day periods which always start on January 1^st ).

IF OBJECT_ID(N'tempdb..#employeetime') is not null DROP TABLE #employeetime
CREATE TABLE  #employeetime (EmployeeID int, WorkDate date, Intime time, OutTime time) 
Insert into #employeetime
VALUES (1001, '1/3/2016', '8:00', '17:00'),
  (1001, '1/4/2016', '8:00', '17:00'),
  (1001, '1/5/2016', '8:00', '17:00'),
  (1001, '1/6/2016', '8:00', '17:00'),
  (1001, '1/7/2016', '8:00', '17:00'), -- end of 7 days starting January 1
  (1001, '1/8/2016', '8:00', '17:00'),
  (1001, '1/9/2016', '8:00', '17:00')
  
SELECT t.EmployeeID, c.WeekNoAlt
      ,SUM(DATEDIFF(hour,InTime,OutTime)-1) as TotalHours
FROM #employeetime t
JOIN dbo.Calendar c on t.WorkDate = c.CalendarDate
GROUP BY T.EmployeeID, WeekNoAlt
-- Or, even more detailed 
SELECT t.EmployeeID, c.WeekNoAlt
      ,SUM(DATEDIFF(hour,InTime,OutTime)-1) as TotalHours
      ,SUM(CASE WHEN Workday = 1 THEN DATEDIFF(hour,InTime,OutTime)-1
           ELSE 0 END
       ) as WorkdayHours
      ,SUM(CASE WHEN Weekend = 1 THEN DATEDIFF(hour,InTime,OutTime)-1
           ELSE 0 END
       ) as WeekendHours
     ,SUM(CASE WHEN Holiday = 1 THEN DATEDIFF(hour,InTime,OutTime)-1
           ELSE 0 END
       ) as HolidayHours
FROM #employeetime t
JOIN dbo.Calendar c on t.WorkDate = c.CalendarDate
GROUP BY T.EmployeeID, WeekNoAlt

Conclusion

While many of these queries can be duplicated with nested date functions, nesting functions may cause queries to be complex, hard to understand, and hard to maintain in T-SQL. As we have seen, queries against a calendar table are simpler to create, understand, and maintain than queries using nested date functions. They also make it much simpler to account for holidays, for which there is no date function. Joining to a calendar table can greatly simplify your coding. Finally, you or anyone else looking at the code in the future should easily be able to understand what dates the query is intended to produce.