ITEM_ID nvarchar(5),

LINE nvarchar(2),

ACTIVITY_DATE date,

ACTIVITY nvarchar(8)

);

INSERT INTO ActivityHX (ITEM_ID, LINE, ACTIVITY_DATE, ACTIVITY)

('38003', '1', '20230803', 'OPENED'),

('38003', '2', '20230803', 'CLOSED'),

('38003', '3', '20230812', 'REOPENED'),

('38003', '4', '20230814', 'CLOSED');

(SELECT

ITEM_ID

,LINE

,ACTIVITY_DATE

,ACTIVITY

,DATEDIFF(DAY, CASE WHEN ACTIVITY = 'CLOSED' THEN ACTIVITY_DATE END, CASE WHEN ACTIVITY IN ('OPENED', 'REOPENED') THEN ACTIVITY_DATE END) 'Diff'

FROM ActivityHX)

ITEM_ID

,CASE WHEN ACTIVITY IN ('OPENED', 'REOPENED') THEN ACTIVITY_DATE END 'START_DATE'

,CASE WHEN ACTIVITY = 'CLOSED' THEN ACTIVITY_DATE END 'END_DATE'

FROM (

ITEM_ID

,ACTIVITY_DATE

,ACTIVITY

,Diff as 'Days Diff'

,ROW_NUMBER() OVER (PARTITION BY ITEM_ID ORDER BY CASE WHEN Diff <= 0 then -1 else 1 end, abs(Diff)) as RN

from mydata) ActivityHX

WHERE RN = 1;