Sample Code for Gaps and Islands


  

 create table mytableTest
(
   PAT_NAME        VARCHAR(8) NOT NULL  
  ,LOC_ID          INTEGER  NOT NULL
  ,MRN             INTEGER  NOT NULL
  ,DEPARTMENT_NAME VARCHAR(17) NOT NULL
  ,EFFECTIVE_TIME datetime NOT NULL
  ,ADMISSION       datetime
  ,Transfer_In     datetime
  ,Transfer_Out    datetime
  ,Discharged      datetime
  ,EVENT_TYPE_IN   VARCHAR(11)
  ,Event_Type_Out  VARCHAR(12)
)
Go

INSERT INTO mytableTest (PAT_NAME,LOC_ID,MRN,DEPARTMENT_NAME,EFFECTIVE_TIME,ADMISSION,Transfer_In,Transfer_Out,Discharged,EVENT_TYPE_IN,Event_Type_Out) VALUES
 ('Doe,Jane',11111,2525,'FantasyLand','1/10/16 17:52','1/10/16 17:52',NULL,NULL,NULL,'Admission',NULL),
 ('Doe,Jane',11111,2525,'FantasyLand','1/12/16 15:21',NULL,NULL,'1/12/16 15:21',NULL,NULL,'Transfer Out'),
 ('Doe,Jane',11111,2525,'AdventureLand','1/12/16 15:21',NULL,'1/12/16 15:21',NULL,NULL,'Transfer In',NULL),
 ('Doe,Jane',11111,2525,'AdventureLand','1/12/16 18:24',NULL,NULL,'1/12/16 18:24',NULL,NULL,'Transfer Out'),
 ('Doe,Jane',11111,2525,'Tomorrowland','1/12/16 18:24',NULL,'1/12/16 18:24',NULL,NULL,'Transfer In',NULL),
 ('Doe,Jane',11111,2525,'Tomorrowland','1/14/16 14:27',NULL,NULL,'1/14/16 14:27',NULL,NULL,'Transfer Out'),
 ('Doe,Jane',11111,2525,'AdventureLand','1/14/16 14:27',NULL,'1/14/16 14:27',NULL,NULL,'Transfer In',NULL),
 ('Doe,Jane',11111,2525,'AdventureLand','1/14/16 16:03',NULL,NULL,'1/14/16 16:03',NULL,NULL,'Transfer Out'),
 ('Doe,Jane',11111,2525,'Tomorrowland','1/14/16 16:03',NULL,'1/14/16 16:03',NULL,NULL,'Transfer In',NULL),
 ('Doe,Jane',11111,2525,'Tomorrowland','1/17/16 11:42',NULL,NULL,'1/17/16 11:42',NULL,NULL,'Transfer Out'),
 ('Doe,Jane',11111,2525,'AdventureLand','1/17/16 11:42',NULL,'1/17/16 11:42',NULL,NULL,'Transfer In',NULL),
 ('Doe,Jane',11111,2525,'AdventureLand','1/17/16 11:43',NULL,NULL,'1/17/16 11:43',NULL,NULL,'Transfer Out'),
 ('Doe,Jane',11111,2525,'Tomorrowland','1/17/16 11:43',NULL,'1/17/16 11:43',NULL,NULL,'Transfer In',NULL),
 ('Doe,Jane',11111,2525,'Tomorrowland','1/18/16 12:00',NULL,NULL,'1/18/16 12:00',NULL,NULL,'Transfer Out'),
 ('Doe,Jane',11111,2525,'TTH 19W ADULT INT','1/18/16 12:00',NULL,'1/18/16 12:00',NULL,NULL,'Transfer In',NULL),
 ('Doe,Jane',11111,2525,'TTH 19W ADULT INT','1/19/16 11:05',NULL,NULL,'1/19/16 11:05',NULL,NULL,'Transfer Out'),
 ('Doe,Jane',11111,2525,'AdventureLand','1/19/16 11:05',NULL,'1/19/16 11:05',NULL,NULL,'Transfer In',NULL),
 ('Doe,Jane',11111,2525,'AdventureLand','1/19/16 16:46',NULL,NULL,'1/19/16 16:46',NULL,NULL,'Transfer Out'),
 ('Doe,Jane',11111,2525,'Tomorrowland','1/19/16 16:46',NULL,'1/19/16 16:46',NULL,NULL,'Transfer In',NULL),
 ('Doe,Jane',11111,2525,'Tomorrowland','1/23/16 15:52',NULL,NULL,'1/23/16 15:52',NULL,NULL,'Transfer Out'),
 ('Doe,Jane',11111,2525,'Tomorrowland','1/23/16 15:52',NULL,'1/23/16 15:52',NULL,NULL,'Transfer In',NULL),
 ('Doe,Jane',11111,2525,'Tomorrowland','1/23/16 16:03',NULL,NULL,'1/23/16 16:03',NULL,NULL,'Transfer Out'),
 ('Doe,Jane',11111,2525,'Tomorrowland','1/23/16 16:03',NULL,'1/23/16 16:03',NULL,NULL,'Transfer In',NULL),
 ('Doe,Jane',11111,2525,'Tomorrowland','1/23/16 20:45',NULL,NULL,'1/23/16 20:45',NULL,NULL,'Transfer Out'),
 ('Doe,Jane',11111,2525,'LibertySquare','1/23/16 20:45',NULL,'1/23/16 20:45',NULL,NULL,'Transfer In',NULL),
 ('Doe,Jane',11111,2525,'LibertySquare','1/25/16 19:06',NULL,NULL,'1/25/16 19:06',NULL,NULL,'Transfer Out'),
 ('Doe,Jane',11111,2525,'FrontierLand','1/25/16 19:06',NULL,'1/25/16 19:06',NULL,NULL,'Transfer In',NULL),
 ('Doe,Jane',11111,2525,'FrontierLand','2/3/16 20:45',NULL,NULL,NULL,'2/3/16 20:45',NULL,'Discharge');
Go

;with mycte as (
select   PAT_NAME,LOC_ID, MRN, DEPARTMENT_NAME, EFFECTIVE_TIME, ADMISSION , Transfer_In,Transfer_Out  ,Discharged     
,row_number() Over(order by EFFECTIVE_TIME )-row_number() Over(partition by  DEPARTMENT_NAME  order by EFFECTIVE_TIME )  grp  
,(row_number() Over(order by EFFECTIVE_TIME )+1)/2  grp2
from mytableTest

)

select  PAT_NAME,LOC_ID CSN, DEPARTMENT_NAME, max(EFFECTIVE_TIME) as EFFECTIVE_TIME
,max(ADMISSION) ADMISSION
,max(Transfer_In) Transfer_In
,max(Transfer_Out) Transfer_Out
,max(Discharged) Discharged

from mycte
Group by  PAT_NAME,LOC_ID, MRN,DEPARTMENT_NAME,grp, grp2
order by EFFECTIVE_TIME
 
drop table mytableTest
 





 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/cddcfd88-72f4-479a-855f-84eea10a8d44/cherry-picking-from-my-data-set-to-consolidate-rows-by-department?forum=transactsql

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s