Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

T sql for patient arrivals, transfers In and Out and discharges and census on a hospital floor by hour Expand / Collapse
Author
Message
Posted Saturday, November 16, 2013 8:21 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, December 07, 2013 7:21 PM
Points: 3, Visits: 33
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.
Post #1514949
Posted Saturday, November 16, 2013 9:12 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 10:55 AM
Points: 571, Visits: 3,729
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...
Post #1514950
Posted Sunday, November 17, 2013 3:44 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 9:38 AM
Points: 6,908, Visits: 12,624
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
Post #1514963
Posted Sunday, November 17, 2013 1:02 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, December 07, 2013 7:21 PM
Points: 3, Visits: 33
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
Post #1515016
Posted Sunday, November 17, 2013 4:14 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, December 07, 2013 7:21 PM
Points: 3, Visits: 33
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




Post #1515032
Posted Sunday, November 17, 2013 4:24 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 9:38 AM
Points: 6,908, Visits: 12,624
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
Post #1515034
Posted Sunday, November 17, 2013 9:02 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 10:55 AM
Points: 571, Visits: 3,729
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!
Post #1515056
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse