Returning all dates between two dates in the same record?

[The Stoat]

Hi,

Please bear with me here as it's a little involved.

I'm doing a staff profile website which includes a section where they can enter their annual/other leave details.

I decided to store their leave in two fields Start_Date | End_Date rather than each individual date that they took - the short and wide approach vs long and narrow.

This has left me needing to do a query that would return all the dates between the start and end dates inclusive.

Example:

StaffID---Start_Date---End_Date
---1-----12/12/2004--14/12/2004

Returns:
StaffID---Leave_Dates
--1-------12/12/2004
--1-------13/12/2004
--1-------14/12/2004


I appreciate i could do this using some script to loop through a recordset and build an array of dates but i wondered/hoped that it could be done using SQL.

As it is an asp page i can't use user defined functions in a VBA module in Access so the solution would need to be pure SQL.

Is this possible?

Any help v.much appreciated.

TS

 

[Len Boorman]

How about having a calendar table with dates.

Query the table for dates between Start date and End Date of holiday

L

 

[The Stoat]

Len as usual your a genius

First Query lets me select all the dates for a given member of staff and for a given leave type i.e. annual leave

SELECT TBL_DATES.LEAVEDATES, TBL_LEAVE.LEAVE_TYPE_ID, TBL_LEAVE.STAFF_ID
FROM TBL_DATES, TBL_LEAVE
WHERE (((TBL_DATES.LEAVEDATES)>=[TBL_LEAVE].[START_DATE] And (TBL_DATES.LEAVEDATES)<=[TBL_LEAVE].[END_DATE]) AND ((TBL_LEAVE.LEAVE_TYPE_ID)=[USERPARAM_LEAVE_TYPE_VAL]) AND ((TBL_LEAVE.STAFF_ID)=[USERPARAM_STAFFID]));

Second query allows me to select which dates i'm interested in from the first query and count them.

SELECT Count(QRY_SELECT_ALL_LEAVE_DATES.LEAVEDATES) AS LEAVE_DAYS_TAKEN, TBL_LEAVE_TYPE.LEAVE_TYPE
FROM TBL_LEAVE_TYPE INNER JOIN QRY_SELECT_ALL_LEAVE_DATES ON TBL_LEAVE_TYPE.LEAVE_TYPE_ID=QRY_SELECT_ALL_LEAVE_DATES.LEAVE_TYPE_ID
WHERE (((QRY_SELECT_ALL_LEAVE_DATES.LEAVEDATES) Between [USERPARAM_START_DATE] And [USERPARAM_END_DATE]))
GROUP BY TBL_LEAVE_TYPE.LEAVE_TYPE;

The reason i'm doing this is to count the number of days taken in a "leave year" i.e. 01/04/#### to 31/03/#### where leave may bridge 2 leave years and i only want the days taken in the current year.

Thanks for your help

TS

 

[Len Boorman]

Len as usual your a genius

Thanks for your help

TS

Keep advertising that and your welcome

L