Create a poor mans continuous inpatient stay within Scottish SMR01 data

smr_cis_label.sql

-- Continuous Inpatient Stay (CIS) is an identifier for an unbroken period of time that
-- a patient spends as an inpatient. A patient may change consultant, significant 
-- facility, speciality and/or hospital during a continuous inpatient stay. CIS can
-- be provided with Scottish SMR datasets. However, there are various rules associated
-- with CIS assignment that don't always map to study requirements (or are not 
-- implemented correct - especially with older SMR records). The code below assigns
-- a naive CIS to an SMR dataset. That is, a given CIS will be assigned to contiguous
-- records based soley on date. No consideration is given to origin of record or type
-- of admission.
--
-- The following is t-sql assuming the source file has been staged in sql server


;with
-- assign a rownumber to each record in smr dataset
-- flag records that break a continuous sequence
-- modify this if require tolerance (i.e. to allow 1,2,n days break and still 
-- consider as one episode)
rows_with_rownumber as (
	select 
		row_number() over (partition by subjectid order by admdate, disdate) as rown,
		case 
		when
			lag(admdate) over (partition by subjectid order by admdate, disdate) is null
			or
			lag(disdate) over (partition by subjectid order by admdate, disdate) < admdate then 1
			else 0
		end as start_of_episode, subjectid, admdate, disdate
	from smr01
),
-- create an episode identifier for each of the periods of continuous inpatient stay
rows_with_episode as (
	select 
		rown, 
		subjectid,
		sum(start_of_episode) over (partition by subjectid order by rown) as cis
		from rows_with_rownumber
)
-- create a summary record containing admission and discharge date associated with
-- continuous inpatient stay
select 
	epi.subjectid, 
	cis, 
	count(rw.rown) reccnt, 
	min(admdate) admdate,
	max(disdate) disdate
from
	rows_with_rownumber rw
	inner join
	rows_with_episode epi
	on
	epi.rown = rw.rown
	and
	epi.subjectid = rw.subjectid
group by
	epi.subjectid, cis
order by
	epi.subjectid, cis;