By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
WITH yourTable AS (
SELECT 123 AS EventID, 1 AS CID, '2020-12-01' AS Date UNION ALL
SELECT 123, 2, '2020-12-01' UNION ALL
SELECT 123, 3, '2020-12-01' UNION ALL
SELECT 345, 2, '2020-12-05' UNION ALL
SELECT 345, 4, '2020-12-05' UNION ALL
SELECT 456, 1, '2020-12-07' UNION ALL
SELECT 456, 4, '2020-12-07' UNION ALL
SELECT 567, 1, '2020-12-08'
)
SELECT
EventID AS ID,
CID,
Date,
(SELECT TOP 1 t2.Date FROM yourTable t2
WHERE t2.CID = t1.CID AND t2.Date > t1.Date
ORDER BY t2.Date) AS ColumnA
FROM yourTable t1
ORDER BY
CID,
Date;
| ID | CID | Date | ColumnA |
|---|---|---|---|
| 123 | 1 | 2020-12-01 | 2020-12-07 |
| 456 | 1 | 2020-12-07 | 2020-12-08 |
| 567 | 1 | 2020-12-08 | null |
| 123 | 2 | 2020-12-01 | 2020-12-05 |
| 345 | 2 | 2020-12-05 | null |
| 123 | 3 | 2020-12-01 | null |
| 345 | 4 | 2020-12-05 | 2020-12-07 |
| 456 | 4 | 2020-12-07 | null |