breaking a month into weeks

[Tara]

I need some help with a query that would break a month
into weeks. What I am dealing with are "activities" that
must occur within specific weeks in order for a client to
be "billable" that month. For example a level I client
must have 3 visits all occuring in different weeks OR 2
visits and 2 attempted visits, again in different weeks.
Any idea how I can make the month break into weeks (wk 1,
wk 2, etc.)? It seems like I have seem this somewhere
before but I can't find the example now!

Thanks in advance,
Tara

 

[Tom Ellison]

Dear Tara:

Since a "month" is not exactly composed of "weeks" there will need to
be some additional definition here. For example, if every "week"
starts on Sunday and belongs to the month in which that Sunday falls,
that is a more specific definition. But, it is certainly not the only
possible definition.

Naturally then, every month would contain either 4 or 5 weeks, right?

I would start out with a numerical methodology of identifying every
week. This would be in 3 parts: the year, the month (1-12) and the
week (1-5). I would then write a function that returns this value in
a YYMMWW format. That would provide a value on which to start.

If you query this for a client with the above week value, and do so
DISTINCTly you will then have a list of weeks for any month (a month
is the YYMM portion). By COUNTing them in both visits and attempted
visits, you could then filter (with a HAVING clause) to show which
clients would be billed according to the rules you suggest.

I'm not sure how much additional detail you may need. For an expert,
this could be just a couple of hours work, possibly a bit less. If
this will be a giant learning experience for you then it may take a
couple of days study and work.

We can break it down into pieces and attack them one at a time, now
that we know what those pieces are (assuming you agree on the approach
to be used).

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Tara]

Thanks for the start Tom! I will definately need some
time to work with it as I am by no means an expert. I'll
be away from work for two weeks, but will try your
suggestions when I get back and hopefully I can get
somewhere with it.

Thanks again
Tara

 

[Peter R. Fletcher]

At the risk of emphasising the obvious:

Make sure that your algorithm defines any given week unambiguously, or
at least in a way consistent with your billing rules, particularly if
you are operating on a monthly billing cycle.

The weeks that will potentially cause problems are those which include
the first day of the month within them. You need to ensure that a
visit on (e.g.) Wednesday, December 31, 2003, is recognised as being
in the same week as a visit on Friday, January 2, 2004, even though
(in this case) both the month and the year of the date are different.

My suggestion would be to number weeks in the _year_ from 1 to 52 (you
wil need to go to 53 every few years) and treat each week as being,
for billing purposes, in the month that its _last_ days falls in. If
you bill monthly, the end date for each bill would thus always be
within the previous month.