Hi Robert

With a corrected Version Number, it's relatively straightforward to set the end date of the current row to the start date of the following row.

E.g.:

FieldVersion: 3, StartDate: 2016-04-06 09:00, EndDate: 2016-08-20 00:32

FieldVersion: 4, StartDate: 2016-04-06 10:00, EndDate: NULL

You want to set the EndDate of FieldVersion 3 to 2016-04-06 10:00. This code will hopefully start you off (worked fine for the sample records I used):

SELECT ContactId, FieldId, FieldTypeId, FieldVersion, FieldVersion - 1 AS PreviousFieldVersion, StartDate, EndDate

INTO #MultiVersions

FROM Scd.ContactFields

SELECT CF.ContactId, CF.FieldId, CF.FieldVersion, CF.StartDate, CF.EndDate AS CurrentEndDate,

MV.StartDate AS NewEndDate

FROM Scd.ContactFields CF

LEFT JOIN #MultiVersions MV

ON MV.ContactId = CF.ContactId AND MV.FieldTypeId = CF.FieldTypeId

AND MV.PreviousFieldVersion = CF.FieldVersion

ORDER BY CF.ContactId, CF.FieldTypeId, CF.FieldVersion

Regards,

Mike.