Re: SQL Problem

 Not all data is clean and not all problems have neat, simple solutions.

Seems to me you need to decide whether

to change the constraints on career_snapshot to allow for the possibility of part-time work in more than one store
OR
to scrub the data, determining where employee 2 really worked during months 7 thru 11 (split the difference???)
OR
to nominate employee 2 for Sainthood (bi-location is an acceptable miracle for Sainthood)

Ed Prochak
I've been doing a lot of scrubbing recently, so I may be a little cranky 8^)

Niall Litchfield wrote:

> I have a problem which I have have fictionalized below. Consider the career
> history of fictional employees as shown in the table career_history (extract
> below). this details the store at which each employee works and a starting
> and ending period when they were employed there. so employee 1 starts at
> store 1 in period 01, works there until period 8 and then transfers to store
> 2 where he works for the rest of the year.
>
> If I wish to update the table career_snapshot_11 with his store at period 11
> I would issue
>
> update career_snapshot_11 s
> set storeid = (select storeid from career_history h
> where h.empno = s.empo
> and s.period between h.start_period and h.end_period
> and s.period = 11);
>
> leading to the first row shown in career_snapshot.
>
> unfortunately for employee2 the statement would fail with
>
> single-row subquery returns more than one row
>
> because employee 2 is apparently working at both stores in this time period.
>
> So my problem is
>
> Given only career_history how can I find all employees who are shown as
> working at different stores in the same period. We the of course have in
> reality the problem of finding out which of the two rows is correct and
> which is wrong, but that is a different story.
>
> career_history
> storeid empno start_period end_period
>
> 1 1 01 08
> 2 1 09 12
> 2 2 01 11
> 1 2 07 12
>
> career_snapshot_11
> empno, storeid, period
> 1 2 11
> 2 <?> 11
>
> I have considered a number of different approaches to this, but really can't
> come up with a neat solution (which I am sure must exist).
>
> Niall Litchfield
> Oracle DBA
> Audit Commission UK
Received on Thu Sep 27 2001 - 23:23:55 CDT