SQL Server: only group sequential records when using GROUP BY

Question

I have a table containing a record for each stay in the ward within the hospital spell (note: the spell may include transfers to other hospitals). Spellno is the unique identifier for the spell. I would like to combine successive stays within the spell to the hospital level. The problem is that if a patient moves from hospital1 to hospital2 and back to hospital1, GROUP BY'Spellno' and 'Hospital' will combine two hospital 1 stays that I do not want to do.

eg. if this was my data:

Spellno   Hospital   WardCode   WardStart   WardEnd 
-------------------------------------------------------------------
123       hosp1      ward1      01/04/2015  03/04/2015
123       hosp1      ward4      03/04/2015  05/04/2015
123       hosp2      ward2      05/04/2015  07/04/2015
123       hosp1      ward3      07/04/2015  10/04/2015
123       hosp1      ward1      10/04/2015  12/04/2015

I want to aggregate at Spellno and Hospital to get:

Spellno   Hospital   WardStart   WardEnd 
-------------------------------------------------------------------
123       hosp1      01/04/2015  05/04/2015
123       hosp2      05/04/2015  07/04/2015
123       hosp1      07/04/2015  12/04/2015

Thank you very much in advance.

Answer 1

WHERE, SELECT, .

SELECT Spellno, Hospital,D.WardStart,
   (SELECT Min(E.WardEnd)
    FROM #tab E
    WHERE E.WardEnd >= D.WardEnd
      AND E.Spellno = D.Spellno
      AND E.Hospital = D.Hospital
      AND NOT EXISTS (SELECT 1
                      FROM #tab E2
                      WHERE E.WardStart < E2.WardStart
                        AND E.WardEnd >= E2.WardStart
                        AND D.Spellno = E2.Spellno
                        AND D.Hospital = E2.Hospital)
  ) AS WardEnd
FROM #tab D
WHERE NOT EXISTS (SELECT 1
                  FROM #tab D2
                  WHERE D.WardStart <= D2.WardEnd
                    AND D.WardEnd > D2.WardEnd
                    AND D.Spellno = D2.Spellno
                    AND D.Hospital = D2.Hospital)

:

, .

LiveDemo

:

╔═════════╦══════════╦═════════════════════╦═════════════════════╗
║ Spellno ║ Hospital ║      WardStart      ║       WardEnd       ║
╠═════════╬══════════╬═════════════════════╬═════════════════════╣
║     123 ║ hosp1    ║ 2015-04-01 00:00:00 ║ 2015-04-05 00:00:00 ║
║     123 ║ hosp2    ║ 2015-04-05 00:00:00 ║ 2015-04-07 00:00:00 ║
║     123 ║ hosp1    ║ 2015-04-07 00:00:00 ║ 2015-04-12 00:00:00 ║
╚═════════╩══════════╩═════════════════════╩═════════════════════╝

Answer 2

, (WardStart, WardEnd) . , .

SQL:

WITH 
  data AS (
    SELECT * 
    FROM (
      VALUES (123, 'hosp1', 'ward1', CAST('2015-04-01' AS DATE), CAST('2015-04-03' AS DATE)),
             (123, 'hosp1', 'ward4', CAST('2015-04-03' AS DATE), CAST('2015-04-05' AS DATE)),
             (123, 'hosp2', 'ward2', CAST('2015-04-05' AS DATE), CAST('2015-04-07' AS DATE)),
             (123, 'hosp1', 'ward3', CAST('2015-04-07' AS DATE), CAST('2015-04-10' AS DATE)),
             (123, 'hosp1', 'ward1', CAST('2015-04-10' AS DATE), CAST('2015-04-12' AS DATE))
    ) AS t(Spellno, Hospital, WardCode, WardStart, WardEnd)
  ),
  consecutive(Spellno, Hospital, WardStart, WardEnd) AS (
    SELECT Spellno, Hospital, WardStart, WardEnd
    FROM data AS d1
    WHERE NOT EXISTS (
      SELECT *
      FROM data AS d2
      WHERE d1.Spellno = d2.Spellno
      AND d1.Hospital = d2.Hospital
      AND d1.WardStart = d2.WardEnd
    )
    UNION ALL
    SELECT c.Spellno, c.Hospital, c.WardStart, d.WardEnd
    FROM consecutive AS c
    JOIN data AS d
    ON c.Spellno = d.Spellno
    AND c.Hospital = d.Hospital
    AND c.WardEnd = d.WardStart
  )
SELECT Spellno, Hospital, WardStart, MAX(WardEnd)
FROM consecutive
GROUP BY Spellno, Hospital, WardStart
ORDER BY Spellno, WardStart

CTE consecutive , " " (Spellno, Hospital). :

Spellno  Hospital  WardStart   WardEnd
-----------------------------------------
123      hosp1     2015-04-01  2015-04-03
123      hosp2     2015-04-05  2015-04-07
123      hosp1     2015-04-07  2015-04-10

WardStart ( ) WardEnd. :

Spellno  Hospital  WardStart   WardEnd
-----------------------------------------
123      hosp1     2015-04-01  2015-04-03 <-- Unwanted, "intermediary" row
123      hosp1     2015-04-01  2015-04-05
123      hosp2     2015-04-05  2015-04-07
123      hosp1     2015-04-07  2015-04-10 <-- Unwanted, "intermediary" row
123      hosp1     2015-04-07  2015-04-12

, WardEnd , :

Spellno  Hospital  WardStart   WardEnd
-----------------------------------------
123      hosp1     2015-04-01  2015-04-05
123      hosp2     2015-04-05  2015-04-07
123      hosp1     2015-04-07  2015-04-12