BorlandTalk.com

Eric Stewart · Guest

I have two tables. Showing only the necessary info, they look like this

rsiEmployeeMaster (T1)

AreaID varchar(20)
Empnum int
EmpName varchar(31)
MaintSite varchar(20)
Shift varchar(20)

rsiScheduleMasterEmployee (T2)

AreaID varchar(20)
Empnum int
ScheduledDay smallint
RouteSection varchar(2)

T1 has one record for each employee. T2 may have multiple records for each
employee. The query I am trying to write will have input paramaters that
provide AreaID, MaintSite, Shift, ScheduledDay, and RouteSection. I need a
query that will return a result set with employees that meet the following
criteria:

Any employee with the specified AreaID, MaintSite, Shift with no record in
T2 for ScheduledDay.
Any employee with the specified AreaID, MaintSite, Shift, ScheduledDay with
a record in T2 that meets this condition:
RouteSection is any letter between A and Z but not the same as the
input parameter supplied (RouteSection)
Also, if the employee has a record in T2 with the specified AreaID,
MaintSite, Shift, ScheduledDay with a RouteSection of '*', he should not be
included in the result set.

I hope this makes since. In case it helps, here is some more detail on what
I am trying to do. I have a shift we'll call MYSHIFT. The shift has routes
and some routes are pulled weekly and others are pulled every other week.
Each route has a route section assigned to it. If it is a weekly route the
route section is '*'. If it is a route pulled every other week, it has a
letter between A and Z assigned to it. When the schedule is opened, the user
specifies what day and routesection they want to see, Monday, Section * or
Monday, Section A for example. So it is possible for an employee to have two
Monday schedules as long as they are schedules with a lettered route
section. However, if an employee is scheduled on Monday with a route section
of '*', he can not be scheduled on any more routes. The resultset I am
looking for are those employees who are available to be scheduled.

Thanks to anyone who understands my jibberish and can help with a query.

Eric

Del Murray · Guest

Eric,

Break it down in smaller pieces and use the "UNION" statement to do a select
of one group, then union it to the select of the next group, etc. Use the
"EXITS" statement to include (or not include) a record or condition that
'exists' (duh) that would effect that row being selected or not.

Wayne Niddery [TeamB] · Guest

Eric Stewart wrote:

Eric Stewart · Guest

Thanks Wayne. Will give it a try.

Eric

"Wayne Niddery [TeamB]" <wniddery (AT) chaffaci (DOT) on.ca> wrote

Eric Stewart · Guest

Thanks Del.

Eric

"Del Murray" <Del.Murray (AT) N_S_CreditHawk (DOT) Net> wrote