;WITH a (ID,ImportGroup) AS (
SELECTc.ID,ROW_NUMBER() OVER (ORDER BY c.ID)
FROM@data c
LEFT JOIN @data n ON n.ID = c.ID + 1
WHERE(c.EventTypeID IN (1,2,4) AND n.EventTypeID in (0, 3))
OR(c.EventTypeID IN (0, 3) and n.EventTypeID in (1, 2, 4))
ORc.DeviceID <> n.DeviceID
ORn.DeviceID IS NULL
),
b (FromID,ToID,ImportGroup) AS (
SELECTISNULL(a1.ID+1,1),a.ID,a.ImportGroup
FROMa
LEFT JOIN a a1 On a1.ImportGroup = a.ImportGroup - 1
)
SELECT d.ID,d.DeviceID,d.EventTypeID,b.ImportGroup
FROM @data d
JOIN b ON d.ID BETWEEN b.FromID AND b.ToID
Performance could be an issue here due to table scans.
Far away is close at hand in the images of elsewhere.
Anon.