July 28, 2005 at 8:36 am
Hi
I have a query that has been requested to provide a length of service for all members in an organization. The requirement simplified is two tables
MEMBER
member_no, member_name
ROLE
role_no, member_no, start_date, end_date
This reflects an organization where members can have 1 or more roles, sometimes in parallel, often a member will leave the organization and return
What I need to determine is the total length of service within the organization using the role start and end dates
eg
role1 01/01/2000 to 01/09/2000
role2 01/06/2000 to 01/08/2002
role3 01/04/2005 to 01/07/2005
So the total length of service here is 1 yr 11 months
The only way that springs to mind to approach this would be to use 2 cursors fetching through each role (in date order) within each member to work out total cumulative service
The question is, is there a way that to achieve this with a Select statement instead
July 28, 2005 at 9:08 am
OK I have no data for testing but here it goes:
Select member_name, Sum( datediff ( d, SD, ED ) ) Total_Days
from
(
--Find all islands
select member_name
, startdate SD
, (select Min(endate) from Members M3
where
M3.member_name = M1.MembersName
and
M1.Startdate > M3.startdate
and
not exists (select * from Members M4 where M4.member_name = M1.MembersName and M4.Startdate <= M3.Endate )
) ED
from Members M1
where not exists (select * from Members M2 where member_name =member_name and M1.start_date <= M2.End_Date)
) Qry
group by member_name
* Noel
August 2, 2005 at 7:45 am
That looks promising Noel, I'll give it a bash and let you know how it goes
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply