Sql Server Programming :: Stuck with query to check two tables with date ranges


	Stuck with query to check two tables with date ranges I am hoping that somebody may be able to assist me with a query I am trying to construct but not really getting anywhere. I have 3 tables in the query, one holds staff details, the other a list of jobs and the third a list of exlusions (e.g. holidays). I am building a wizard that will walk the user through creating a job that can span multiple days, so I will have a start data and end date as a paramter along with an office_id. I need to populate a dataset with a list of staff id's and names that are available to assign to new jobs. I have to ensure that the list does not contain staff that are already assigned to a job within the jobs table but also do not belong to somebody else's job as an assistant within the date range. I think I have this part nailed. Finally I also need to make sure that they do not fall within an exclusion period, which can span multiple days. For example if they are on holiday Mon-Wed, and the job start date is Tuesday, that person should be filtered out because they need to be available for the whole period. Hope that makes sense. My query so far looks like this ALTER PROCEDURE [dbo].[sp_getAvailableStaff] @StartDate datetime, @EndDate datetime, @OfficeID int AS BEGIN SET NOCOUNT ON; SELECT staff_id, staff_name FROM tblStaff WHERE (staff_id NOT IN (SELECT DISTINCT job_owner FROM tblJobs WHERE (job_date BETWEEN @StartDate and @EndDate) AND job_owner IS NOT NULL) ) AND (staff_id NOT in (SELECT DISTINCT job_assistant FROM tblJobs WHERE (job_date BETWEEN @StartDate and @EndDate) AND job_assistant IS NOT NULL) ) AND (staff_id NOT IN (SELECT resource_id FROM tblExclusions WHERE (@StartDate BETWEEN startdate AND enddate) AND resource_type = 0) ) AND (staff_office_id = @OfficeID) AND staff_active = 'True' END This works, except for the last subquery which only checks the start date when checking the exclusion table: SELECT resource_id -- resource_id will effectively by the staff_id, other resources such as tools can have exclusions too FROM tblExclusions WHERE @StartDate BETWEEN startdate AND enddate) AND resource_type = 0) -- resource_type 0 = staff members How can I modify the last query to take into account both the start date and end date for the job which are the two input parameters and compare with the startdate and enddate of the exclusions table? Being quite new to SQL I'm really stuck with this, so any guidance would be useful. Thanks in advance, Lee On 6 Jun, 10:14, "Lee Clements" <lee.clements.nos@btopenworld.com> wrote: - Hide quoted text - > I am hoping that somebody may be able to assist me with a query I am trying > to construct but not really getting anywhere. I have 3 tables in the query, > one holds staff details, the other a list of jobs and the third a list of > exlusions (e.g. holidays). > I am building a wizard that will walk the user through creating a job that > can span multiple days, so I will have a start data and end date as a > paramter along with an office_id. > I need to populate a dataset with a list of staff id's and names that are > available to assign to new jobs. I have to ensure that the list does not > contain staff that are already assigned to a job within the jobs table but > also do not belong to somebody else's job as an assistant within the date > range. I think I have this part nailed. Finally I also need to make sure > that they do not fall within an exclusion period, which can span multiple > days. For example if they are on holiday Mon-Wed, and the job start date is > Tuesday, that person should be filtered out because they need to be > available for the whole period. Hope that makes sense. My query so far looks > like this > ALTER PROCEDURE [dbo].[sp_getAvailableStaff] > @StartDate datetime, > @EndDate datetime, > @OfficeID int > AS > BEGIN > SET NOCOUNT ON; > SELECT staff_id, staff_name FROM tblStaff > WHERE (staff_id NOT IN > (SELECT DISTINCT job_owner > FROM tblJobs > WHERE (job_date BETWEEN @StartDate and @EndDate) > AND job_owner IS NOT NULL) > ) > AND (staff_id NOT in > (SELECT DISTINCT job_assistant > FROM tblJobs > WHERE (job_date BETWEEN @StartDate and @EndDate) > AND job_assistant IS NOT NULL) > ) > AND (staff_id NOT IN > (SELECT resource_id > FROM tblExclusions > WHERE (@StartDate BETWEEN startdate AND enddate) > AND resource_type = 0) > ) > AND (staff_office_id = @OfficeID) > AND staff_active = 'True' > END > This works, except for the last subquery which only checks the start date > when checking the exclusion table: > SELECT resource_id -- resource_id will effectively by the staff_id, other > resources such as tools can have exclusions too > FROM tblExclusions > WHERE @StartDate BETWEEN startdate AND enddate) > AND resource_type = 0) -- resource_type 0 = staff members > How can I modify the last query to take into account both the start date and > end date for the job which are the two input parameters and compare with the > startdate and enddate of the exclusions table? Being quite new to SQL I'm > really stuck with this, so any guidance would be useful. > Thanks in advance, > Lee Can you give example on how @EndDate has an effect on the filter data. Do mean the @EndDate is >er then startdate and <= enddate? Please give more example with different data to understand more quickly. -----------------------------------------------Reply----------------------------------------------- Thanks -pb- I was writing an explanation with sample data, I answered my own question. Your comment "Do mean the @EndDate is >er then startdate and <= enddate?" got me thinking in a broader way. By changing the date filter to WHERE ((@StartDate BETWEEN startdate AND enddate) OR (@EndDate BETWEEN startdate AND enddate)) I can capture both sides of the @StartDate and @EndDate. I was making it more complicated than it needed to be. Thanks for your help and swift response. Cheers Lee "-pb-" <prateekb@gmail.com> wrote in message news:1181122204.312943.191050@q75g2000hsh.googlegroups.com... - Hide quoted text - > On 6 Jun, 10:14, "Lee Clements" <lee.clements.nos@btopenworld.com> > wrote: >> I am hoping that somebody may be able to assist me with a query I am >> trying >> to construct but not really getting anywhere. I have 3 tables in the >> query, >> one holds staff details, the other a list of jobs and the third a list of >> exlusions (e.g. holidays). >> I am building a wizard that will walk the user through creating a job >> that >> can span multiple days, so I will have a start data and end date as a >> paramter along with an office_id. >> I need to populate a dataset with a list of staff id's and names that are >> available to assign to new jobs. I have to ensure that the list does not >> contain staff that are already assigned to a job within the jobs table >> but >> also do not belong to somebody else's job as an assistant within the date >> range. I think I have this part nailed. Finally I also need to make sure >> that they do not fall within an exclusion period, which can span multiple >> days. For example if they are on holiday Mon-Wed, and the job start date >> is >> Tuesday, that person should be filtered out because they need to be >> available for the whole period. Hope that makes sense. My query so far >> looks >> like this >> ALTER PROCEDURE [dbo].[sp_getAvailableStaff] >> @StartDate datetime, >> @EndDate datetime, >> @OfficeID int >> AS >> BEGIN >> SET NOCOUNT ON; >> SELECT staff_id, staff_name FROM tblStaff >> WHERE (staff_id NOT IN >> (SELECT DISTINCT job_owner >> FROM tblJobs >> WHERE (job_date BETWEEN @StartDate and @EndDate) >> AND job_owner IS NOT NULL) >> ) >> AND (staff_id NOT in >> (SELECT DISTINCT job_assistant >> FROM tblJobs >> WHERE (job_date BETWEEN @StartDate and @EndDate) >> AND job_assistant IS NOT NULL) >> ) >> AND (staff_id NOT IN >> (SELECT resource_id >> FROM tblExclusions >> WHERE (@StartDate BETWEEN startdate AND enddate) >> AND resource_type = 0) >> ) >> AND (staff_office_id = @OfficeID) >> AND staff_active = 'True' >> END >> This works, except for the last subquery which only checks the start date >> when checking the exclusion table: >> SELECT resource_id -- resource_id will effectively by the staff_id, >> other >> resources such as tools can have exclusions too >> FROM tblExclusions >> WHERE @StartDate BETWEEN startdate AND enddate) >> AND resource_type = 0) -- resource_type 0 = staff members >> How can I modify the last query to take into account both the start date >> and >> end date for the job which are the two input parameters and compare with >> the >> startdate and enddate of the exclusions table? Being quite new to SQL I'm >> really stuck with this, so any guidance would be useful. >> Thanks in advance, >> Lee > Can you give example on how @EndDate has an effect on the filter data. > Do mean the @EndDate is >er then startdate and <= enddate? Please give > more example with different data to understand more quickly.