Hi All,

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

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 ?

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.

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

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