locked
How do I merge 2 rows into 1 row RRS feed

  • Question

  • So below is a small sample of test data which is effectively the output of a cte. Is it possible to do the following:

    HGHD & HICU are effectively the same place/location and need to treated as a single period regardless of which order they are in. This is reflected in the field DQ_1_PERIOD flagged as Y.

    For info only a Spell (HOSPITAL_PROVIDER_SPELL_NUMBER) is an entire period someone was in hospital, the WARD_CODE represent the different location someone was in during that entire stay. The field TR_SEQUENCE_NUMBER_WITHIN_SPELL is a sequential order in which they were in those location with START and END dates and how long they were on each location (LENGTH_OF_STAY_IN_MINUTES)

    The goal is the get the output to 4 rows where rows 3/4 (HICU/HGHD) become a single period which starts (2019-06-04 13:57:00) and ends (2019-06-05 23:52:00) forming a new location HICU/HGHD with a sequence of 3, the last one row would then become sequence 4.

    It would also be a bonus if the length of stay can be combined to show 2035.

    I thought I could somehow use the field DQ_1_PERIOD to identify these instances and select the start date of the minimum sequence number as the start date and then use the end date of the maximum sequence number to represent the end date. Bear in mind this could be more than 2 eg patient could go from HICU to HGHD back to HICU and back to HGHD which would result in 4 periods which need combining into 1, they would all have the DQ_1_PERIOD flag set to Y and could potentially be matched the same as mentioned above.

    Just not sure how !

     

    DECLARE @CC_DQTABLE TABLE( 
    LOCAL_PATIENT_NUMBER VARCHAR(255) NULL, 
    HOSPITAL_PROVIDER_SPELL_NUMBER VARCHAR(50) NULL, 
    TR_SEQUENCE_NUMBER_WITHIN_SPELL INT NULL,
    START_DATE_TIME_WARD_STAY DATETIME2 NULL,
    END_DATE_TIME_WARD_STAY DATETIME2 NULL,
    WARD_CODE VARCHAR(50) NULL ,
    LENGTH_OF_STAY_IN_MINUTES INT NULL ,
    DQ_1_PERIOD CHAR(1) NULL 
    ); 
    
    INSERT INTO @CC_DQTABLE (LOCAL_PATIENT_NUMBER,HOSPITAL_PROVIDER_SPELL_NUMBER,TR_SEQUENCE_NUMBER_WITHIN_SPELL,START_DATE_TIME_WARD_STAY,END_DATE_TIME_WARD_STAY,WARD_CODE,LENGTH_OF_STAY_IN_MINUTES,DQ_1_PERIOD)
    VALUES
    ('HEY999TEST','600072229999','1','03/06/2019 16:00','03/06/2019 21:55','H4','355','N'),
    ('HEY999TEST','600072229999','2','03/06/2019 21:55','04/06/2019 13:57','H12','962','N'),
    ('HEY999TEST','600072229999','3','04/06/2019 13:57','04/06/2019 19:38','HICU','341','Y'),
    ('HEY999TEST','600072229999','4','04/06/2019 19:38','05/06/2019 23:52','HGHD','1694','Y'),
    ('HEY999TEST','600072229999','5','05/06/2019 23:52','07/06/2019 17:45','H4','2513','N')
    
    
    SELECT * FROM @CC_DQTABLE

    Thursday, November 21, 2019 8:03 AM

Answers

  • DECLARE @CC_DQTABLE TABLE( 
    LOCAL_PATIENT_NUMBER VARCHAR(255) NULL, 
    HOSPITAL_PROVIDER_SPELL_NUMBER VARCHAR(50) NULL, 
    TR_SEQUENCE_NUMBER_WITHIN_SPELL INT NULL,
    START_DATE_TIME_WARD_STAY DATETIME2 NULL,
    END_DATE_TIME_WARD_STAY DATETIME2 NULL,
    WARD_CODE VARCHAR(50) NULL ,
    LENGTH_OF_STAY_IN_MINUTES INT NULL ,
    DQ_1_PERIOD CHAR(1) NULL 
    ); 
    
    INSERT INTO @CC_DQTABLE (LOCAL_PATIENT_NUMBER,HOSPITAL_PROVIDER_SPELL_NUMBER,TR_SEQUENCE_NUMBER_WITHIN_SPELL,START_DATE_TIME_WARD_STAY,END_DATE_TIME_WARD_STAY,WARD_CODE,LENGTH_OF_STAY_IN_MINUTES,DQ_1_PERIOD)
    VALUES
    ('HEY999TEST','600072229999','1','03/06/2019 16:00','03/06/2019 21:55','H4','355','N'),
    ('HEY999TEST','600072229999','2','03/06/2019 21:55','04/06/2019 13:57','H12','962','N'),
    ('HEY999TEST','600072229999','3','04/06/2019 13:57','04/06/2019 19:38','HICU','341','Y'),
    ('HEY999TEST','600072229999','4','04/06/2019 19:38','05/06/2019 23:52','HGHD','1694','Y'),
    ('HEY999TEST','600072229999','5','05/06/2019 23:52','07/06/2019 17:45','H4','2513','N')
    
     
    ;with mycte as (
    SELECT 
     
    LOCAL_PATIENT_NUMBER
    ,HOSPITAL_PROVIDER_SPELL_NUMBER
    ,
      TR_SEQUENCE_NUMBER_WITHIN_SPELL
    , START_DATE_TIME_WARD_STAY 
    , END_DATE_TIME_WARD_STAY 
    ,Case when WARD_CODE in ('HICU','HGHD') then 'HICU/HGHD' else WARD_CODE end WARD_CODE
     
    , LENGTH_OF_STAY_IN_MINUTES 
    ,DQ_1_PERIOD
    --create a grp to handle island and gap issue
    ,Row_number() Over(Partition by LOCAL_PATIENT_NUMBER
    ,HOSPITAL_PROVIDER_SPELL_NUMBER
    Order by  START_DATE_TIME_WARD_STAY )
    - Row_number() Over(Partition by LOCAL_PATIENT_NUMBER
    ,HOSPITAL_PROVIDER_SPELL_NUMBER
    ,Case when WARD_CODE in ('HICU','HGHD') then 'HICU/HGHD' else WARD_CODE end
    Order by  START_DATE_TIME_WARD_STAY ) grp
    
    FROM @CC_DQTABLE
    )
    
    Select 
    LOCAL_PATIENT_NUMBER
    ,HOSPITAL_PROVIDER_SPELL_NUMBER
    ,Row_number() Over(Partition by LOCAL_PATIENT_NUMBER
    ,HOSPITAL_PROVIDER_SPELL_NUMBER
    Order by   min(START_DATE_TIME_WARD_STAY)) as TR_SEQUENCE_NUMBER_WITHIN_SPELL 
    , min(START_DATE_TIME_WARD_STAY) as START_DATE_TIME_WARD_STAY
    , max(END_DATE_TIME_WARD_STAY) as END_DATE_TIME_WARD_STAY 
    , WARD_CODE
    ,sum(LENGTH_OF_STAY_IN_MINUTES) LENGTH_OF_STAY_IN_MINUTES
    ,DQ_1_PERIOD
    From mycte
    
    Group by LOCAL_PATIENT_NUMBER
    ,HOSPITAL_PROVIDER_SPELL_NUMBER 
    ,WARD_CODE 
    ,DQ_1_PERIOD, grp
    

    • Marked as answer by SimonKEvans Monday, November 25, 2019 7:33 AM
    Friday, November 22, 2019 3:14 PM

All replies

  • Not sure if I understand correctly your request. Does this help?

    SELECT  b.LOCAL_PATIENT_NUMBER

    ,b.HOSPITAL_PROVIDER_SPELL_NUMBER

    ,b.TR_SEQUENCE_NUMBER_WITHIN_SPELL

    ,b.START_DATE_TIME_WARD_STAY

    ,e.END_DATE_TIME_WARD_STAY

    ,concat(b.WARD_CODE,'/',e.WARD_CODE) as WARD_CODE

    ,b.LENGTH_OF_STAY_IN_MINUTES+e.LENGTH_OF_STAY_IN_MINUTES as LENGTH_OF_STAY_IN_MINUTES

    ,b.DQ_1_PERIOD

    FROM @CC_DQTABLE b

    join @CC_DQTABLE e 

    on b.END_DATE_TIME_WARD_STAY=e.START_DATE_TIME_WARD_STAY

    where b.DQ_1_PERIOD=e.DQ_1_PERIOD  and b.DQ_1_PERIOD='Y'


    Thursday, November 21, 2019 8:25 AM
  • Not sure if I understand correctly your request. Does this help?

    SELECT  b.LOCAL_PATIENT_NUMBER

    ,b.HOSPITAL_PROVIDER_SPELL_NUMBER

    ,b.TR_SEQUENCE_NUMBER_WITHIN_SPELL

    ,b.START_DATE_TIME_WARD_STAY

    ,e.END_DATE_TIME_WARD_STAY

    ,concat(b.WARD_CODE,'/',e.WARD_CODE) as WARD_CODE

    ,b.LENGTH_OF_STAY_IN_MINUTES+e.LENGTH_OF_STAY_IN_MINUTES as LENGTH_OF_STAY_IN_MINUTES

    ,b.DQ_1_PERIOD

    FROM @CC_DQTABLE b

    join @CC_DQTABLE e 

    on b.END_DATE_TIME_WARD_STAY=e.START_DATE_TIME_WARD_STAY

    where b.DQ_1_PERIOD=e.DQ_1_PERIOD  and b.DQ_1_PERIOD='Y'


    Yes that's spot on my good man, now just need the other included eg I would expect to see sequence 1,2,3,4, the 3rd one is exactly how you have done it with the others unchanged other than sequence 5 will now be sequence 4 as we have merged 3/4 into one.

    Thursday, November 21, 2019 8:33 AM
  • After having updated your table with the merged row and having removed row 4 you can renumber the sequence according the start_date_time_ward_stay.

    Something like update table set seq = row_number() over(partition by ... order by start_date_time_ward_stay)

    Please pay attention that you can't use windowing functions in the update-statement directly.

    By the way: What is the sequence good for? Any foreign keys on that?

    Thursday, November 21, 2019 11:03 AM
  • Hi ,

    Could you please share us your  your expected result? So that we’ll get a right direction and make some test.

     

    When you post your issue, please refer POSTING TIPS - Code, Images, Hyperlinks, Details.

     

    Best Regards,

    Rachel


    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.

    Friday, November 22, 2019 6:45 AM
  • Expected Outcome is below:


    DECLARE @CC_DQTABLE TABLE( 
    LOCAL_PATIENT_NUMBER VARCHAR(255) NULL, 
    HOSPITAL_PROVIDER_SPELL_NUMBER VARCHAR(50) NULL, 
    TR_SEQUENCE_NUMBER_WITHIN_SPELL INT NULL,
    START_DATE_TIME_WARD_STAY DATETIME2 NULL,
    END_DATE_TIME_WARD_STAY DATETIME2 NULL,
    WARD_CODE VARCHAR(50) NULL ,
    LENGTH_OF_STAY_IN_MINUTES INT NULL ,
    DQ_1_PERIOD CHAR(1) NULL 
    ); 
    INSERT INTO @CC_DQTABLE (LOCAL_PATIENT_NUMBER,HOSPITAL_PROVIDER_SPELL_NUMBER,TR_SEQUENCE_NUMBER_WITHIN_SPELL,START_DATE_TIME_WARD_STAY,END_DATE_TIME_WARD_STAY,WARD_CODE,LENGTH_OF_STAY_IN_MINUTES,DQ_1_PERIOD)
    VALUES
    ('HEY999TEST','600072229999','1','03/06/2019 16:00','03/06/2019 21:55','H4','355','N'),
    ('HEY999TEST','600072229999','2','03/06/2019 21:55','04/06/2019 13:57','H12','962','N'),
    ('HEY999TEST','600072229999','3','04/06/2019 13:57','2019-06-05 23:52','HICU/HGHD','2035','Y'),
    ('HEY999TEST','600072229999','4','05/06/2019 23:52','07/06/2019 17:45','H4','2513','N')
    
    SELECT * FROM @CC_DQTABLE
    
    


    • Edited by SimonKEvans Friday, November 22, 2019 10:09 AM sql editied
    Friday, November 22, 2019 10:08 AM
  • DECLARE @CC_DQTABLE TABLE( 
    LOCAL_PATIENT_NUMBER VARCHAR(255) NULL, 
    HOSPITAL_PROVIDER_SPELL_NUMBER VARCHAR(50) NULL, 
    TR_SEQUENCE_NUMBER_WITHIN_SPELL INT NULL,
    START_DATE_TIME_WARD_STAY DATETIME2 NULL,
    END_DATE_TIME_WARD_STAY DATETIME2 NULL,
    WARD_CODE VARCHAR(50) NULL ,
    LENGTH_OF_STAY_IN_MINUTES INT NULL ,
    DQ_1_PERIOD CHAR(1) NULL 
    ); 
    
    INSERT INTO @CC_DQTABLE (LOCAL_PATIENT_NUMBER,HOSPITAL_PROVIDER_SPELL_NUMBER,TR_SEQUENCE_NUMBER_WITHIN_SPELL,START_DATE_TIME_WARD_STAY,END_DATE_TIME_WARD_STAY,WARD_CODE,LENGTH_OF_STAY_IN_MINUTES,DQ_1_PERIOD)
    VALUES
    ('HEY999TEST','600072229999','1','03/06/2019 16:00','03/06/2019 21:55','H4','355','N'),
    ('HEY999TEST','600072229999','2','03/06/2019 21:55','04/06/2019 13:57','H12','962','N'),
    ('HEY999TEST','600072229999','3','04/06/2019 13:57','04/06/2019 19:38','HICU','341','Y'),
    ('HEY999TEST','600072229999','4','04/06/2019 19:38','05/06/2019 23:52','HGHD','1694','Y'),
    ('HEY999TEST','600072229999','5','05/06/2019 23:52','07/06/2019 17:45','H4','2513','N')
    
     
    ;with mycte as (
    SELECT 
     
    LOCAL_PATIENT_NUMBER
    ,HOSPITAL_PROVIDER_SPELL_NUMBER
    ,
      TR_SEQUENCE_NUMBER_WITHIN_SPELL
    , START_DATE_TIME_WARD_STAY 
    , END_DATE_TIME_WARD_STAY 
    ,Case when WARD_CODE in ('HICU','HGHD') then 'HICU/HGHD' else WARD_CODE end WARD_CODE
     
    , LENGTH_OF_STAY_IN_MINUTES 
    ,DQ_1_PERIOD
    --create a grp to handle island and gap issue
    ,Row_number() Over(Partition by LOCAL_PATIENT_NUMBER
    ,HOSPITAL_PROVIDER_SPELL_NUMBER
    Order by  START_DATE_TIME_WARD_STAY )
    - Row_number() Over(Partition by LOCAL_PATIENT_NUMBER
    ,HOSPITAL_PROVIDER_SPELL_NUMBER
    ,Case when WARD_CODE in ('HICU','HGHD') then 'HICU/HGHD' else WARD_CODE end
    Order by  START_DATE_TIME_WARD_STAY ) grp
    
    FROM @CC_DQTABLE
    )
    
    Select 
    LOCAL_PATIENT_NUMBER
    ,HOSPITAL_PROVIDER_SPELL_NUMBER
    ,Row_number() Over(Partition by LOCAL_PATIENT_NUMBER
    ,HOSPITAL_PROVIDER_SPELL_NUMBER
    Order by   min(START_DATE_TIME_WARD_STAY)) as TR_SEQUENCE_NUMBER_WITHIN_SPELL 
    , min(START_DATE_TIME_WARD_STAY) as START_DATE_TIME_WARD_STAY
    , max(END_DATE_TIME_WARD_STAY) as END_DATE_TIME_WARD_STAY 
    , WARD_CODE
    ,sum(LENGTH_OF_STAY_IN_MINUTES) LENGTH_OF_STAY_IN_MINUTES
    ,DQ_1_PERIOD
    From mycte
    
    Group by LOCAL_PATIENT_NUMBER
    ,HOSPITAL_PROVIDER_SPELL_NUMBER 
    ,WARD_CODE 
    ,DQ_1_PERIOD, grp
    

    • Marked as answer by SimonKEvans Monday, November 25, 2019 7:33 AM
    Friday, November 22, 2019 3:14 PM