which weeks betwen 2 dates?

  • Thread starter Thread starter JamesSF
  • Start date Start date
J

JamesSF

how would i get a list of weeks that are between 2 dates?

for example:

between 1/5/04 and 1/30/04 i need a list like this

1/4/04
1/11//04
1/18/04
1/25/04

these dates are the first date of each week that the date range includes.
so even though 1/5 is the start date - 1/4 is the first date of that week

thanks
JamesSF
 
how would i get a list of weeks that are between 2 dates?
Click to expand...

You seem to be looking for Sundays, not weeks. And the Sundays you
seem to be looking for aren't between the two dates you're looking
between. That's a conceptual problem, not a technical one.

I think what you really want is

all the Sundays between
the latest Sunday <= the start date and
the latest Sunday <= the end date

where 2004-01-05 is your start date, and 2004-01-30 is your end date.
If I got that right, now we have a technical problem, not a conceptual
one.
for example:

between 1/5/04 and 1/30/04 i need a list like this

1/4/04
1/11//04
1/18/04
1/25/04
Click to expand...

In standard SQL, I'd probably do something like this.

CREATE VIEW Sundays AS
SELECT * FROM Calendar WHERE ("DOW" = 'Sun');

(Do you use a "calendar" table?)

And then

SELECT "Date" FROM Sundays
WHERE (
("DOW" = 'Sun') AND
("Date" >= (SELECT MAX("Date")
FROM Sundays
WHERE "Date" <= date '2004-01-05')) AND
("Date" <= (SELECT MAX("Date")
FROM Sundays
WHERE "Date" <= date '2004-01-30'))
);

I ran that against Mimer SQL server, and here's what I got.

Date
==========
2004-01-04
2004-01-11
2004-01-18
2004-01-25
 
thanks

Ill work on this today. Its not always sundays, it could be mondays for
example, but i can change the DOW as needed. Yes, we use a calendar table.

thanks
JamesSF
 
Hmm,, jsut thought of something else

I cant use a select query on a table - the calendar table we have only hsa
scheduled events. Im looking for every sunday (or monday) of the weeks
specified in order to check how many events are scheduled. (and, therefore,
not scheduled if there is a minimum requirement.)

so, for example, my calendar table has 6 events per week but only on the
first 3 weeks of the date range. the requirement is for 6 events for 4
weeks. So what i want show is that for the week:

Week Required Scheduled
1/4/04 6 6
1/11//04 6 6
1/18/04 6 6
1/25/04 6 0

so im trying to build a requirements query with a one to many join on the
calendar table to count events by week.

This has got to be easy, im just stumped.

thanks again

JamesSF
 
I cant use a select query on a table - the calendar table we have only hsa
scheduled events.
Click to expand...

I think you *could* use a query (or a join) on a calendar table. (I
did.) It seems that what you have now is a table of events, not a
calendar table.

A calendar table might look like this:

Date Year Month Day DOW DOWOrdinal WeekNum WeekYear
========== ====== ====== ====== === ========== ======= ========
2004-01-21 2004 1 21 Wed 3 4 2004
2004-01-22 2004 1 22 Thu 4 4 2004
2004-01-23 2004 1 23 Fri 4 4 2004
2004-01-24 2004 1 24 Sat 4 4 2004
2004-01-25 2004 1 25 Sun 4 4 2004
2004-01-26 2004 1 26 Mon 4 5 2004
2004-01-27 2004 1 27 Tue 4 5 2004
2004-01-28 2004 1 28 Wed 4 5 2004
2004-01-29 2004 1 29 Thu 5 5 2004
2004-01-30 2004 1 30 Fri 5 5 2004
2004-01-31 2004 1 31 Sat 5 5 2004
2004-02-01 2004 2 1 Sun 1 5 2004
2004-02-02 2004 2 2 Mon 1 6 2004

Use a spreadsheet to generate the data. That's the easiest way.
Im looking for every sunday (or monday) of the weeks
specified in order to check how many events are scheduled. (and, therefore,
not scheduled if there is a minimum requirement.)
Click to expand...

You can join a calendar table to a table of events. (But after reading
ahead, I'm not quite sure whether you actually need to do that.)
Anyway, if I had a table of events that looked like this:

Date Num
========== ======
2004-01-05 1
2004-01-05 2
2004-01-05 6
2004-01-06 3
2004-01-06 5
2004-01-07 4

then I could do this in standard SQL:

SELECT T1."WeekYear", T1."WeekNum", COUNT(T2."Num") AS "NumScheduled"
FROM Calendar T1
LEFT JOIN Events T2 ON (T2."Date" = T1."Date")
WHERE (
(T1."Date" BETWEEN date '2004-01-05' AND date '2004-01-28')
)
GROUP BY T1."WeekYear", T1."WeekNum";

WeekYear WeekNum NumScheduled
======== ======= ============
2004 2 6
2004 3 0
2004 4 0
2004 5 0

(Note that I cheated on the start date and end date to keep the SQL
short and focused on the point, which is the COUNT(), OUTER JOIN, and
GROUP BY. I'm pretty sure everything I've posted so far can be
translated into Access/Jet SQL.)
so, for example, my calendar table has 6 events per week but only on the
first 3 weeks of the date range. the requirement is for 6 events for 4
weeks. So what i want show is that for the week:

Week Required Scheduled
1/4/04 6 6
1/11//04 6 6
1/18/04 6 6
1/25/04 6 0

so im trying to build a requirements query with a one to many join on the
calendar table to count events by week.
Click to expand...

Can you post structure and sample data for the tables you're using?
 
Back
Top