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 (1st,2nd,3rd,4th) 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.
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.
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 28th. 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 11th.
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
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 1st ).
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
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.