Combine multiple records into a date time line.

[mattd]

I have a data file that contains multiple records for each employee. I'm trying to build a view of the table that would show me a combined date range for per location. The example below are the fields from the file that i'm trying to combine.

Table Exp:
Employee # Begin Date End Date Location
1 1/1/2005 10/1/2005 BRD
1 10/2/2005 5/31/2007 BRD
1 6/1/2007 3/31/2009 RMC
1 4/1/2009 9/30/2010 RMC
1 10/1/2010 BRD

I'm trying to get a view that would show me:
Employee # Begin Date End Date Location
1 1/1/2005 5/31/2007 BRD
1 6/1/2007 9/30/2010 RMC
1 10/1/2010 BRD

[Rabbit]

You'll have to use a cursor for that. Here's a link to the documentation discussing cursors. http://msdn.microsoft.com/en-us/library/ms180169.aspx

[ck9663]

What's the basis for grouping? Are you just getting the value from the second row? Are there only two rows? 'Coz your last record does not seem to have a pair.


~~ CK

[mattd]

Sorry the data didn't upload visually as I was hoping for.
What I'm trying to do is capture the first and last date each time the employee changes location.
So i'm hoping to get 3 lines out of the data.
1) 1/1/2005 - 5/31/2007 date range for location = BRD.
2) 6/1/2007 - 9/30/2010 date range for location = RMC.
3) 10/1/2010 - null (current) date range for location = BRD.

[mattd]

Thanks, I'll check it out.

[mattd]

Rabbit thanks for the info but I have no idea how to set this up. I'm pretty new at SQL, would you be able to show me how to set something like this up with Cursor?

[Rabbit]

Make an attempt with the information in the doumentation. Post any errors you get and we can help you through them.

[mattd]

Rabbit, I found a SQL guys at the office. He helped me build the Cursor query which worked. Thanks for pointing me in the right direction.