Thread: Query help...
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 --------------------------------------------------------
> 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.