locked
Cherry Picking from my Data Set to consolidate rows by department RRS feed

  • Question

  • Greetings,

    I have a query that I need to essentially show time in and time out for each department that a patient is in during their admission in chronological order. Thus, an ADT trail of for each patient as far is which departments they were in and how much time they were in them. Eventually I will add a column to show number of minutes in each department.

    I have changed names/dates/times to maintain privacy in my sample below. My query below shows the results of a single patient, but in reality I will be listing every inpatient in my result set. I am very close to what I need now but am not quite sure how to pull the final part.

    I am using a CTE in my query which currently gives me the following data set (very very close... shows each department the patient was in during their admission (and in the correct order) but is doing it in multiple rows... I want to consolidate that). Note how a patient can be readmitted to the same department (i.e, If I went from 4th floor to surgery, I could then be readmitted to 4th floor after surgery.... need to follow the full trail). So far so good...

    SELECT 
    
     PAT_NAME
    ,LOC_ID
    ,CSN
    ,DEPARTMENT_NAME
    ,EFFECTIVE_TIME
    ,ADMISSION
    ,Transfer_In
    ,Transfer_Out
    ,Discharged
    ,EVENT_TYPE_IN
    ,Event_Type_Out
    
    
    FROM CTE2
    ORDER BY CSN, EFFECTIVE_TIME, Admission, Transfer_In, Transfer_Out ,Discharged

    This portion of my CTE query gives me this (so close) Notice how I am basically getting an in and out time for each department in chronological order) It looks like you have to scroll left/right to see all of the columns below:

    PAT_NAME LOC_ID MRN DEPARTMENT_NAME EFFECTIVE_TIME ADMISSION Transfer_In Transfer_Out Discharged EVENT_TYPE_IN Event_Type_Out
    Doe,Jane 11111 2525 FantasyLand 1/10/16 17:52 1/10/17 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

    Below is what I would like my final output to look like. I have been experimenting with MAX and various groupings but don't quite grasp yet how to achieve the output below (note how the In and Out time for each department is consolidated to a single row). (You need to scroll left/right to view all of the columns):

    PAT_NAME LOC_ID MRN DEPARTMENT_NAME EFFECTIVE_TIME ADMISSION Transfer_In Transfer_Out Discharged
    Doe,Jane 11111 2525 FantasyLand 1/10/16 17:52 1/10/17 17:52 NULL 1/12/16 15:21 NULL
    Doe,Jane 11111 2525 AdventureLand 1/12/16 15:21 NULL 1/12/16 15:21 1/12/16 18:24 NULL
    Doe,Jane 11111 2525 Tomorrowland 1/12/16 18:24 NULL 1/12/16 18:24 1/14/16 14:27 NULL
    Doe,Jane 11111 2525 AdventureLand 1/14/16 14:27 NULL 1/14/16 14:27 1/14/16 16:03 NULL
    Doe,Jane 11111 2525 Tomorrowland 1/14/16 16:03 NULL 1/14/16 16:03 1/17/16 11:42 NULL
    Doe,Jane 11111 2525 AdventureLand 1/17/16 11:42 NULL 1/17/16 11:42 1/17/16 11:43 NULL
    Doe,Jane 11111 2525 Tomorrowland 1/17/16 11:43 NULL 1/17/16 11:43 1/18/16 12:00 NULL
    Doe,Jane 11111 2525 TTH 19W ADULT INT 1/18/16 12:00 NULL 1/18/16 12:00 1/19/16 11:05 NULL
    Doe,Jane 11111 2525 AdventureLand 1/19/16 11:05 NULL 1/19/16 11:05 1/19/16 16:46 NULL
    Doe,Jane 11111 2525 Tomorrowland 1/19/16 16:46 NULL 1/19/16 16:46 1/23/16 15:52 NULL
    Doe,Jane 11111 2525 Tomorrowland 1/23/16 15:52 NULL 1/23/16 15:52 1/23/16 16:03 NULL
    Doe,Jane 11111 2525 Tomorrowland 1/23/16 16:03 NULL 1/23/16 16:03 1/23/16 20:45 NULL
    Doe,Jane 11111 2525 LibertySquare 1/23/16 20:45 NULL 1/23/16 20:45 1/25/16 19:06 NULL
    Doe,Jane 11111 2525 FrontierLand 1/25/16 19:06 NULL 1/25/16 19:06 NULL 2/3/16 20:45

     Many Thanks in advance for your assistance!!!



    • Edited by DaveDVF Monday, June 12, 2017 1:18 PM display issue
    Monday, June 12, 2017 1:11 PM

All replies

  • Cannot test it right now, but

    SELECT * FROM 
    (
    SELECT 
    TOP 1
     PAT_NAME
    ,LOC_ID
    ,CSN
    ,DEPARTMENT_NAME
    ,EFFECTIVE_TIME
    ,ADMISSION
    ,Transfer_In
    ,Transfer_Out
    ,Discharged
    ,EVENT_TYPE_IN
    ,Event_Type_Out


    FROM CTE2
    ORDER BY CSN, EFFECTIVE_TIME, Admission, Transfer_In, Transfer_Out ,Discharged
    ) AS DER
    UNION ALL
    SELECT 
     PAT_NAME
    ,LOC_ID
    ,CSN
    ,DEPARTMENT_NAME
    ,EFFECTIVE_TIME
    ,ADMISSION
    ,Transfer_In
    ,Transfer_Out
    ,Discharged
    ,EVENT_TYPE_IN
    ,Event_Type_Out


    FROM CTE2 WHERE Transfer_In IS NOT NULL


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, June 12, 2017 1:23 PM
    Answerer
  • CREATE TABLE mytable(
       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       VARCHAR(13)
      ,Transfer_In     VARCHAR(13)
      ,Transfer_Out    VARCHAR(13)
      ,Discharged      VARCHAR(30)
      ,EVENT_TYPE_IN   VARCHAR(11)
      ,Event_Type_Out  VARCHAR(12)
    )
    INSERT INTO mytable(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');
    
     
    ;with mycte as (
    select PAT_NAME,LOC_ID,MRN,DEPARTMENT_NAME,EFFECTIVE_TIME,ADMISSION,Transfer_In,Transfer_Out,Discharged,EVENT_TYPE_IN,Event_Type_Out
    ,row_number() Over(order by EFFECTIVE_TIME )-row_number() Over(partition by DEPARTMENT_NAME order by EFFECTIVE_TIME )  grp from mytable)
    
    select PAT_NAME,LOC_ID,MRN,DEPARTMENT_NAME
    ,min(EFFECTIVE_TIME) 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
    order by EFFECTIVE_TIME
    
    
    drop table mytable
    
    

    Monday, June 12, 2017 1:57 PM
  • Thank you for your help.  Much closer...

    PAT_NAME LOC_ID CSN DEPARTMENT_NAME2 EFFECTIVE_TIME ADMISSION Transfer_In Transfer_Out Discharged
    Doe,Jane 11111 2525 FantasyLand 1/10/17 17:52 1/10/17 17:52 NULL 1/12/17 15:21 NULL
    Doe,Jane 11111 2525 AdventureLand 1/12/17 15:21 NULL 1/12/17 15:21 NULL NULL
    Doe,Jane 11111 2525 AdventureLand 1/12/17 18:24 NULL NULL 1/12/17 18:24 NULL
    Doe,Jane 11111 2525 TomorrowLand 1/12/17 18:24 NULL 1/12/17 18:24 NULL NULL
    Doe,Jane 11111 2525 TomorrowLand 1/14/17 14:27 NULL NULL 1/14/17 14:27 NULL
    Doe,Jane 11111 2525 AdventureLand 1/14/17 14:27 NULL 1/14/17 14:27 NULL NULL
    Doe,Jane 11111 2525 AdventureLand 1/14/17 16:03 NULL NULL 1/14/17 16:03 NULL
    Doe,Jane 11111 2525 TomorrowLand 1/14/17 16:03 NULL 1/14/17 16:03 NULL NULL
    Doe,Jane 11111 2525 TomorrowLand 1/17/17 11:42 NULL NULL 1/17/17 11:42 NULL
    Doe,Jane 11111 2525 AdventureLand 1/17/17 11:42 NULL 1/17/17 11:42 NULL NULL
    Doe,Jane 11111 2525 AdventureLand 1/17/17 11:43 NULL NULL 1/17/17 11:43 NULL
    Doe,Jane 11111 2525 TomorrowLand 1/17/17 11:43 NULL 1/17/17 11:43 NULL NULL
    Doe,Jane 11111 2525 TomorrowLand 1/18/17 12:00 NULL NULL 1/18/17 12:00 NULL
    Doe,Jane 11111 2525 TTH 19W ADULT INT 1/18/17 12:00 NULL 1/18/17 12:00 1/19/17 11:05 NULL
    Doe,Jane 11111 2525 AdventureLand 1/19/17 11:05 NULL 1/19/17 11:05 NULL NULL
    Doe,Jane 11111 2525 AdventureLand 1/19/17 16:46 NULL NULL 1/19/17 16:46 NULL
    Doe,Jane 11111 2525 TomorrowLand 1/19/17 16:46 NULL 1/19/17 16:46 NULL NULL
    Doe,Jane 11111 2525 TomorrowLand 1/23/17 15:52 NULL 1/23/17 16:03 1/23/17 20:45 NULL
    Doe,Jane 11111 2525 LibertySquare 1/23/17 20:45 NULL 1/23/17 20:45 NULL NULL
    Doe,Jane 11111 2525 LibertySquare 1/25/17 19:06 NULL NULL 1/25/17 19:06 NULL
    Doe,Jane 11111 2525 FrontierLand 1/25/17 19:06 NULL 1/25/17 19:06 NULL NULL
    Doe,Jane 11111 2525 FrontierLand 2/3/17 20:45 NULL NULL NULL 2/3/17 20:45

    Monday, June 12, 2017 5:47 PM
  • Can you provide your sample table DDL and your expected result?

    From the tabular data you posted, you can convert it into scripts with this online tool:

    CSV to SQL

    What is your expected result of your new dataset?

    Monday, June 12, 2017 5:52 PM
  • PAT_NAME LOC_ID MRN DEPARTMENT_NAME EFFECTIVE_TIME ADMISSION Transfer_In Transfer_Out Discharged
    Doe,Jane 11111 2525 FantasyLand 1/10/16 17:52 1/10/17 17:52 NULL 1/12/16 15:21 NULL
    Doe,Jane 11111 2525 AdventureLand 1/12/16 15:21 NULL 1/12/16 15:21 1/12/16 18:24 NULL
    Doe,Jane 11111 2525 Tomorrowland 1/12/16 18:24 NULL 1/12/16 18:24 1/14/16 14:27 NULL
    Doe,Jane 11111 2525 AdventureLand 1/14/16 14:27 NULL 1/14/16 14:27 1/14/16 16:03 NULL
    Doe,Jane 11111 2525 Tomorrowland 1/14/16 16:03 NULL 1/14/16 16:03 1/17/16 11:42 NULL
    Doe,Jane 11111 2525 AdventureLand 1/17/16 11:42 NULL 1/17/16 11:42 1/17/16 11:43 NULL
    Doe,Jane 11111 2525 Tomorrowland 1/17/16 11:43 NULL 1/17/16 11:43 1/18/16 12:00 NULL
    Doe,Jane 11111 2525 TTH 19W ADULT INT 1/18/16 12:00 NULL 1/18/16 12:00 1/19/16 11:05 NULL
    Doe,Jane 11111 2525 AdventureLand 1/19/16 11:05 NULL 1/19/16 11:05 1/19/16 16:46 NULL
    Doe,Jane 11111 2525 Tomorrowland 1/19/16 16:46 NULL 1/19/16 16:46 1/23/16 15:52 NULL
    Doe,Jane 11111 2525 Tomorrowland 1/23/16 15:52 NULL 1/23/16 15:52 1/23/16 16:03 NULL
    Doe,Jane 11111 2525 Tomorrowland 1/23/16 16:03 NULL 1/23/16 16:03 1/23/16 20:45 NULL
    Doe,Jane 11111 2525 LibertySquare 1/23/16 20:45 NULL 1/23/16 20:45 1/25/16 19:06 NULL
    Doe,Jane 11111 2525 FrontierLand 1/25/16 19:06 NULL 1/25/16 19:06 NULL 2/3/16 20:45

    ~THANKS!

    Monday, June 12, 2017 5:55 PM
  • Can you explain why you have a few rows that are not consistent with your other rows?
    Monday, June 12, 2017 9:03 PM
  • PAT_NAME LOC_ID MRN DEPARTMENT_NAME EFFECTIVE_TIME ADMISSION Transfer_In Transfer_Out Discharged
    Doe,Jane 11111 2525 FantasyLand 1/10/16 17:52 1/10/17 17:52 NULL 1/12/16 15:21 NULL
    Doe,Jane 11111 2525 AdventureLand 1/12/16 15:21 NULL 1/12/16 15:21 1/12/16 18:24 NULL
    Doe,Jane 11111 2525 Tomorrowland 1/12/16 18:24 NULL 1/12/16 18:24 1/14/16 14:27 NULL
    Doe,Jane 11111 2525 AdventureLand 1/14/16 14:27 NULL 1/14/16 14:27 1/14/16 16:03 NULL
    Doe,Jane 11111 2525 Tomorrowland 1/14/16 16:03 NULL 1/14/16 16:03 1/17/16 11:42 NULL
    Doe,Jane 11111 2525 AdventureLand 1/17/16 11:42 NULL 1/17/16 11:42 1/17/16 11:43 NULL
    Doe,Jane 11111 2525 Tomorrowland 1/17/16 11:43 NULL 1/17/16 11:43 1/18/16 12:00 NULL
    Doe,Jane 11111 2525 TTH 19W ADULT INT 1/18/16 12:00 NULL 1/18/16 12:00 1/19/16 11:05 NULL
    Doe,Jane 11111 2525 AdventureLand 1/19/16 11:05 NULL 1/19/16 11:05 1/19/16 16:46 NULL
    Doe,Jane 11111 2525 Tomorrowland 1/19/16 16:46 NULL 1/19/16 16:46 1/23/16 15:52 NULL
    Doe,Jane 11111 2525 Tomorrowland 1/23/16 15:52 NULL 1/23/16 15:52 1/23/16 16:03 NULL
    Doe,Jane 11111 2525 Tomorrowland 1/23/16 16:03 NULL 1/23/16 16:03 1/23/16 20:45 NULL
    Doe,Jane 11111 2525 LibertySquare 1/23/16 20:45 NULL 1/23/16 20:45 1/25/16 19:06 NULL
    Doe,Jane 11111 2525 FrontierLand 1/25/16 19:06 NULL 1/25/16 19:06 NULL 2/3/16 20:45

    ~THANKS!


    Hi Dave,

    You may use APPLY operator to meet your needs. The APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query. Here is the example code for your reference.

    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 cte1 as
    (
    select PAT_NAME,LOC_ID,MRN,DEPARTMENT_NAME,EFFECTIVE_TIME,ADMISSION,case when Transfer_In is null then ADMISSION else Transfer_In end as Transfer_In1,Transfer_In
    from mytableTest
    where EVENT_TYPE_IN in ('Admission','Transfer In')
    ),
    cte2 as
    (
    select PAT_NAME,LOC_ID,MRN,DEPARTMENT_NAME,EFFECTIVE_TIME,Discharged,case when Transfer_Out is null then Discharged else Transfer_Out end as Transfer_Out1,Transfer_Out
    from mytableTest
    where EVENT_TYPE_out in ('Transfer Out','Discharge')
    )
    select T.PAT_NAME,T.LOC_ID,MRN,T.DEPARTMENT_NAME,T.EFFECTIVE_TIME,T.ADMISSION,T.Transfer_In
    ,T1.Transfer_Out,T1.Discharged 
    from cte1 T
    cross apply( 
      select top 1 Transfer_Out,Discharged from cte2
       where PAT_NAME=T.PAT_NAME
    		and LOC_ID=T.LOC_ID
    		and DEPARTMENT_NAME=T.DEPARTMENT_NAME
    		and Transfer_Out1> T.Transfer_In1
    		order by Transfer_Out
              )T1

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, June 13, 2017 9:49 AM
  • Hi Will_Kong,

    Good work with the apply. 

    With your modified dataset, it can still be solved as gaps and islands question. Here is the solution I came up with your databaset. The OP's sample dataset has issues.

    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

    Tuesday, June 13, 2017 2:04 PM
  • This works beautifully with the test code here, but not in my database. I suspect the devil is in the details (in my attempt to maintain data privacy I must have made a mistake in transposing the data which is causing this code not to work the same way in my real database.  

    Nevertheless, the logic and presentation here is exactly what I was hoping to achieve. Many Many thanks for that !!  I will work through the process on my end and see where I made the mistake. Once I figure that out I think I can apply this logic at that point.   Can't thank all of you enough/ So glad you are out there to point the way!!

    Wednesday, June 14, 2017 10:57 AM
  • OK.... So in an attempt to use your awesome code, I placed whatr I have so far into a Temp table so that my starting point for your CTE code recommendation above would be the same.

    Thus... I do this:

    SELECT 
    PAT_NAME
    ,LOC_ID
    ,CSN
    ,DEPARTMENT_NAME
    ,EFFECTIVE_TIME
    ,ADMISSION
    ,Transfer_In
    ,Transfer_Out
    ,Discharged
    ,EVENT_TYPE_IN
    ,Event_Type_Out

    FROM #SOURCE

    ORDER BY CSN, EFFECTIVE_TIME, Admission, Transfer_In, Transfer_Out ,Discharged

    This gives me what I believe is the very same data set as yours (using the temp table #source) as my starting point to before I use your CTE.  Here is the contents of #SOURCE that the code above returns:

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

    OK.... So now I incorporate your CTE Logic, which works perfectly in your sample data:

    ;with mycte as (
    select   PAT_NAME,LOC_ID, CSN, 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 #SOURCE
    
    
    )
    
    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, CSN,DEPARTMENT_NAME,grp, grp2
    order by EFFECTIVE_TIME
     
      drop table #SOURCE

    Instead of getting your perfect results, I get this instead (not sure where I went wrong):

    PAT_NAME CSN DEPARTMENT_NAME EFFECTIVE_TIME ADMISSION Transfer_In Transfer_Out Discharged
    Doe,Jane 2525 AdventureLand 1/12/17 15:21 NULL 1/12/17 15:21 NULL NULL
    Doe,Jane 2525 FantasyLand 1/12/17 15:21 1/10/17 17:52 NULL 1/12/17 15:21 NULL
    Doe,Jane 2525 AdventureLand 1/12/17 18:24 NULL NULL 1/12/17 18:24 NULL
    Doe,Jane 2525 Tomorrowland 1/12/17 18:24 NULL 1/12/17 18:24 NULL NULL
    Doe,Jane 2525 Tomorrowland 1/14/17 14:27 NULL NULL 1/14/17 14:27 NULL
    Doe,Jane 2525 AdventureLand 1/14/17 14:27 NULL 1/14/17 14:27 NULL NULL
    Doe,Jane 2525 AdventureLand 1/14/17 16:03 NULL NULL 1/14/17 16:03 NULL
    Doe,Jane 2525 Tomorrowland 1/14/17 16:03 NULL 1/14/17 16:03 NULL NULL
    Doe,Jane 2525 Tomorrowland 1/17/17 11:42 NULL NULL 1/17/17 11:42 NULL
    Doe,Jane 2525 AdventureLand 1/17/17 11:42 NULL 1/17/17 11:42 NULL NULL
    Doe,Jane 2525 AdventureLand 1/17/17 11:43 NULL NULL 1/17/17 11:43 NULL
    Doe,Jane 2525 Tomorrowland 1/17/17 11:43 NULL 1/17/17 11:43 NULL NULL
    Doe,Jane 2525 Tomorrowland 1/18/17 12:00 NULL NULL 1/18/17 12:00 NULL
    Doe,Jane 2525 AdventureLand 1/19/17 11:05 NULL 1/19/17 11:05 NULL NULL
    Doe,Jane 2525 TTH 19W ADULT INT 1/19/17 11:05 NULL 1/18/17 12:00 1/19/17 11:05 NULL
    Doe,Jane 2525 AdventureLand 1/19/17 16:46 NULL NULL 1/19/17 16:46 NULL
    Doe,Jane 2525 Tomorrowland 1/19/17 16:46 NULL 1/19/17 16:46 NULL NULL
    Doe,Jane 2525 Tomorrowland 1/23/17 15:52 NULL 1/23/17 15:52 NULL NULL
    Doe,Jane 2525 Tomorrowland 1/23/17 16:03 NULL NULL 1/23/17 16:03 NULL
    Doe,Jane 2525 Tomorrowland 1/23/17 20:45 NULL 1/23/17 16:03 1/23/17 20:45 NULL
    Doe,Jane 2525 LibertySquare 1/25/17 19:06 NULL 1/23/17 20:45 1/25/17 19:06 NULL
    Doe,Jane 2525 FrontierLand 2/3/17 20:45 NULL 1/25/17 19:06 NULL 2/3/17 20:45

    Wednesday, June 14, 2017 11:58 AM
  • Please post your sample DDL and data in script!

     
    CREATE TABLE mytable(
       PAT_NAME        VARCHAR(8) NOT NULL  
      ,LOC_ID          INTEGER  NOT NULL
      ,MRN             INTEGER  NOT NULL
      ,DEPARTMENT_NAME VARCHAR(17) NOT NULL
      ,EFFECTIVE_TIME  VARCHAR(13) NOT NULL
      ,ADMISSION       VARCHAR(13)
      ,Transfer_In     VARCHAR(13)
      ,Transfer_Out    VARCHAR(13)
      ,Discharged      VARCHAR(12)
      ,EVENT_TYPE_IN   VARCHAR(11)
      ,Event_Type_Out  VARCHAR(12)
    );
    INSERT INTO mytable(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/17 17:52','1/10/17 17:52',NULL,NULL,NULL,'Admission',NULL);
    INSERT INTO mytable(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/12/17 15:21',NULL,NULL,'1/12/17 15:21',NULL,NULL,'Transfer Out');
    INSERT INTO mytable(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,'AdventureLand','1/12/17 15:21',NULL,'1/12/17 15:21',NULL,NULL,'Transfer In',NULL);
    INSERT INTO mytable(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,'AdventureLand','1/12/17 18:24',NULL,NULL,'1/12/17 18:24',NULL,NULL,'Transfer Out');
    INSERT INTO mytable(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,'Tomorrowland','1/12/17 18:24',NULL,'1/12/17 18:24',NULL,NULL,'Transfer In',NULL);
    INSERT INTO mytable(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,'Tomorrowland','1/14/17 14:27',NULL,NULL,'1/14/17 14:27',NULL,NULL,'Transfer Out');
    INSERT INTO mytable(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,'AdventureLand','1/14/17 14:27',NULL,'1/14/17 14:27',NULL,NULL,'Transfer In',NULL);
    INSERT INTO mytable(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,'AdventureLand','1/14/17 16:03',NULL,NULL,'1/14/17 16:03',NULL,NULL,'Transfer Out');
    INSERT INTO mytable(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,'Tomorrowland','1/14/17 16:03',NULL,'1/14/17 16:03',NULL,NULL,'Transfer In',NULL);
    INSERT INTO mytable(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,'Tomorrowland','1/17/17 11:42',NULL,NULL,'1/17/17 11:42',NULL,NULL,'Transfer Out');
    INSERT INTO mytable(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,'AdventureLand','1/17/17 11:42',NULL,'1/17/17 11:42',NULL,NULL,'Transfer In',NULL);
    INSERT INTO mytable(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,'AdventureLand','1/17/17 11:43',NULL,NULL,'1/17/17 11:43',NULL,NULL,'Transfer Out');
    INSERT INTO mytable(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,'Tomorrowland','1/17/17 11:43',NULL,'1/17/17 11:43',NULL,NULL,'Transfer In',NULL);
    INSERT INTO mytable(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,'Tomorrowland','1/18/17 12:00',NULL,NULL,'1/18/17 12:00',NULL,NULL,'Transfer Out');
    INSERT INTO mytable(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,'TTH 19W ADULT INT','1/18/17 12:00',NULL,'1/18/17 12:00',NULL,NULL,'Transfer In',NULL);
    INSERT INTO mytable(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,'TTH 19W ADULT INT','1/19/17 11:05',NULL,NULL,'1/19/17 11:05',NULL,NULL,'Transfer Out');
    INSERT INTO mytable(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,'AdventureLand','1/19/17 11:05',NULL,'1/19/17 11:05',NULL,NULL,'Transfer In',NULL);
    INSERT INTO mytable(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,'AdventureLand','1/19/17 16:46',NULL,NULL,'1/19/17 16:46',NULL,NULL,'Transfer Out');
    INSERT INTO mytable(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,'Tomorrowland','1/19/17 16:46',NULL,'1/19/17 16:46',NULL,NULL,'Transfer In',NULL);
    INSERT INTO mytable(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,'Tomorrowland','1/23/17 15:52',NULL,NULL,'1/23/17 15:52',NULL,NULL,'Transfer Out');
    INSERT INTO mytable(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,'Tomorrowland','1/23/17 15:52',NULL,'1/23/17 15:52',NULL,NULL,'Transfer In',NULL);
    INSERT INTO mytable(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,'Tomorrowland','1/23/17 16:03',NULL,NULL,'1/23/17 16:03',NULL,NULL,'Transfer Out');
    INSERT INTO mytable(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,'Tomorrowland','1/23/17 16:03',NULL,'1/23/17 16:03',NULL,NULL,'Transfer In',NULL);
    INSERT INTO mytable(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,'Tomorrowland','1/23/17 20:45',NULL,NULL,'1/23/17 20:45',NULL,NULL,'Transfer Out');
    INSERT INTO mytable(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,'LibertySquare','1/23/17 20:45',NULL,'1/23/17 20:45',NULL,NULL,'Transfer In',NULL);
    INSERT INTO mytable(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,'LibertySquare','1/25/17 19:06',NULL,NULL,'1/25/17 19:06',NULL,NULL,'Transfer Out');
    INSERT INTO mytable(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,'FrontierLand','1/25/17 19:06',NULL,'1/25/17 19:06',NULL,NULL,'Transfer In',NULL);
    INSERT INTO mytable(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,'FrontierLand','2/3/17 20:45',NULL,NULL,NULL,'2/3/17 20:45',NULL,'Discharge');
    
    ;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 mytable
    
    )
    
    select  PAT_NAME,LOC_ID, MRN, 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
    
    
    --select * from mytable
    
    
    
    drop table mytable
    
    /*
    PAT_NAME	LOC_ID	MRN	DEPARTMENT_NAME	EFFECTIVE_TIME	ADMISSION	Transfer_In	Transfer_Out	Discharged
    Doe,Jane	11111	2525	FantasyLand	1/12/17 15:21	1/10/17 17:52	NULL	1/12/17 15:21	NULL
    Doe,Jane	11111	2525	AdventureLand	1/12/17 18:24	NULL	1/12/17 15:21	1/12/17 18:24	NULL
    Doe,Jane	11111	2525	Tomorrowland	1/14/17 14:27	NULL	1/12/17 18:24	1/14/17 14:27	NULL
    Doe,Jane	11111	2525	AdventureLand	1/14/17 16:03	NULL	1/14/17 14:27	1/14/17 16:03	NULL
    Doe,Jane	11111	2525	Tomorrowland	1/17/17 11:42	NULL	1/14/17 16:03	1/17/17 11:42	NULL
    Doe,Jane	11111	2525	AdventureLand	1/17/17 11:43	NULL	1/17/17 11:42	1/17/17 11:43	NULL
    Doe,Jane	11111	2525	Tomorrowland	1/18/17 12:00	NULL	1/17/17 11:43	1/18/17 12:00	NULL
    Doe,Jane	11111	2525	TTH 19W ADULT INT	1/19/17 11:05	NULL	1/18/17 12:00	1/19/17 11:05	NULL
    Doe,Jane	11111	2525	AdventureLand	1/19/17 16:46	NULL	1/19/17 11:05	1/19/17 16:46	NULL
    Doe,Jane	11111	2525	Tomorrowland	1/23/17 15:52	NULL	1/19/17 16:46	1/23/17 15:52	NULL
    Doe,Jane	11111	2525	Tomorrowland	1/23/17 16:03	NULL	1/23/17 15:52	1/23/17 16:03	NULL
    Doe,Jane	11111	2525	Tomorrowland	1/23/17 20:45	NULL	1/23/17 16:03	1/23/17 20:45	NULL
    Doe,Jane	11111	2525	LibertySquare	1/25/17 19:06	NULL	1/23/17 20:45	1/25/17 19:06	NULL
    Doe,Jane	11111	2525	FrontierLand	2/3/17 20:45	NULL	1/25/17 19:06	NULL	2/3/17 20:45
    
    */
    

    Wednesday, June 14, 2017 1:18 PM