Displaying all dates between two dates

[The Stoat]

Hi all,


Just to be clear i don't mean a Between statement


I've come up against a problem and i'm really not sure what the correct solution is.

I want to store the leave dates for staff. The most space efficient way seems to be just to store the start and end dates of the leave period.
It also makes it easy to validate.

However I need to report on leave taken on any given day, week, or month which means being able to extract the individual days between the two dates.

This is bl***y difficult/messy to do using the start date and end date format.

I can do it simply if i store all the individual days taken as leave but the amount of data stored would be huge.

I did find this on the net for an Oracle db but i can't quite get my head round it.

Apparently rownum is a virtual field that oracle creates for all tables that incrementally counts the number of rows in the table. I'm not sure how that helps here but...

SELECT TO_DATE('12/01/2003', 'MM/DD/YYYY') - 1 + rownum AS d
FROM all_objects
WHERE TO_DATE('12/01/2003', 'MM/DD/YYYY') - 1 + rownum <= TO_DATE('12/05/2003', 'MM/DD/YYYY')

....my best approximation for access is where rownum should be an integer that increments by 1 for each pass. So i could put this in a field in a query

rownum: (Select Count (*) FROM [TBL_LEAVE] as Temp WHERE [Temp].[LEAVE_ID] < [TBL_LEAVE].[LEAVE_ID])+1

And then use it like this - in the same query

SELECT dateadd("d" ,-1 ,[start_date]) + rownum  AS d
FROM TBL_LEAVE
WHERE dateadd("d", -1 ,[start_date]) + rownum <= [end_date]

Trouble is it doesn't work. It keeps asking me for rownum

Any ideas how i might do this?

Thanks

TS

 

[Jon K]

This is bl***y difficult/messy to do using the start date and end date format.

I can do it simply if i store all the individual days taken as leave but the amount of data stored would be huge.

You can put a list of work days (exclude any holidays and week ends, if needed) in a separate table and join this table to the leave table with Between StartDate And EndDate. (See query "qryLeave Days" in the database, from this query you can pull any info you want.)

I have also included a Totals Query to show the Number of WorkDays between the leave start dates and end dates.
.

 

[The Stoat]

Dear Jon_K

That is a very interesting solution, forgive me if i'm wrong here; you need all the potential dates in tbl_WorkDays for this to work and the query is a Cartesian product type where the tables aren't joined. Do you know what the impact is when running this query with lots of data?

Cheers

TS

 

[Jon K]

No, it isn't a Cartesian product. The two tables are actually joined by:-

[tbl_WorkDays].[WorkDay] Between [tbl_Leave].[StartDate] And [tbl_Leave].[EndDate]

In the sample database, the query returns only 58 records. A Cartesian product would return 1,464 records.


If you feel more comfortable with the ANSI syntax of JOINs, you can use:-

SELECT tbl_Leave.LeaveID, tbl_Leave.StaffID, tbl_Leave.StartDate, tbl_Leave.EndDate, tbl_WorkDays.WorkDay
FROM tbl_WorkDays INNER JOIN tbl_Leave ON (tbl_Workdays.WorkDay between tbl_Leave.StartDate and tbl_Leave.EndDate)

in query SQL View. This syntax, being a Non-Equi Join, is not supported in query Design View. And it will run noticeably slower than my original query.


And I believe my query will run much faster than the rownum (where rownum should be an integer that increments by 1 for each pass) even if you can make rownum work because my query doesn't need to make any passes on the records.

With lots of data, any method will take time unless you already have a table containing the individual leave dates of each Leave_ID. It's inevitable.
.

 

[The Stoat]

Hi Jon_K

Thanks for explaining that I didn't realise that this was a way of joining.
This is certainly the answer to my problem.

Cheers

TS