CODEWITHSUNDEEP
sqlms-accessdate-range
Mohgeroth
Mohgeroth

Reputation: 1627

SQL - Query range between two dates (NON-VBA)

I see various topics on this around stack overflow but none that fit the contect of MS-Access...

Given a starting date and an ending date, is there a way through SQL to return records for each given month within the time frame?

EG:

A record has a Start Date of #1/1/2010# and an End Date of #1/31/2010#

Running the query against that single record, I would like the results to be
            #1/4/2010#   (Monday the 4th)
            #1/11/2010#  (Monday the 11th)
            #1/18/2010#   ...etc
            #1/25/2010#

Restrictions

Is this possible? I see many no's, but if there was a way to pass a value into a function I could find a way to make this work. Sad that I don't have a way to simulate a stored procedure without using a d/c recordset, at least that I know of... any experts out there know a way?

Upvotes: 1

Views: 2781

Answers (4)

Praesagus
Praesagus

Reputation: 2094

I hope I understand your question correctly. To to this we need to create a cartesian product - so you should create another table. It only needs an ID in it with numbers that go from 1 to the highest number of weeks that your date range will contain.

For example table: tCounter(CounterID int)
CounterID
1
2
3
4
etc..

for test data I created a mock table with your data 

For example: tDates(DateID int, startdate datetime, enddate datetime)
DateID, Startdate, EndDate
1,  1/1/10      1/31/10
2,  1/18/10     3/4/10
3,  2/1/10      2/28/10

the msaccess sql statement that will produce your results looks like

SELECT tDates.DateID, ((7-Weekday([startdate],3))+[startdate])+(([CounterID]-1)*7) AS NewDay
FROM tDates, tCounter
WHERE (((((7-Weekday([startdate],3))+[startdate])+(([CounterID]-1)*7))<=[enddate]))
ORDER BY tDates.DateID, ((7-Weekday([startdate],3))+[startdate])+(([CounterID]-1)*7);

If you want to change the day from Monday to another day, change the argument in the weekday function. 1=Sunday->7=Saturday

e.g. Weekday([startdate],3)...the 3=Tuesday so result is Monday 
e.g. Weekday([startdate],5)...the 5=Thursday so result is Wednesday

Hope that works for you.

Upvotes: 0

Unreason
Unreason

Reputation: 12704

Since this is probably related to reporting you could use temporary table approach, that is not so bad, especially if you might be running multiple reports against a given period (but do take care about multiple users).

Now, just for fun, here's a super-ugly, but semi-flexible approach (uses table with 10 entries and works for a range of 273 years)

Make a table called t10 with one field - id (make it long and primary key) and enter ten rows: 0,1,2,3..9

After that

SELECT #1/1/2010#+
       [t10].[ID]+
       [t100].[iD]*10+
       [t1000].[ID]*100+
       [t10000].[ID]*1000+
       [t100000].[iD]*10000 AS Mondays,
FROM t10, 
     t10 AS t100, 
     t10 AS t1000, 
     t10 AS t10000, 
     t10 AS t100000
WHERE  Weekday(#1/1/2010#+
               [t10].[ID]+
               [t100].[iD]*10+
               [t1000].[ID]*100+
               [t10000].[ID]*1000+  
               [t100000].[iD]*10000)=2;

returns all Mondays from 2010-1-1 to 2283-10-15 and is not as slow as it is ugly. Of course to get a solution for your question you'll have to filter the results for your fields date1 and date2.

This is in essence same solution as having temporary table, with the main difference that you don't have to rebuild the table on each query.

You could achieve the same result with only one table with entries 0..99999, and if i was to use temp tables I would probably use something like that.

Upvotes: 1

Philippe Grondier
Philippe Grondier

Reputation: 11138

You are talking about a query that generates multiple records (one per date) out of a single record. I guess this should be made through a stored procedure (if your database is SQL) or through recordsets. No query will do the trick. Creating a recordset and adding records where needed seems quite straightforward to me. You should go for it.

Upvotes: 0

Knox
Knox

Reputation: 2919

You could create a giant calendar table, with dates and days in it. 4/12/10, Monday; 4/13/10, Tuesday, etc. Then query that giant table for the results. How big of range in the past and future do you need? Even though this is a clumsy solution, it would certainly make the queries clean and simple.

Upvotes: 1

Related Questions