none
Query Help

    Question

  • Good morning,

    I am trying to create a script that shows me a list of unique patients, how long they stayed and what wards (if any) they moved between during their stay.

    I have produced a script that gives me the list of patients and their length of stay but I dont know how to add the extra column to show the wards they have been on.

    My current script is this:

    SELECT
    PT_CODE,
    EVENT_NO,
    TRUE_LOS AS LOS_MINS,
    FROM dbo.vwTOPASInpatients

    Event_No is the Unique field in my code.

    The Ward Movements are stored in the dbo.vwTOPASWardMovements table and Here you will find multiple rows per event_no depending on how many movements they made. The Field Name in this table I want to pull through to my code is W_CODE. If there are multiple ward movements for an event_no then I want my code to pull all of the wards out with a SEMICOLON seperating them if possible.

    I want an example of my data to look like this:

    PT_CODE, EVENT_NO, LOS_MIINS, OTHER_WARDS

    J212122,  38475659,  1000,  3B;4C;CCU

    Can Anybody help

    Thursday, August 01, 2013 1:44 AM

Answers

  • Try this :

    declare @vwTOPASInpatients table(
    PT_CODE varchar(10),
    EVENT_NO INT,
    TRUE_LOS INT)
    
    declare @vwTOPASWardMovements table(EVENT_NO INT, W_CODE varchar(10))
    
    insert into @vwTOPASInpatients values('J212122',38475659,1000)
    
    insert into @vwTOPASWardMovements 
    values(38475659,'3B'),
    (38475659,'4C'),
    (38475659,'CCU')
    
    
    select PT_CODE,EVENT_NO,TRUE_LOS,
    		STUFF((	select ';' + W_CODE
    			from @vwTOPASWardMovements v1
    			where v.EVENT_NO = v1.EVENT_NO
    			for XML path('')
    		),1,1,'') AS Other_Wards
    from @vwTOPASInpatients v 


    Please vote if you find this posting was helpful or Mark it as answered.

    Thursday, August 01, 2013 5:07 AM

All replies