Cursor help

Question

Post reply

Cursor help

piotrka

SSCrazy

Points: 2030
More actions
April 5, 2012 at 5:04 pm

#258891

Hi guys

I need to calculate a patient’s length of stay for every nurse station that patient has been to. The first table is the source table I already have and the second table is the output I am looking for. What I need to do is to find out registration and discharge date for every nurse station? A single cursor doesn't seem to work. What would you propose?

Thanks

CREATE TABLE #Tbl_PATIENT_ADM (

ADT_SOURCE VARCHAR(30),

PATIENT_NUM VARCHAR(20),

PATIENT_NURSESTATION VARCHAR(10),

REGISTER_DTIME DATETIME

)

INSERT INTO #Tbl_PATIENT_ADM

SELECT 'Register','54689', '260', '12/30/11 9:37'

UNION

SELECT 'OutPatToInPat', '54689', '6S', '12/30/11 22:34'

UNION

SELECT 'Transfer','54689','7W','1/1/12 20:27'

UNION

SELECT 'Transfer','54689','SICU','1/5/12 19:06'

UNION

SELECT 'Transfer','54689','8S','1/7/12 22:21'

UNION

SELECT 'Transfer','54689','SICU','1/9/12 13:20'

UNION

SELECT 'Transfer','54689','8S','1/13/12 18:15'

UNION

SELECT 'Transfer','54689','8S','1/13/12 18:20'

UNION

SELECT 'Discharge','54689','8S','1/18/12 18:30'

SELECT *

FROM #Tbl_PATIENT_ADM

ORDER BY REGISTER_DTIME

DROP TABLE #Tbl_PATIENT_ADM

CREATE TABLE #Tbl_PATIENT_STAY (

ADT_SOURCE VARCHAR(30),

PATIENT_NUM VARCHAR(20),

PATIENT_NURSESTATION VARCHAR(10),

REGISTER_DTIME DATETIME,

PTNRSTA_REGISTARTION DATETIME,

PTNRSTA_DISCHARGE DATETIME,

PTNRSTA_STAY FLOAT

)

INSERT INTO #Tbl_PATIENT_STAY

SELECT 'Register','54689', '260', '12/30/11 9:37','12/30/11 9:37', '12/30/11 22:34', '0'

UNION

SELECT 'OutPatToInPat', '54689', '6S', '12/30/11 22:34','12/30/11 22:34', '1/1/12 20:27', '2'

UNION

SELECT 'Transfer','54689','7W','1/1/12 20:27','1/1/12 20:27', '1/5/12 19:06', '4'

UNION

SELECT 'Transfer','54689','SICU','1/5/12 19:06','1/5/12 19:06', '1/7/12 22:21', '2'

UNION

SELECT 'Transfer','54689','8S','1/7/12 22:21','1/7/12 22:21', '1/9/12 13:20', '2'

UNION

SELECT 'Transfer','54689','SICU','1/9/12 13:20','1/9/12 13:20', '1/13/12 18:15', '4'

UNION

SELECT 'Transfer','54689','8S','1/13/12 18:15','1/13/12 18:15', '1/18/12 18:30', '5'

UNION

SELECT 'Transfer','54689','8S','1/13/12 18:20','1/13/12 18:15', '1/18/12 18:30', '5'

UNION

SELECT 'Discharge','54689','8S','1/18/12 18:30','1/13/12 18:15', '1/18/12 18:30', '5'

SELECT *

FROM #Tbl_PATIENT_STAY

ORDER BY REGISTER_DTIME

DROP TABLE #Tbl_PATIENT_STAY

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

CapnHector SSCoach Points: 16143 More actions · Answer 1

CREATE TABLE #Tbl_PATIENT_ADM (



	ADT_SOURCE VARCHAR(30),

	PATIENT_NUM VARCHAR(20),

	PATIENT_NURSESTATION VARCHAR(10),

	REGISTER_DTIME DATETIME

   )

 INSERT INTO #Tbl_PATIENT_ADM

 SELECT 'Register','54689',	'260', '12/30/11 9:37'

 UNION

 SELECT 'OutPatToInPat', '54689', '6S', '12/30/11 22:34'

 UNION

 SELECT 'Transfer','54689','7W','1/1/12 20:27'

 UNION

 SELECT 'Transfer','54689','SICU','1/5/12 19:06'

 UNION

 SELECT 'Transfer','54689','8S','1/7/12 22:21'

 UNION

 SELECT 'Transfer','54689','SICU','1/9/12 13:20'

 UNION

 SELECT 'Transfer','54689','8S','1/13/12 18:15'

 UNION

 SELECT 'Transfer','54689','8S','1/13/12 18:20'

 UNION

 SELECT 'Discharge','54689','8S','1/18/12 18:30'

 SELECT *

 FROM #Tbl_PATIENT_ADM

 ORDER BY REGISTER_DTIME

 DROP TABLE #Tbl_PATIENT_ADM

CREATE TABLE #Tbl_PATIENT_STAY (

	ADT_SOURCE VARCHAR(30),

	PATIENT_NUM VARCHAR(20),

	PATIENT_NURSESTATION VARCHAR(10),

	REGISTER_DTIME DATETIME,

	PTNRSTA_REGISTARTION DATETIME,

	PTNRSTA_DISCHARGE DATETIME,

	PTNRSTA_STAY FLOAT

   )

 INSERT INTO #Tbl_PATIENT_STAY

 SELECT 'Register','54689',	'260', '12/30/11 9:37','12/30/11 9:37',	'12/30/11 22:34',	'0'

 UNION

 SELECT 'OutPatToInPat', '54689', '6S', '12/30/11 22:34','12/30/11 22:34',	'1/1/12 20:27',	'2'

 UNION

 SELECT 'Transfer','54689','7W','1/1/12 20:27','1/1/12 20:27',	'1/5/12 19:06',	'4'

 UNION

 SELECT 'Transfer','54689','SICU','1/5/12 19:06','1/5/12 19:06',	'1/7/12 22:21',	'2'

 UNION

 SELECT 'Transfer','54689','8S','1/7/12 22:21','1/7/12 22:21',	'1/9/12 13:20',	'2'

 UNION

 SELECT 'Transfer','54689','SICU','1/9/12 13:20','1/9/12 13:20',	'1/13/12 18:15',	'4'

 UNION

 SELECT 'Transfer','54689','8S','1/13/12 18:15','1/13/12 18:15',	'1/18/12 18:30',	'5'

 UNION

 SELECT 'Transfer','54689','8S','1/13/12 18:20','1/13/12 18:15',	'1/18/12 18:30',	'5'

 UNION

 SELECT 'Discharge','54689','8S','1/18/12 18:30','1/13/12 18:15',	'1/18/12 18:30',	'5'

 SELECT *

 FROM #Tbl_PATIENT_STAY

 ORDER BY REGISTER_DTIME

DROP TABLE #Tbl_PATIENT_STAY

here it is formatted nicely

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

Considering that you input data is only a half of what your output data is, your going to have to describe how the data describes "in" and "out" times. Consider the "6S" entry to see what I mean.

😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

CapnHector SSCoach Points: 16143 More actions · Answer 3

Jeff Moden (4/5/2012)

Considering that you input data is only a half of what your output data is, your going to have to describe how the data describes "in" and "out" times. Consider the "6S" entry to see what I mean.

😉

i think in is the first time and out is the next earliest time stamp to a different area so 6s has an in of '12/30/11 22:34' and an out of '1/1/12 20:27' (the entry for '7W') if i am reading his sample output correct.

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]

piotrka SSCrazy Points: 2030 More actions · Answer 4

That is exactly what the 2 extra columns are; the registration date and the next earliest registration time to the new nurse station. The problem I am having is that a patient can move in and out of any station and come back at a different date. I need to find the in date and out date every time the patient moves. The last column is just a datediff between the previous 2 and is not an issue.

CapnHector SSCoach Points: 16143 More actions · Answer 5

piotrka (4/5/2012)

That is exactly what the 2 extra columns are; the registration date and the next earliest registration time to the new nurse station. The problem I am having is that a patient can move in and out of any station and come back at a different date. I need to find the in date and out date every time the patient moves. The last column is just a datediff between the previous 2 and is not an issue.

so at '1/13/12 18:15' and '1/13/12 18:20' where there are 2 8s that should be a 5 min stay? if so this became kinda easy

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]

piotrka SSCrazy Points: 2030 More actions · Answer 6

piotrka

SSCrazy

Points: 2030

April 6, 2012 at 5:57 am

#1470046

I don't think the transfer did happen since it is only 5 minutes difference between the 2 registrations and the nurse station didn't change but it may work for me if I calculate it anyway.

CapnHector SSCoach Points: 16143 More actions · Answer 7

ok know how im attacking it. have to boot up my other machine to get every thing coded preaty and tested.

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]

CapnHector SSCoach Points: 16143 More actions · Answer 8

with cte as (select row_number() over (Partition by PATIENT_NUM order by REGISTER_DTIME asc) AS Row, * FROM #Tbl_PATIENT_ADM)



select a.PATIENT_NUM, a.ADT_SOURCE, a.PATIENT_NURSESTATION, a.REGISTER_DTIME, b.REGISTER_DTIME, DATEDIFF(Day,a.REGISTER_DTIME,b.REGISTER_DTIME)

  from cte a

 LEFT JOIN cte b

    ON a.PATIENT_NUM = b.PATIENT_NUM

   AND a.Row = b.Row + (-1)

ORDER BY a.Row

the common table expression is used to number the rows of each patient in ascending date time order so we can run this on your table and it should be correct for all patients. the query joing the cte to its self says that the rows for each patient are joined and when the row number for a is one less than b. (a=1, b=2) the left join is so we get your discharged to show up which being the last status has a null match for the next row. then in the select we select the columns you want and then run a datediff (or some other method of your choice) on a.REGISTER_DTIME and b.REGISTER_DTIME

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]

CapnHector SSCoach Points: 16143 More actions · Answer 9

to limit the patients returned you can put your conditions in a where clause in the query (the self join giving your results) or if its a large table your running this on i would look at putting any limiting in the CTE its self to limit how much stuff is self joined.

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 10

capn.hector (4/5/2012)

Jeff Moden (4/5/2012)

Considering that you input data is only a half of what your output data is, your going to have to describe how the data describes "in" and "out" times. Consider the "6S" entry to see what I mean.

😉

i think in is the first time and out is the next earliest time stamp to a different area so 6s has an in of '12/30/11 22:34' and an out of '1/1/12 20:27' (the entry for '7W') if i am reading his sample output correct.

Ah. Of course. I'm not sure why but I totally missed that.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

CapnHector SSCoach Points: 16143 More actions · Answer 11

Jeff Moden (4/6/2012)

capn.hector (4/5/2012)

Jeff Moden (4/5/2012)

Considering that you input data is only a half of what your output data is, your going to have to describe how the data describes "in" and "out" times. Consider the "6S" entry to see what I mean.

😉

i think in is the first time and out is the next earliest time stamp to a different area so 6s has an in of '12/30/11 22:34' and an out of '1/1/12 20:27' (the entry for '7W') if i am reading his sample output correct.

Ah. Of course. I'm not sure why but I totally missed that.