Query Date Range Against Date Range
-
Hello,
I am really struggling with how to create a query that returns records for a query using a date range to find data with matching date range.
Problem:
Basically best thought of as a job allocation/recruitment database which holds staff information on contracts they are currently working on, ie charles is booked out to work as cleaner between 13/01/05 - 17/01/05 and 21/01/05 - 23/01/05. He can be booked against multiple future jobs and past jobs and jobs that run over any possible start date/ end date of a new job.
Heres the problem, the system also holds date ranges of jobs ie. It holds a job that is between 14/01/05 -and 16/01/05.
This would mean there is an unforfilled job where charles is available. A query i need to build and run
Now implementing this in a query is killing me, tryed messing with intersect joins, didnt get anywhere.
ie staffjobstartdate<contractstartdate
intersect
staffjobenddate<contractenddate.
no luck
any help appreciated
-
You have staff who are contracted (with date ranges)
You have jobs (with date ranges)
and you want see if any staff can fulfill any jobs
Questions
Where is the relationship between staff and jobs? (or is that the contracted date range)
How do you determine whether the member of staff is qualified to do the job?
What is the maximum number of days in any date range?
Can you post ddl of the tables and what sort of output you want?
Far away is close at hand in the images of elsewhere.
Anon. -
Hi,
I tried to solve your problem as an exercise. don't know if it helps.
I image you could you the following schema
Table Employee : Id as Int, Name as char(50), Title as Int.
Table Jobs : Id as Int, IdCompany as Int, Title as Int, StartDate as datetime, EndDate as datetime
Table Contracts : Id as Int, IdEmployee as Int, StartDate as datetime, EndDate as datetime. This tells when an employee is already busy.
This procedure tell you which job an employee could apply to:
create proc GetJob(@employee as int)
as
select * from jobs J
where not exists
(select contracts.id
from jobs, employees inner join contracts on employees.id = contracts.idemployee
where ((contracts.startdate >= jobs.startdate and contracts.startdate <= jobs.enddate)
or (contracts.enddate >= jobs.startdate and contracts.enddate <= jobs.enddate))
and employees.id = @employee
and J.Id = jobs.id
 
go
This procedure tells you which employee could apply to a job:
create proc GetEmployee(@job as int)
as
select * from employees E
where not exists
(select contracts.id
from jobs, employees inner join contracts on employees.id = contracts.idemployee
where ((contracts.startdate >= jobs.startdate and contracts.startdate <= jobs.enddate)
or (contracts.enddate >= jobs.startdate and contracts.enddate <= jobs.enddate))
and jobs.id = @job
and E.Id = employees.id
 
go
This list every possible match :
select E.id, E.name, J.id
from jobs J, employees E
where not exists
(select contracts.id
from jobs, employees inner join contracts on employees.id = contracts.idemployee
where ((contracts.startdate >= jobs.startdate and contracts.startdate <= jobs.enddate)
or (contracts.enddate >= jobs.startdate and contracts.enddate <= jobs.enddate))
and jobs.id = J.Id
and employees.id = E.Id
 
Patrick Duflot
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply