Query help...

I've got three tables that I'm working with... the first is a table of
staff members... firstname, lastname, staffid... nothing tough... the
second is a table of jobs... job title, jobid... nothing tough... the
third is a history of job assignments... it's got a staffid, a jobid, a
timestamp, and a field to denote whether the job was added or dropped...
this third table exists because it is useful in this project to be able
to look at a staff member's job history...

...

Any suggestions?

Perhaps not a helpful one... but I would be inclined to reorganize that
third table to have a "start date" *and* a "stop date" field, and ditch
the "dropped" boolean:

o it would keep you from having to figure out how to associate two
entries in the same table with the same job (the added and dropped
rows). How would you currently prevent a job from being entered as
"dropped" without a corresponding "added" row?

o it would make it easier to do the query you are asking about; to tell
whether someone has an active job, just select on a date -- like 'today'
-- between the start and stop dates.

o it may better match reality; jobs have a duration so represent that
explicitly.

hth

- Thomas

Have you thought about adding an integer field (we'll
call it 'status')that is dependent upon the
add_or_drop filed? (I'm assuming that add_or_drop has
a not null constraint and must have either 'add' or
'drop'.)

Before running report queries run:

update staff_history
set add_drop_status = case when add_or_drop='add' then
1 else -1 end;

The following query should give you a list of active
staff/job combinations:

select staff_id, job_id, max(date_filed) as eff_date,
sum(add_drop_status) as status from staff_history
group by staffid, job_id having sum(add_drop_status) >
0;

Of course, if you just change add_or_drop to the
status field, you can eliminate the update step.

I hope this helps.

Andrew Gould

--- Joshua Adam Ginsberg <rainman@owlnet.rice.edu>
wrote:

A simplified version of my situation...

I've got three tables that I'm working with... the
first is a table of
staff members... firstname, lastname, staffid...
nothing tough... the
second is a table of jobs... job title, jobid...
nothing tough... the
third is a history of job assignments... it's got a
staffid, a jobid, a
timestamp, and a field to denote whether the job was
added or dropped...
this third table exists because it is useful in this
project to be able
to look at a staff member's job history...

The problem I'm running into is queries that attempt
to determine who is
an "active" member of the staff, defined as a staff
member who currently
has at least one job added but not dropped... this
can be easily
measured by saying that if the number of times a
staff member had a job
added is greater than the number of times that same
staff member had a
job dropped, that staff member is active...

What I'm having trouble figuring out is how can I
incorporate those
rowcounts into a greater query...

I've succeeded in some other cases... I can get a
list of the active
personnel:

select staff.staff_id, firstnames, lastname,
jobs.job_id, job_title from
staff, staff_history, jobs where staff.staff_id =
staff+history.staff_id
and jobs.job_id = staff_history.job_id group by
staff.staff_id,
firstnames, lastname, jobs.job_id, job_title having
(count(add_or_drop)
% 2) = 1 order by lastname, firstnames;

This succeeds because for each person-job
combination, if there's 0 mod
2 instances of them, then the person has added and
dropped the job (some
number of times)... if there's 1 mod 2 instances,
then the person has
added but not dropped the job...

I can get a list of jobs and who is actively
assigned to the job:

select jobs.job_id, job_title, staff.staff_id,
firstnames, lastname from
jobs left outer join (staff_history join staff on
(staff_history.staff_id = staff.staff_id)) on
(jobs.job_id =
staff_history.job_id) group by jobs.job_id,
staff.staff_id, job_title,
firstnames, lastname having (count(staff.staff_id) %
2 = 1);

This succeeds because for each grouping, which again
is a person-job
combination, I can eliminate anybody who has been
assigned and
deassigned to the same job the same number of
times...

But here's a case where I can't figure it out... I'm
trying to generate
a list of staff members with the "active" staff
members first... now, I
can't use my same of mod 2 here, because a person
can have more than one
job... so if i have been added to two jobs but not
removed from any,
count(add_or_drop) % 2 = 0, but I am active...

Any suggestions?

-jag

--

--------------------------------------------------------

Joshua Ginsberg
rainman@owlnet.rice.edu
Director of Technology dirtech@sa.rice.edu
Student Association AIM: L0stInTheDesert
Rice University, Houston, TX

========================================================

"Programming today is a race between software
engineers
striving to build bigger and better idiot-proof
programs
and the Universe trying to produce bigger and better
idiots. So far, the Universe is winning." -Rich Cook

--------------------------------------------------------

---------------------------(end of
broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://www.postgresql.org/search.mpl

__________________________________________________
Do You Yahoo!?
Send instant messages & get email alerts with Yahoo! Messenger.
http://im.yahoo.com/

I like Thomas's idea better than the one I just
posted.

If jobs are one-time projects, I would also create a
unique index on staff_history(staff_id, job_id). This
would help keep the data clean. (This would not work
if jobs are employment positions or projects that an
employee can leave and return to.)

Andrew Gould

--- Thomas Lockhart <lockhart@fourpalms.org> wrote:

I've got three tables that I'm working with... the

first is a table of

staff members... firstname, lastname, staffid...

nothing tough... the

second is a table of jobs... job title, jobid...

nothing tough... the

third is a history of job assignments... it's got

a staffid, a jobid, a

timestamp, and a field to denote whether the job

was added or dropped...

this third table exists because it is useful in

this project to be able

to look at a staff member's job history...

...

Any suggestions?

Perhaps not a helpful one... but I would be inclined
to reorganize that
third table to have a "start date" *and* a "stop
date" field, and ditch
the "dropped" boolean:

o it would keep you from having to figure out how to
associate two
entries in the same table with the same job (the
added and dropped
rows). How would you currently prevent a job from
being entered as
"dropped" without a corresponding "added" row?

o it would make it easier to do the query you are
asking about; to tell
whether someone has an active job, just select on a
date -- like 'today'
-- between the start and stop dates.

o it may better match reality; jobs have a duration
so represent that
explicitly.

hth

- Thomas

---------------------------(end of
broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to

majordomo@postgresql.org

__________________________________________________
Do You Yahoo!?
Send instant messages & get email alerts with Yahoo! Messenger.
http://im.yahoo.com/

On Sat, 11 Aug 2001 16:57:10 +0000 (UTC), <lockhart@fourpalms.org>:

Perhaps not a helpful one... but I would be inclined to reorganize that
third table to have a "start date" *and* a "stop date" field, and ditch
the "dropped" boolean:

This is how I would do it, then query for employees with
records that have NULL in the "stop date" field.