Query - counts per week?

[G Lykos]

Greetings! Need a little orientation. Have a Data table with a series of
event dates and associated counts on each date. Would like to create a
fixed list of weeks (I set the starting and ending weeks) with counts per
week. Appears that one needed element is to create a Calendar table. Think
I need to JOIN the event dates against the calendar dates, I guess using
Event_Date BETWEEN Date1 AND Date2.

Some questions:
1. What is the basic structure of the SQL query to achieve a simple list of
weeks with event counts in each week?
2. Can Date1 and Date2 come from the calendar table as variables in the SQL
statement? Better yet, can I just define Date1 in the table and have Date2
be calculated as (Date1+6)?
3. Is there an online example of a similar query (or set of queries)?

Thanks for any ideas!
George

 

[Guest]

I do not think you need a calendar of weeks to do what you want. Below is a
query that prompts for start and end dates. It totals the events in each
week between those dates. It displays the week as the Monday date. You can
change to Sunday by editing the ],"w")+2,[ to ],"w")+1,[ if that
is the start of your week.

SELECT DateAdd('d',-Format([EventDates],"w")+2,[EventDates]) AS Week,
Sum(GLykos.Events) AS [Number of Events], [Enter start date] AS [Starting
Date], [Enter end date] AS [Ending Date]
FROM GLykos
WHERE (((GLykos.EventDates) Between [Enter start date] And [Enter end date]))
GROUP BY DateAdd('d',-Format([EventDates],"w")+2,[EventDates]), [Enter start
date], [Enter end date];

 

[Guest]

George:

You'll need a Calendar table if you want to include weeks in the result set
for which there are no dates in the events table. Otherwise you can compute
the weeks along the lines Karl suggests. The following function will create
a basic calendar table for you. Make sure you have a refernce to the
Microsoft ADO Extensions for DDL and Security library as well as the normal
ActiveX Data Objects library (Tools | References on the VBA menu bar)

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 New 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 & _
"(calDate DATETIME, " & _
"CONSTRAINT PrimaryKey PRIMARY KEY (calDate))"
cmd.CommandText = strSQL
cmd.Execute

' refresh database window
Application.RefreshDatabaseWindow

' refresh catalog
cat.Tables.Refresh

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 & "(calDate) " & _
"VALUES(#" & 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 & "(calDate) " & _
"VALUES(#" & Format(dtmDate, "mm/dd/yyyy") & "#)"
cmd.CommandText = strSQL
cmd.Execute
End If
Next varDay
Next dtmDate
End If

Set cmd = Nothing

End Function

So if you wanted to create a table called Calendar to cover this year and
all years up to the end of 2010 say, and include all days of the week in the
table you'd call it like so:

MakeCalendar "Calendar", #01/01/2006#, #12/31/2010#, 0

You can then return the events count with a query which counts the rows in
the events table for each year/week number in the calendar table:

SELECT YEAR(calDate) As Year,
MIN(calDate) As WeekStarting,
DATEPART("ww", calDate) As WeekNumber,
(SELECT SUM(eventCount)
FROM Events
WHERE DATEPART("ww", eventDate) =
DATEPART("ww", Calendar.calDate)
AND YEAR(eventDate) = YEAR(Calendar.calDate)) AS TotalPerWeek
FROM Calendar
GROUP BY YEAR(calDate), DATEPART("ww", calDate);

You can of course include parameters to restrict the result set to a date
range rather than returning all dates in the calendar table.

Ken Sheridan
Stafford, England