data conversion

Last Post 14 Jan 2011 07:42 AM by dbandee. 0 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
dbandee
New Member
New Member

--
14 Jan 2011 07:42 AM
Hi
i have Table1 like this
FID, VehStatus, Date
1,   Running, 2009-01-01
2,   Running, 2009-02-01
3,   Repair,  2009-03-01
4,   Running, 2009-04-01
5,   Repair,  2010-05-01
6,   Running, 2010-06-01
7,   Maint,   2010-01-01
8,   Maint,   2010-11-01
9,   Running, 2010-12-01  

I need to:
1. Record Begin and End date for each status, so end of previous status is begin of next status

FID, VehStatus, BeginDate,  EndDate
1,   Running,   2009-01-01, 2009-03-01
2,   Repair,    2009-03-01, 2009-04-01
3,   Running,   2009-04-01, 2010-05-01
4,   Repair,    2010-05-01, 2010-06-01
5,   Running,   2010-06-01, 2010-01-01
6,   Maint,     2010-01-01, 2010-11-01
7,   Running,   2010-12-01, NULL


2. find gaps in data (like between rec 4 and 5 in Table1

Can some one suggest a trick?


Acceptable Use Policy
---