Fix Date gap

[CTl06]

Hello all,
I am trying to fix data in the database that incorrect record start date and sometime having incorrect record enddate. Can you please help me what is the best way to fix this data in SQL. Here are examples of problem data.

Scenario #1:
Recordid SartDate EndDate
1 01/01/2010 01/02/2010
2 01/02/2010 01/05/2010--correct date is start date of recorid 3
3 01/03/2010 01/04/2010
4 01/04/2010

Scenario #2:
Recordid SartDate EndDate
1 01/01/2010 01/02/2010
2 01/02/2010 01/05/2010
3 01/02/010 01/06/2010-- Correct start dt is recordid 2 enddate
4 01/062010

 

[bborissov]

How do you know which date is incorrect (start or end)?
And also is there ALWAYS record ID is in sequence?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[CTl06]

bborissov,

Most of the time record enddate is incorrect and yes there is always a recordid in sequence.

 

[bborissov]

MOST of the time didn't work
It should be ALL the time or nothing

SELECT YourTable.*,
       Tbl1.StartDate AS MaybeCorrectDate
FROM YourTable
INNER JOIN YourTable Tbl1 
      ON (YourTable.RecordId+1) = Tbl1.RecordId

See if this is what you need?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[CTl06]

Borissov,

Sorry, I did not mean to say that the RecordId is always requence.. The record id is identity seed but not all the group of records with bad date happen in the sequence.. for example:

1 01/01/2010 01/02/2010
100 01/02/2010 01/05/2010--correct date is start date of recorid 3
135 01/03/2010 01/04/20104 01/04/2010

 

[bborissov]

OK, what makes records in group and what version of SQL Server you use?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[CTl06]

I use sql 2005 and identifer that make records in group is other field

Recordid SartDate EndDate Id
1 01/01/2010 01/02/2010 10
2 01/02/2010 01/05/2010 10
3 01/02/010 01/06/2010 10 -- Correct start dt is recordid 2 enddate
4 01/062010 10

 

[bborissov]

SELECT YourTable.*,
       Tbl1.StartDate AS MaybeCorrectDate
FROM (SELECT *, 
             ROW_NUMBER() OVER (PARTITION BY Id ORDER BY StartDate) AS RowN
             FROM YourTable) YourTable
INNER JOIN (SELECT *, 
             ROW_NUMBER() OVER (PARTITION BY Id ORDER BY StartDate) AS RowN
             FROM YourTable) Tbl1
      ON (YourTable.RowN+1) = Tbl1.RowN

NOT TESTED!

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.