0

Hi All,

I think of creating a table(TeamMember) which has the following columns:

(Table1)
TeamMemberId TeamId MemberId StartDate
1 TeamA MemberA 1-Jan-2010
2 TeamA MemberB 2-Jan-2010
3 TeamB MemberA 3-Jan-2010
4 TeamA MemberA 4-Jan-2010
.
.
.

(TeamMemberId is an autoid field)

This table is to record whenever there is a change among Team and Members. Team could contains many members and members only assigned to 1 team. Any later team assignment date (StartDate) will override the previous(if any) team assignment date.

How do i extract the above information as dated eg. 1-Jan-2010 to 5-Jan-2010 ?

(Table2)
Date TeamId MemberId
1-Jan-2010 TeamA MemberA
2-Jan-2010 TeamA MemberA
2-Jan-2010 TeamA MemberB
3-Jan-2010 TeamB MemberA
3-Jan-2010 TeamA MemberB
4-Jan-2010 TeamA MemberA
4-Jan-2010 TeamA MemberB
5-Jan-2010 TeamA MemberA
5-Jan-2010 TeamA MemberB

Is there possible sql query for above scenario without programming?(Table1->Table2 or Table2->Table1)

Please guide. Thanks.

2
Contributors
1
Reply
2
Views
6 Years
Discussion Span
Last Post by buddylee17
0

You are storing duplicate information. Both tables contain Date, TeamId, and MemberId.

You need a table for member, team, and a teammemberlink table.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.