Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


T sql for patient arrivals, transfers In and Out and discharges and census on a hospital floor by...


T sql for patient arrivals, transfers In and Out and discharges and census on a hospital floor by hour

Author
Message
sreenu9f
sreenu9f
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 37
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 OldLocationID
10001520449 ENADMIN 11/12/13 7:35 AM LDR NULL
10001520313 ENADMIN 11/5/13 6:03 AM HOLD NULL
10001520313 TFRADMIN 11/5/13 12:26 PM 5S HOLD
10001520313 ENDISIN 11/8/13 12:39 PM NULL NULL
10001519626 ENADMIN 11/12/13 6:13 AM HOLD NULL
10001519626 TFRADMIN 11/12/13 12:41 PM 5W HOLD
10001505051 ENADMIN 11/8/13 10:29 AM LDR NULL
10001505051 TFRADMIN 11/8/13 2:27 PM LDR DR
10001505051 TFRADMIN 11/8/13 3:25 PM 3S DR
10001505051 TFRADMIN 11/10/13 11:11 AM 3S N3S
10001505051 ENDISIN 11/11/13 11:30 AM NULL NULL
10001502123 ENADMIN 11/5/13 10:44 AM HOLD NULL
10001502123 TFRADMIN 11/5/13 6:27 PM 5S HOLD
10001502123 ENDISIN 11/7/13 1:19 PM NULL NULL
10001501299 ENADMIN 11/5/13 9:04 AM HOLD NULL
10001501299 TFRADMIN 11/5/13 4:47 PM 5W LD
10001501299 ENDISIN 11/8/13 12:52 PM NULL NULL


Also some additional information on 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.
pietlinden
pietlinden
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2193 Visits: 12553
Since you're new, I'll go easy on you... <g>

One thing to keep in mind when posting questions is that you're the only one with access to your database, so if you want a real answer, post real structures. (but NOT real data... and enough to explain what's going on... if 3 records will do, no need to add more!)

Here's a create table and insert statements...


create table #visit (
VisitID char(15),
VCode CHAR(8),
EffectiveDate DATETIME,
LocationID char(4),
OldLocationID char(4)
);

-- inserts---
INSERT INTO #visit VALUES('10001520449','ENADMIN', '11/12/13 7:35 AM','LDR',NULL);
INSERT INTO #visit VALUES('10001520313','ENADMIN', '11/5/13 6:03 AM','HOLD',NULL);
INSERT INTO #visit VALUES('10001520313','TFRADMIN', '11/5/13 12:26 PM', '5S','HOLD');
INSERT INTO #visit VALUES('10001520313', 'ENDISIN', '11/8/13 12:39 PM', NULL, NULL);
INSERT INTO #visit VALUES('10001519626', 'ENADMIN', '11/12/13 6:13 AM', 'HOLD', NULL);
INSERT INTO #visit VALUES('10001519626', 'TFRADMIN', '11/12/13 12:41 PM','5W', 'HOLD');
INSERT INTO #visit VALUES('10001505051', 'ENADMIN', '11/8/13 10:29 AM', 'LDR', NULL);
INSERT INTO #visit VALUES('10001505051', 'TFRADMIN', '11/8/13 2:27 PM', 'LDR', 'DR');
INSERT INTO #visit VALUES('10001505051', 'TFRADMIN', '11/8/13 3:25 PM', '3S', 'DR');
INSERT INTO #visit VALUES('10001505051', 'TFRADMIN', '11/10/13 11:11 AM', '3S', 'N3S');
INSERT INTO #visit VALUES('10001505051', 'ENDISIN', '11/11/13 11:30 AM', NULL, NULL);
INSERT INTO #visit VALUES('10001502123', 'ENADMIN', '11/5/13 10:44 AM', 'HOLD', NULL);
INSERT INTO #visit VALUES('10001502123', 'TFRADMIN', '11/5/13 6:27 PM', '5S', 'HOLD');
INSERT INTO #visit VALUES('10001502123', 'ENDISIN', '11/7/13 1:19 PM', NULL, NULL);
INSERT INTO #visit VALUES('10001501299', 'ENADMIN', '11/5/13 9:04 AM', 'HOLD',NULL);
INSERT INTO #visit VALUES('10001501299', 'TFRADMIN', '11/5/13 4:47 PM', '5W', 'LD');
INSERT INTO #visit VALUES('10001501299', 'ENDISIN', '11/8/13 12:52 PM', NULL, NULL);



As for the rest of the question... not sure yet... but if you make it easy for people to recreate your table(s) and some data, it's much easier for others to answer...
LutzM
LutzM
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7007 Visits: 13559
Now we have (almost) ready to use sample data.
"Almost" because I can't load the sample data "as is" due to a different DATEFORMAT setting, so you might consider in the future to always post date values as YYYYMMDD instead of MM/DD/YY.

What we also need is your expected result based on your sample data in the format you need it. Currently we can just guess what you're looking for...



Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
sreenu9f
sreenu9f
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 37
Thanks for the responses and sorry for not posting appropriately.
The final results should be in graph with 5 series on the y axis count of visits and on x axis hours 24 for each day.
The 5 series are arrivals(ENADMIN), transfers in ( Tfradmin) when the patient moves into that floor, transfer out (tfradmin) when patient moves out of that floor, discharges ( DISIN) per hour and then finally totally number of patients each hour on that floor.

For example in the data for visit 10001501299
I need the output as below so that I can plot the graph.

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
sreenu9f
sreenu9f
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 37
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
LutzM
LutzM
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7007 Visits: 13559
It still doesn't make much sense. Please provide the expected result in a SELECT ... format.
Something like

SELECT
'20131105' as Date,
'HOLD' as Location,
1 AS ArvlCount,
9 AS ArvllHour,
1 AS TrfInCount,
16 AS TrfInHour,
'?' AS TrfOutCount,
'?' AS TrfOutHour
UNION ALL
SELECT ...


You see what happened? Your expected output doesn't even match the number of colum names you provided.
Neither did you bother to change the date format.

I'm out of here until I see something that's ready to use. And I will not complain any more either.



Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
pietlinden
pietlinden
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2193 Visits: 12553
Okay, read this article and follow the instructions... Not trying to be mean, just trying to teach you how to use this site. Because if you don't follow the instructions, and people have a hard time helping you, you just won't get any help. So read this and try again.

Learning to ask good/complete questions takes practice. but once you get the basics down, you would be surprised how much you can learn here!

Happy reading and good luck!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search