Finding the ORIGINATING team of a client where the Current team is X?

[SDS100UK]

Hi,

I have sql written which will brings me back all the current members of team ABC.

In addition what I need is the team they originated in before moving to ABC. This may have been the team directly before or many team moves before.

What I have so far is:
-- Selects Non Employees from the Persons table to and find the relationships an
-- person has had
SELECT p.per_id,
p.per_first_names || ' '|| p.per_surname as Full_Name,
ou.oun_short_name,
ou.oun_name AS Team_Name,
ore.rel_start_date AS Rel_Start,
ore.rel_end_date AS Rel_End
FROM O_persons p
INNER JOIN o_relationships ore
on ore.rel_source_per_gro_id = p.per_id
INNER JOIN o_organisation_units ou
on ore.rel_to_per_gro_id = ou.oun_short_name

WHERE p.per_employee_ind = 'N' and ou.oun_short_name = 'ABC'
ORDER BY p.per_id, ore.rel_start_date desc;

This works fine, but I cant figure out the logic to present the original team.

Many thanks in advance,

Steven

 

[LKBrwnDBA]

. . . e t c
This works fine, but I cant figure out the logic to present the original team

And what conditions define the "original team"?
Post the query that base on a person just gets this "original team".



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[SkipVought]

Does your db contain a Parent - Child data relationship?

Something like [This Team] and [Former Team]?

 

[SDS100UK]

Hi....thanks for your responses.

The table has a client id as primary key. It then lists all the teams they have been assigned to. This row comprises client id, team id, start date and end date.

So it will have all teams in one table. I need to say in effect....show me the first team the client was assigned to for those who are in team ABC now.

Really hope you guys can help.

Steven