Drop table #table
Drop table #table_with_groupid
-- Prepare test data
CREATE TABLE #table
(	[Admissions_key] bigint NOT NULL PRIMARY KEY,
MRN nvarchar(10) NOT NULL,
hosp_code nvarchar(10) NOT NULL,
adm_datetime datetime NOT NULL,
sep_datetime datetime NOT NULL,
Sequence nvarchar(10) NOT NULL
)

SET DATEFORMAT DMY
INSERT INTO #table(	Admissions_key,MRN, hosp_code, adm_datetime, sep_datetime,Sequence)
VALUES
(7099222,'0000544607','0003','22/07/2011 04:55','22/07/2011 10:44','First'), 
(7099223,'0000544607','0003','22/07/2011 10:45','25/07/2011 19:43','Middle'), 
(7099224,'0000544607','0003','25/07/2011 19:44','26/07/2011 11:29','Middle'),
(7099225,'0000544607','0003','27/07/2011 13:30','27/07/2011 19:30','First'),
(7099226,'0000544607','0003','27/07/2011 19:31','28/07/2011 11:30','Final'),
(7099227,'0000559282','0003','03/07/2011 22:50','03/07/2011 23:51','First'),
(7099228,'0000559282','0003','03/07/2011 23:52','04/07/2011 15:30','Middle'),
(7099229,'0000559282','0003','04/07/2011 15:31','04/07/2011 17:59','Final'),
(7099230,'0000559282','0003','05/07/2011 18:00','05/07/2011 18:05','Middle'),
(7099231,'0000559282','0003','05/07/2011 18:06','09/07/2011 14:58','Final'),
(7099232,'0000999271','0003','07/08/2011 01:00','07/08/2011 18:05','Middle'),
(7099233,'0000999271','0003','07/08/2011 18:06','09/08/2011 14:58','Final'),
(7099234,'0000999271','0003','10/08/2011 18:00','10/08/2011 18:05','First'),
(7099235,'0000446435','0003','11/08/2011 18:06','12/08/2011 12:08','First'),
(7099236,'0000446435','0003','12/08/2011 12:09','12/08/2011 14:58','Final')


;WITH cur_prev AS 
(	-- Match current row with row above

	SELECT cur.*, prev_Sequence = prev.Sequence, pre_sep_datetime =  CASE WHEN cur.Sequence='First' THEN NULL ELSE prev.sep_datetime  END
	   FROM #table cur
		OUTER APPLY -- we use OUTER APPLY instead of CROSS APPLY to get a row even if previous row does not exist
		(	-- Find previous row to current row.
			SELECT TOP 1 tt.*
			FROM #table tt
			WHERE tt.MRN = cur.MRN AND tt.hosp_code = cur.hosp_code -- within the same group
				and -- only rows above
				(	tt.adm_datetime < cur.adm_datetime
					OR tt.adm_datetime = cur.adm_datetime AND tt.Admissions_key < cur.Admissions_key
				)
			ORDER BY tt.adm_datetime DESC, tt.Admissions_key DESC -- sort should be unique, so we added a PK column
		) prev
)

SELECT c.*,
	GroupID =
	(	-- Find the first row above that starts a group
		SELECT TOP 1 cc.Admissions_key
		FROM cur_prev cc
		WHERE cc.MRN = c.MRN AND cc.hosp_code = c.hosp_code -- within the same group
			AND -- only rows above, but this time INCLUDING current row!
			(	cc.adm_datetime < c.adm_datetime
				OR cc.adm_datetime = c.adm_datetime AND cc.Admissions_key <= c.Admissions_key
			)

		ORDER BY CASE WHEN cc.Sequence='First' OR cc.prev_Sequence='Final' THEN 0 ELSE 1 END, -- first try to find a row that starts a group
			cc.adm_datetime DESC, cc.Admissions_key DESC -- sort should be unique, so we added a PK column
	)
INTO #table_with_groupid
FROM cur_prev c

SELECT * FROM #table_with_groupid

SELECT t.*, g.IsGroupCorrect   
FROM #table_with_groupid t
	LEFT JOIN
	(	-- Find which group is correct and which is not
		SELECT tg.GroupID, 
			IsGroupCorrect = CASE -- correct is group that have 'First' and 'Final' in it.
				WHEN MAX(CASE WHEN tg.Sequence='First' THEN 1 ELSE 0 END)=1 AND MAX(CASE WHEN tg.Sequence='Final' THEN 1 ELSE 0 END)=1 THEN 'C'
				ELSE 'E'
			END
		FROM #table_with_groupid tg
	--where  datediff(MINUTE,tg.adm_datetime,tg.pre_sep_date)< =1	
		GROUP BY tg.GroupID
	) g on t.GroupID = g.GroupID