Re: Query help... - Mailing list pgsql-general

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/