SP for patient arrival,Discharges and census

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

SP for patient arrival,Discharges and census

Author

Topic

sreenu9f
Yak Posting Veteran

73 Posts

Posted - 2013-11-15 : 09:57:07

Hi,
I am trying to create a SP which lists the total number of patients Arrivals, Total Discharges,Total transfers and total patients in a bed on a given hospital unit every hour.
The table that i am working has data like below each patient has a unique VisitID and arrivals are captured by a code ENADMIN, Transfers as TFRADMIN,discharges as ENDISIN. when the patient is transferred from one floor to the other it will be an arrival on the new floor and the hardest piece is the time that was spent on a given unit.
Any help and suggestions are greatly appreciated.
Like in the VisitID 10001501299 the patient arrived on HOLD on11/5@9:04 then transferred to 5W on 11/5@16:47 and then stayed on that floor till 11/8 12:52 and then got discharged. so the output should include by hours this patient on multiple floors based on the arrivals, discharges and transfers.
Thanks,

VisitID Code EffectiveDateTime LocationID
10001501299 ENADMIN 11/5/2013 9:04 HOLD
10001501299 TFRADMIN 11/5/2013 16:47 5W
10001501299 ENDISIN 11/8/2013 12:52 NULL
10001502123 ENADMIN 11/5/2013 10:44 HOLD
10001502123 TFRADMIN 11/5/2013 18:27 5S
10001502123 ENDISIN 11/7/2013 13:19 NULL
10001505051 ENADMIN 11/8/2013 10:29 LDR
10001505051 TFRADMIN 11/8/2013 14:27 LDR
10001505051 TFRADMIN 11/8/2013 15:25 N3S
10001505051 TFRADMIN 11/10/2013 11:11 3S
10001505051 ENDISIN 11/11/2013 11:30 NULL
10001519626 ENADMIN 11/12/2013 6:13 HOLD
10001519626 TFRADMIN 11/12/2013 12:41 5W
10001520313 ENADMIN 11/5/2013 6:03 HOLD
10001520313 TFRADMIN 11/5/2013 12:26 5S
10001520313 ENDISIN 11/8/2013 12:39 NULL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-15 : 11:46:41

Please post you expected output for the sample data above.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

sreenu9f
Yak Posting Veteran

73 Posts

Posted - 2013-11-15 : 14:12:35

Hi Visakh,
for the first visitID 10001501299 the results should be as below and the same iteration should be performed on all the visits and should be group by date and location.
Thanks in advance for all your help.

Date Location ArrivalsCount ArrivalHour TransfersCount TransfersHour DischargesCount Discharges Hour CensusCount Census Hour
11/5/2013 Hold 1 9 1 16 1 9,10,11,12,13,14,15,16
11/5/2013 5W 1 16 1 16,17,18,19,20,21,22,23,24
11/6/2013 5W 1 1 to 24
11/7/2013 5W 1 1 to 24
11/8/2013 5W 1 12 1 1 to 12 hours

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-16 : 01:17:38

can you properly format your output. I'm having difficulty in understanding which value belongs to which columns
Also explain rules based on which you'll get above output.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

sreenu9f
Yak Posting Veteran

73 Posts

Posted - 2013-11-16 : 13:35:09

Hi visakh,
I am having a hard time pasting the output here the format is not good.
I will explain the process.
In a hospital there will be several floors which have different rooms where patients
Are places and treated.
The patients arrives on to a given room in a floor from emergency dept or from doctors office in the table the code is ENADMIN and the time when this happend is effective date time column.
The patients will move from one floor to other floor meaning transfer out from one floor
And transfer in to other floor the time when this happens is column effective date time and the code is TFRADMIN.
The patient is finally discharged once treatment is completed to home with code as DISIN and time when this happens is in effective date time column.

I am trying to get on a given day like yesterday by each floor the count of arrivals, transfers in, transfers out and discharges by hour(1 to 24).

Thanks in advance for all your help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-17 : 11:45:44

what does these represent? 9,10,11,12,13,14,15,16? the hours? whats the basis of listing them? I see 9,10 etc but your actual start time is 9:04

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

sreenu9f
Yak Posting Veteran

73 Posts

Posted - 2013-11-17 : 13:51:45

Yes the start time is 9 but the patient stayed on HOLD till 16 hours
That's why it listed as 9 through 16.
At 16:47 the patient moved ( transferred out from hold to 5W) from this time till the next event that is discharge in 11/8 the patient stayed on 5w hence for all this time the patient is considered on the 5th floor.
Thanks

sreenu9f
Yak Posting Veteran

73 Posts

Posted - 2013-11-17 : 18:16:14

Sorry the earlier output expected didn't formatted properly so i have split it into two halves ( by seperating columns)

First Half of the result
Date Location ArvlCount ArvllHour TrfInCount TrfInHour TrfOutCount TrfOutHour
11/5/2013 HOLD 1 9 1 16
11/5/2013 5W 1 16
11/6/2013 5W
11/7/2013 5W
11/8/2013 5W

Second Half

Date DischsCount DischsHour CensCount CensHour
11/5/2013 1 9 to 16 hours
11/5/2013 1 16 to 24 hours
11/6/2013 1 1 to 24 hours
11/7/2013 1 1 to 24 hours
11/8/2013 1 1 to 12 hours 1 1 to 12 hours

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources