A device frequently used in SQL programming when handling dates is to create
‘calendar’ tables, which usually contain rows of serial dates over a number
of years, but can also have other columns useful for processing the data. In
your case you could create a table of all weekday dates numbered serially.
To make this easy to do I wrote the following function, which you can paste
into a standard module in your database. Make sure that you have a reference
to the Microsoft ADO Extensions for DDL and Security as well as the normal
ActiveX Data Objects library (Tools|References on the VBA menu bar).
''''code begins''''
Public Function MakeCalendar(strTable As String, _
dtmStart As Date, _
dtmEnd As Date, _
ParamArray varDays() As Variant)
' Accepts: Name of calendar table to be created: String.
' Start date for calendar: DateTime.
' End date for calendar: DateTime.
' Days of week to be included in calendar
' as value list, e.g. 2,3,4,5,6 for Mon-Fri
' (use 0 to include all days of week)
Dim cmd As ADODB.Command
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim strSQL As String
Dim dtmDate As Date
Dim varDay As Variant
Dim lngDayNum As Long
Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText
Set cat = New Catalog
cat.ActiveConnection = CurrentProject.Connection
' does table exist? If so get user confirmation to delete it
On Error Resume Next
Set tbl = cat(strTable)
If Err = 0 Then
If MsgBox("Replace existing table: " & _
strTable & "?", vbYesNo + vbQuestion, _
"Delete Table?") = vbYes Then
strSQL = "DROP TABLE " & strTable
cmd.CommandText = strSQL
cmd.Execute
Else
Set cmd = Nothing
Exit Function
End If
End If
On Error GoTo 0
' create new table
strSQL = "CREATE TABLE " & strTable & _
"(dayNum LONG, calDate DATETIME, " & _
"CONSTRAINT PrimaryKey PRIMARY KEY (calDate))"
cmd.CommandText = strSQL
cmd.Execute
If varDays(0) = 0 Then
' fill table with all dates
For dtmDate = dtmStart To dtmEnd
lngDayNum = lngDayNum + 1
cmd.CommandText = strSQL
strSQL = "INSERT INTO " & strTable & "(dayNum, calDate) " & _
"VALUES(" & lngDayNum & ", #" & Format(dtmDate, "mm/dd/yyyy")
& "#)"
cmd.CommandText = strSQL
cmd.Execute
Next dtmDate
Else
' fill table with dates of selected days of week only
For dtmDate = dtmStart To dtmEnd
For Each varDay In varDays()
If Weekday(dtmDate) = varDay Then
lngDayNum = lngDayNum + 1
cmd.CommandText = strSQL
strSQL = "INSERT INTO " & strTable & "(dayNum, calDate)
" & _
"VALUES(" & lngDayNum & ", #" & Format(dtmDate,
"mm/dd/yyyy") & "#)"
cmd.CommandText = strSQL
cmd.Execute
End If
Next varDay
Next dtmDate
End If
Set cmd = Nothing
End Function
''''code ends''''
This allows you to create calendar tables containing all or selected days of
the week, so to create one of weekdays called WeekdayCal covering 2005 – 2010
for instance call I like so:
MakeCalendar "WeekdayCal",#01/01/2005#, #12/31/2010#, 2, 3, 4, 5, 6
You can do this simply by entering the above line in the debug window (AKA
Immediate pane) once you’ve put the function in a module. BTW make sure you
save the module with a different name form that of the function, e.g.
mdlCalendarStuff.
Armed with this table you can now join it to your table of absences, which
I’ve assumed is called Absences and has columns EmployeeID and AbsenceDate,
in a query like so:
SELECT EmployeeID, AbsenceDate, DayNum
FROM Absences INNER JOIN WeekDayCal
ON Absences.AbsenceDate=WeekDayCal.calDate;
Save this as qryAbsencesNumbered. You’ll see that its result set contains
numbers for the days of absence which run in sequence, skipping the weekends.
This enables you to make use of a class of queries which deal with what are
known as Regions. These are subsets of rows which are contiguous in the sort
order determined by, in this case, the dayNum column and having the same
value, in this case the EmployeeID column. Various solutions to different
types of problems of this nature have been published over the years and here
I’m making use of a method developed by Joe Celko and described in his book
‘SQL for Smarties’. This uses 3 instances of the table (the result set of
the above query in this case) and works by identifying the missing numbers
before and after each sequence, along with the fact that there can be no gaps
within the sequence. The query to do this is as follows:
SELECT Q1.EmployeeID, Q1.AbsenceDate AS Start, MIN(Q2.AbsenceDate) AS End,
MIN(Q2.DayNum)-Q1.DayNum+1 AS DaysAbsent
FROM qryAbsencesNumbered AS Q1, qryAbsencesNumbered AS Q2
WHERE Q1.EmployeeID = Q2.EmployeeID AND Q1.DayNum <= Q2.DayNum
AND NOT EXISTS
(SELECT *
FROM qryAbsencesNumbered AS Q3
WHERE Q3.EmployeeID = Q1.EmployeeID
AND Q3.DayNum NOT BETWEEN Q1.DayNum AND Q2.DayNum
AND (Q3.DayNum =Q1.DayNum -1 OR Q3.DayNum = Q2.DayNum +1))
GROUP BY Q1.EmployeeID, Q1.DayNum, Q1.AbsenceDate;
To get the number of instances of absences per employee you’d just count the
rows per employee, which you could do with another query based on the one
above, or you can do it all in one query:
SELECT EmployeeID, COUNT(*) AS NumberOfAbsences
FROM
[SELECT Q1.EmployeeID,Q1.AbsenceDate AS Start, MIN(Q2.AbsenceDate) AS End
FROM qryAbsencesNumbered As Q1,qryAbsencesNumbered As Q2
WHERE Q1.EmployeeID = Q2.EmployeeID
AND Q1.DayNum <= Q2.DayNum
AND NOT EXISTS
(SELECT *
FROM qryAbsencesNumbered AS Q3
WHERE Q3.EmployeeID = Q1.EmployeeID
AND Q3.DayNum NOT BETWEEN Q1.DayNum AND Q2.DayNum
AND (Q3.DayNum =Q1.DayNum -1 OR Q3.DayNum = Q2.DayNum +1))
GROUP BY Q1.EmployeeID, Q1.AbsenceDate]. AS AbsenceRanges
GROUP BY EmployeeID;
Ken Sheridan
Stafford, England
