I have a SQL table with historical data on users of a specific system. I need to report on how one field has changed over time. Here's an example of the data:
User Type Effective Date
User 1 A 1/1/2010
User 1 B 5/1/2010
User 1 A 7/1/2010
User 2 B 1/1/2010
User 2 A 2/1/2010
The data basically means that User 1 was type A from 1/1 - 4/30, type B from 5/1 - 6/30 and back to type A from 7/1 through today.
How can I write a query that expands this data so I can chart the change of user types over time?