Home     |     .Net Programming    |     cSharp Home    |     Sql Server Home    |     Javascript / Client Side Development     |     Ajax Programming

Ruby on Rails Development     |     Perl Programming     |     C Programming Language     |     C++ Programming     |     IT Jobs

Python Programming Language     |     Laptop Suggestions?    |     TCL Scripting     |     Fortran Programming     |     Scheme Programming Language


 
 
Cervo Technologies
The Right Source to Outsource

MS Dynamics CRM 3.0

Sql Server Programming

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:

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...

Add to del.icio.us | Digg this | Stumble it | Powered by Megasolutions Inc