1. These items are created in a reporting tool called business objects, which is hooked up to a "database" called healthquest. I use the word database loosely since data is somehow input like this.
2. Okay maybe not so much gaps in this example (there are in other examples), but at the very least overlapping times, meaning duplication in time, when the ultimate goal is a FULL UNIQUE PICTURE of how a patient passes through hospital on an intraday basis for their whole stay.
3. I believe times are entered manually.
4. Yes 292 belongs at the end of the previous line, I cut it wrong by accident.
5. The first line differs with the semicolons because I put those in to show you where the breaks in the data are, they are NOT part of the original data, I did it for illustration purposes.
6. I will be willing to eliminate a nursing stations (one or the the other) if it contains overlapping minutes, in the situation where there are two different nursing stations/departments.
7. I was hoping some VBA would possibly compare one line to another while holding the first line in a buffer in order to compare the next in the list, but I did not know exactly how to start it.
Even though you feel I would need something more robust than Excel or Access, I wanted to attempt something, at least on a small scale, with a few patients in a list rather than a whole file.
8. I couldn't agree more that the input is ridiculous. For some reason they don't understand the value of knowing the exact total minutes of time the patient stayed in the hospital, distinct and non-overlapping minutes, no less.
9. What I am aiming to produce from all this, is a unique and distinct full picture of time in the hospital for each patient stay by each nursing department, in chronological order, from the first day to the last day at the hospital, NOT merely a total minute amount. I will need to count the number of total unique nursing department switches (starting in the ER, moving to cancer floor, and moving back to ER for some reason IS 3 distinct nursing departments, but having three rows of data all labeled the same, is only 1 distinct nursing department). I will also need to count time within each distinct nursing dept.
Eric Lutz
________________________________
From: David Smart smartware.consulting@gmail.com>
To: ExcelVBA@yahoogroups.com
Sent: Wednesday, February 13, 2013 6:37 PM
Subject: Re: [ExcelVBA] Patient Intraday Look Table and its wonderful Dysfunctions
You haven't indicated where these items are created. Your example shows no
gaps in the times that I can see. If the times are entered manually, then
gaps are inevitable, as are minor overlaps sue only to mis-entering of
times.
You also have a 292 at the start of a line, where it should presumably be at
the end of the previous line. If this is in the actual data, then you have
a checking function which needs to be undertaken first.
Additionally, the first line differs from the rest, in that it has semicolon
separators. Again, the data will need reformatting before further analysis.
Compression of unnecessary multiple lines will not be all that simple. This
is because there are also duplications of the time periods, and these don't
line up. Worse, we have duplications that aren't even for the same
department (e.g. the first two lines). It is, of course, fairly
straightforward to separate out the different time lines and ensure that
they are cut down into single items; and it's also easy enough to eliminate
redundant time lines for the same department. But you are going to need to
handle the situation where overlapping time lines refer to different
departments (as in the first two lines), and even when time lines go nowhere
(as in the first line).
This is really a job for a professional programmer and will involve quite a
bit of data manipulation.
> Imagine a plethora of 200,000 patients in a years time,
> all ridiculously logged just like this.
If it's this many, then you want something more robust than Excel or Access.
It needs to be done in a full-scale programming language, and probably using
a full-scale database engine.
But, the input really is ridiculous. Why isn't it being fixed?
The other question is, what are you aiming to produce from all this? You
haven't said. But, if it's just to arrive at the patient's 25.12 days, then
all you need to do is look for the minimum and maximum date/time items for
the patient, and subtract.
Regards, Dave S
----- Original Message -----
From: "Barry White" mailto:imtigerwords%40yahoo.com>
To: mailto:ExcelVBA%40yahoogroups.com>
Sent: Thursday, February 14, 2013 6:26 AM
Subject: [ExcelVBA] Patient Intraday Look Table and its wonderful
Dysfunctions
Hello all,
Looking for a little help as to how to combat the three dysfunctions in this
table (don't roll your eyes at me, I didn't make this mess, I am just trying
to clean it up)
The three main dysfunctions in this Patient Intraday Table View are,
1. I need to nix the duplicate or overlapping minutes. This table should
show unduplicated time stamps and nursing floors where the patient is
staying in a bed. As the patient gets moved around to different parts of the
hospital, you should see no gaps in the time and date stamps, when the
patient is entering or leaving from one nursing floor locale to another.
2. This whole file needs to be compressed to its most efficient list. Many
times I see the same nursing floor repeated back to back to back, where one
row of data will do, ultimately displaying a single entry time and date, and
a single exit time and date
3. Given this list of inefficiency and duplication, sometimes there is more
than one viable path that can be taken to arrive and the correct and
unduplicated minutes of total time stayed within the hospital walls.
A fine example is below. This made up patient ultimately stayed in the
hospital for 25.12 days, from 12/16/2011 @ 3:21PM to 1/7/2012 @ 1:28PM, or
36,177 total minutes. As you can see from this snippet below there was
39,420 duplicate minutes mixed in, that need to be weeded out.
I need to turn this gobbleygook entirety,
Nursing Wing; Start Date; Start Time; End Date; End Time; Minutes between
Times;
Emergency Room; 12/16/2011; 3:21 PM; 12/16/2011; 3:33 PM; 12;
Holding Area 12/16/2011 3:21 PM 12/16/2011 6:00 PM 159
Holding Area 12/16/2011 6:00 PM 12/16/2011 6:51 PM 51
5th Floor South 12/16/2011 6:51 PM 12/16/2011 11:43 PM
292 Cancer Floor North 12/16/2011 11:43 PM 12/18/2011 1:14 PM 2,251
Cancer Floor North 12/18/2011 1:14 PM 12/23/2011 5:52 AM 6,758
Intensive Care East 12/23/2011 5:52 AM 12/23/2011 6:08 AM 16
Intensive Care East 12/23/2011 6:08 AM 12/23/2011 6:39 AM 31
Intensive Care East 12/23/2011 6:39 AM 12/23/2011 6:40 AM 1
Intensive Care East 12/23/2011 6:39 AM 12/24/2011 12:00 PM 1,761
Intensive Care East 12/23/2011 6:39 AM 12/25/2011 1:56 AM 2,597
Intensive Care East 12/23/2011 6:40 AM 12/24/2011 1:56 AM 1,156
Intensive Care East 12/24/2011 1:56 AM 12/25/2011 1:56 AM 1,440
Intensive Care East 12/24/2011 12:00 PM 12/25/2011 1:56 AM 836
Cancer Floor North 12/25/2011 1:56 AM 12/29/2011 12:00 PM 12,728
Cancer Floor North 12/25/2011 1:56 AM 01/05/2012 11:03 PM 17,107
Cancer Floor North 12/29/2011 12:00 PM 01/05/2012 11:03 PM 21,486
General Med 3rd Floor 01/05/2012 11:03 PM 01/07/2012 1:28 PM 6,915
Into this efficient path,
Nursing Wing; Start Date; Start Time; End Date; End Time; Minutes between
Times;
Holding Area; 12/16/2011; 3:21 PM; 12/16/2011; 6:51 PM; 210;
5th Floor South 12/16/2011 6:51 PM 12/16/2011 11:43 PM 292
Cancer Floor North 12/16/2011 11:43 PM 12/23/2011 5:52 AM 9009
Intensive Care East 12/23/2011 5:52 AM 12/25/2011 1:56 AM 2644
Cancer Floor North 12/25/2011 1:56 AM 01/05/2012 11:03 PM 17,107
General Med 3rd Floor 01/05/2012 11:03 PM 01/07/2012 1:28 PM 6,915
The highlighted yellow portion from the first piece contains the
non-duplicate trip through the hospital as it is displayed in the second
piece concisely.
Thank you for any help that can be provided, as this is making my head spin.
Imagine a plethora of 200,000 patients in a years time, all ridiculously
logged just like this.
I would gladly take either Excel or Access help on this. Any suggestion
would do at this point.
Sincerely,
Eric Lutz
[Non-text portions of this message have been removed]
------------------------------------
----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go
here: http://www.mousetrax.com/techtrax to enter the ezine, then search the
ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com/
----------------------------------Yahoo! Groups Links
-----
No virus found in this message.
Checked by AVG - www.avg.com
Version: 10.0.1430 / Virus Database: 2639/5600 - Release Date: 02/12/13
[Non-text portions of this message have been removed]
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (7) |
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Tidak ada komentar:
Posting Komentar