Member Avatar for ilrnaz

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
  • 79 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by buddylee17
Member Avatar for buddylee17

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

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.