Hi Bill!

I'd say this is likely to be a bit more complicated than it appears at
first sight. Let's say you've got one field called JobStart and
another called JobEnd. Then the first thought would be:

select C.Firstname || ' ' || C.Lastname as Contractor, C.ContractorID
from Contractors C
where not exists(select * from Jobs J
where J.ContractorID = C.ContractorID
and (J.JobStart between :JobStart1 and :JobEnd1
or J.JobEnd between :JobStart2 and :JobEnd2))

(using NOT EXISTS is better than <field> NOT IN (<subselect>) unless
the optimizer transform it)

However, if you're looking for someone for a job this coming week,
you'll not catch those who are assigned a job for the entire February
since neither the start or end of their assignment is this week. In
the end, I guess you'll end up with something like

select C.Firstname || ' ' || C.Lastname as Contractor, C.ContractorID
from Contractors C
where not exists(select * from Jobs J
where J.ContractorID = C.ContractorID
and J.JobStart <= :JobEnd
and J.JobEnd >= :JobStart)

Oops, that turned out to be simpler than I thought. Let's check the
possibilities. Say you're looking for someone for an assignment for
parts of the coming week, 9.2.2005 - 12.2.2005. You have five to
choose from and they have the following assignments:

Helen: 10.2.2005-11.2.2005 (entire work period within new assignment)
Ann: 7.2.2005-11.2.2005 (begin before, end during work period)
Martijn: 7.2.2005-15.2.2005 (begin before, finish after work period)
Set: 7.2.2005- 8.2.2005 (begin and end before work period)
Artur: 11.2.2005-15.2.2005 (begin within, end after work period)

Helen's out, and so is Ann, Martijn and Artur, but I'm available.
Hence, the SQL works. Though I certainly do not want any more work
just now. Oh, wait, I forgot one person:

Arno: 15.2.2005-16.2.2005 (begin and end after work period)

Yes, he doesn't begin before your job ends, so he should also be
available! Hurray, I can celebrate my 35th birthday without having to
work that day!!!

Set

-the names of the hypothetical case are fictuous. Any resemblance to
real persons are purely coincidental!

--- In firebird-support@yahoogroups.com, "bill_zwirs" wrote:
>
> Would appreciate help with an SQL query (Firebird database using
> Delphi 5 Pro and IBX components).
>
> The two tables I am querying are Contractors and Cont_Jobs and the
> result that I want is a list of contractors who would be available
> between the two passed parameters 'jobstart' and 'jobend' (ie.
> those that are not already allocated a job in the period between
> 'jobstart' and 'jobend'). ContractorID is primary key in
> Contractors table and foreign key in Cont_Jobs table. JobStart is a
> TimeStamp field in the Cont_Jobs table - so the parameters I am
> passing are DateTime.
>
> select C.Firstname || ' ' || C.Lastname as Contractor,
> C.ContractorID
> from Contractors C left join Cont_Jobs J on C.ContractorID =
> J.ContractorID
> where J.JobStart not between :jobstart and :jobend
>
> This query doesn't give me a result at all and if I change
> the 'where' clause to 'and', making the last line part of the 'from'
> clause, then I get a list of all contractors
> including those that have a job allocated between the passed
> parameters.
>
> The Contractor table contains names etc of the contractors and the
> Cont_Job table contains details of jobs allocated to contractors.
>
> This is driving me crazy. What am I missing. Any help would be
> appreciated.
>
> thanks in advance
>
> Bill