Criteria help needed, records between two date fields 1

[LD1010]

I have a database that stores information on vacation time taken by employees. The two Date/Time data type fields [FirstDayOff] and [ReturningOn] record the vacation days. Days between those two dates are vacation days.
I need help writing the criteria for a query that will return the records of all employees who are off between any two date parameters, [Report Start Date] and [Report End Date] entered by the user.

Any help would be mutch appreciated.

 

[Golom]

There are several possibilities for this.

The employee days off may

- begin before, and end after the report interval.
- begin between the report dates but end after the end of the report.
- begin before the report start but end within the report interval.
- begin and end within the report interval.

Select EmpID
     , DateDiff("d", [Report Start Date], [Report End Date]) As Days
From myTable
Where [Report Start Date] >= [FirstDayOff] 
  AND [Report End Date]   <= [ReturningOn]

UNION

Select EmpID
     , DateDiff("d", [FirstDayOff], [Report End Date]) As Days
From myTable
Where [Report Start Date] <= [FirstDayOff] 
  AND [Report End Date]   <  [ReturningOn]

UNION

Select EmpID
     , DateDiff("d", [Report Start Date], [ReturningOn]) As Days
From myTable
Where [Report Start Date] >  [FirstDayOff] 
  AND [Report End Date]   >= [ReturningOn]

UNION

Select EmpID
     , DateDiff("d", [FirstDayOff], [ReturningOn]) As Days
From myTable
Where [Report Start Date] <  [FirstDayOff] 
  AND [Report End Date]   >  [ReturningOn]

 

[griffi256]

I have been trying something similar with my hotel booking system. Trying to prevent "double bookings".
When booking a room, the start and finish dates cannot overlap already booked dates. Otherwise we get a "double booking".
So, the code I used (which works) modifies the original code slightly.
Now it shows all possible clashes:-

Select DISTINCT *
, DateDiff("d", [ClientAllocation.StartDate], [ClientAllocation.FinishDate]) As Days
From Clientallocation
Where [ClientAllocation.StartDate] >= [Start]
AND [ClientAllocation.FinishDate] <= [Finish]
UNION ALL
Select DISTINCT *
, DateDiff("d", [Start], [ClientAllocation.FinishDate]) As Days
From ClientAllocation
Where [ClientAllocation.StartDate] <= [Start]
AND [ClientAllocation.FinishDate] < [Finish]
UNION ALL
Select DISTINCT *
, DateDiff("d", [ClientAllocation.StartDate], [Finish]) As Days
From ClientAllocation
Where [ClientAllocation.StartDate] > [Start]
AND [ClientAllocation.FinishDate] >= [Finish]
UNION ALL Select DISTINCT *
, DateDiff("d", [Start], [Finish]) As Days
From ClientAllocation
Where [ClientAllocation.StartDate] <= [Start]
AND [ClientAllocation.FinishDate] >= [Finish];

All negative values for [Days] can be disregarded!
Hope this helps?