T-SQL Puzzel – Oplossing!

Enkele dagen geleden ging de puzzel online.
Stiekem hebben veel enthousiaste mensen me gemaild met een oplossing en natuurlijk zijn meerdere varianten mogelijk.

Maar….ook schoonheid verdient een prijs :)
(overigens was mijn oplossing lang niet zo mooi als het onderstaande)

Hierbij de oplossing:

SELECT DISTINCT [id]
, [department]
, [d1] = MIN([d1]) OVER(PARTITION BY [id], [department],[group])
, [d2] = MAX([d2]) OVER(PARTITION BY [id], [department],[group])
FROM (
SELECT *, SUM(r) OVER(PARTITION BY [id] ORDER BY [d1]) [group]
FROM (
SELECT *, IIF(ISNULL([department],'') <> ISNULL([pre_department],'') , 1 , 0) r
FROM (
SELECT [id]
, [department]
, [pre_department] = LAG ([department],1,NULL) OVER (PARTITION BY [id] ORDER BY [d])
, [d1] = [d]
, [d2] = LEAD ([d],1,NULL) OVER (PARTITION BY [id] ORDER BY [d])
FROM [dbo].[Table1]
) A
)B
)C
WHERE [d1] <> [d2]
ORDER BY [id], [d1]