Identify the week of the month

[Guest]

Hi I am looking for a query that I can use to total al calls which came in on
the 1st or 2nd week of a group of Months.

example
Total calls:1000
_____________________
Total By Month: Jan 500
Feb 500
_____________________
Totals By week: 1 300
2 250
3 200
4 250

 

[Guest]

need a little more information! Table structure would be nice.

How are you defining the week? 1-7, 8-14, ... or by the days of the week
(Sun-Sat)? If the latter, how are you deciding whether a week that contains
the last day of one month and the first days of another?

Dale

 

[Guest]

I have a field in my table called start date. This filed captures the date
and time a call begins. from this field i would like to total all of the
calls that come in on the 1st week of any month. the 1st week of a month
would begin on the 1st Sunday of the month. So if July 3rd is the 1st sunday
that will be the 1st day of the 1st week of my month.

 

[Guest]

Ramon,

The problem is that there are no functions that define a month or week the
way you want to define them. So I wrote a series of three function
fnMyMonth, fnMyWeek, and fnFirstSunday (see below-watch out for line breaks)
so that you can determine what Month, and Week your dates are in. Save these
functions in a code module, then write a query that would look something like
(assumes your table has a PK field called ID):

SELECT Year([Start Date]) as CallYear,
fnMyMonth([Start Date]) as CallMonth,
fnMyWeek([Start Date]) as CallWeek,
Count(ID) as CallsPerWeek
FROM yourTable
GROUP BY Year([Start Date]),
fnMyMonth([Start Date]),
fnMyWeek([Start Date])

If you are really only concerned with the first week of each month, you can
add a WHERE clause to restrict the CallWeek value to 1.

HTH
Dale

Public Function fnMyMonth(ByVal SomeDate As Date) As Integer

Dim dtFirstSunday As Date

dtFirstSunday = fnFirstSunday(SomeDate)
fnMyMonth = Month(SomeDate) + (SomeDate < dtFirstSunday)

End Function

Public Function fnMyWeek(ByVal SomeDate As Date) As Integer

Dim dtFirstSunday As Date

dtFirstSunday = fnFirstSunday(SomeDate)

'If the first Sunday is after the date passed, then the date
'falls in the previous months week count
If dtFirstSunday > SomeDate Then
dtFirstSunday = fnFirstSunday(DateAdd("m", -1, SomeDate))
End If

fnMyWeek = Int(DateDiff("d", dtFirstSunday, SomeDate) / 7) + 1

End Function
Public Function fnFirstSunday(ByVal SomeDate As Date) As Date

'Accepts a date and returns the first Sunday of that month
Dim intLoop As Integer

'The first Sunday has to fall sometime in the first 7 days of the month
For intLoop = 1 To 7
If Weekday(DateSerial(Year(SomeDate), Month(SomeDate), intLoop),
vbSunday) = 1 Then
fnFirstSunday = DateSerial(Year(SomeDate), Month(SomeDate), intLoop)
Exit Function
End If
Next

End Function