November 16, 2013 at 8:21 pm
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,
VisitIDCodeEffectiveDateTimeLocationIDOldLocationID
10001520449ENADMIN11/12/13 7:35 AMLDRNULL
10001520313ENADMIN11/5/13 6:03 AMHOLDNULL
10001520313TFRADMIN11/5/13 12:26 PM5SHOLD
10001520313ENDISIN11/8/13 12:39 PMNULLNULL
10001519626ENADMIN11/12/13 6:13 AMHOLDNULL
10001519626TFRADMIN11/12/13 12:41 PM5WHOLD
10001505051ENADMIN11/8/13 10:29 AMLDRNULL
10001505051TFRADMIN11/8/13 2:27 PMLDRDR
10001505051TFRADMIN11/8/13 3:25 PM3SDR
10001505051TFRADMIN11/10/13 11:11 AM3SN3S
10001505051ENDISIN11/11/13 11:30 AMNULLNULL
10001502123ENADMIN11/5/13 10:44 AMHOLDNULL
10001502123TFRADMIN11/5/13 6:27 PM5SHOLD
10001502123ENDISIN11/7/13 1:19 PMNULLNULL
10001501299ENADMIN11/5/13 9:04 AMHOLDNULL
10001501299TFRADMIN11/5/13 4:47 PM5WLD
10001501299ENDISIN11/8/13 12:52 PMNULLNULL
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.
November 16, 2013 at 9:12 pm
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...
November 17, 2013 at 3:44 am
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...
November 17, 2013 at 1:02 pm
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.
DateLocationArrivalsCountArrivalHourTransfersCountTransfersHour DischargesCountDischarges HourCensusCountCensus Hour
11/5/2013Hold19116 19,10,11,12,13,14,15,16
11/5/20135W116 116,17,18,19,20,21,22,23,24
11/6/20135W 11 to 24
11/7/20135W 11 to 24
11/8/20135W 11211 to 12 hours
November 17, 2013 at 4:14 pm
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 ArvlCountArvllHourTrfInCountTrfInHour TrfOutCount TrfOutHour
11/5/2013HOLD19 1 16
11/5/20135W 1 16
11/6/20135W
11/7/20135W
11/8/20135W
Second Half
DateDischsCountDischsHourCensCountCensHour
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/201311 to 12 hours 1 1 to 12 hours
November 17, 2013 at 4:24 pm
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.
November 17, 2013 at 9:02 pm
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[/url] 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!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply