date range calculations 1

[bookor]

I have a hospital application where I want to report on demographics of residents (male, female, black, white, etc), the average daily population of the facility, and the average length of stay. I have a person table with the demographics and a movement table which gives me the dates and times of entry and release. Can I construct a sql statement that would give me the stats for a particular date or date range? I can do something like this with an employee application because you have few employees that work only a part of a day where in the hospital you will have numerous patients who are only in the facility for a few hours. <br>
<br>
In the past we used to take a "snapshot" of the facility population at the same time every day and store this in a stats table then report off of that. Of course this is not very accurate and your results depend on the time of day the snapshot is taken. <br>
<br>
Any suggestions on the appropriate way to handle this problem would be appreciated. Can I do it in sql or do I need to maintain the snapshot process? <br>
<br>
Im working with an Oracle8 database.<br>
<br>
any information would be appreciated<br>
<br>
<br>
<br>

 

[CRoberts]

There is indeed a way to query a range of values, including dates. One can use UNIONS on multiple SELECTS or incorporate your ranges into one SELECT using nested DECODES. However, this is rather obtuse. These nested DECODE statements uses the GREATEST and LEAST functions to calculate whether a value falls within a particular range. Ironically, I did a similar report in SQLPLus for a hospital on admissions, length of stay and ethnicity using this approach. It went somewhat like this:<br>
<br>
DEFINE ELPASED = 'ROUND(MONTHS_BETWEEN(DATE_DISCHARGE, DATE_ADMIN), 3)' /* DEFINE used to simplify query */<br>
<br>
SELECT <br>
DECODE('IN',<br>
DECODE (GREATEST(&ELAPSED, 0), LEAST(&ELASPED, .467), 'IN', 'OUT'), 'Less Than Two Weeks',<br>
DECODE (GREATEST(&ELAPSED, .468), LEAST(ELAPSED, .999),<br>
'IN', 'OUT'), 'Less than One Month',<br>
'Over One Month) RANGE<br>
FROM Hos_Stay H, Patient_Info P<br>
WHERE H.Pat_Id = P.Pat_Id<br>
GROUP BY DECODE('IN',<br>
DECODE (GREATEST(&ELAPSED, 0), LEAST(&ELASPED, .467), 'IN', 'OUT'), 'Less Than Two Weeks',<br>
DECODE (GREATEST(&ELAPSED, .468), LEAST(ELAPSED, .999),<br>
'IN', 'OUT'), 'Less than One Month',<br>
'Over One Month)<br>
/<br>
<br>
Now that you can get a taste of how to query ranges, at least in SQLPlus (or even Oracle Reports), I would argue against it. I think that having a number of summary tables that are populated with Triggers on your movement and person tables is a better approach, keep a running total of entries by ethnicity, or the like, with an average length of stay. This is much easier to work with. Now there are statistics available for anyone to query. Otherwise, eight months from now when you are asked to modify your report, and you used a complex report, you may have a hell of a time trying to remember how and why you did what you did.

 

[bookor]

You got that right. I have trouble remembering why I did something yesterday....<br>
<br>
Having a summary table always seemed like the most practical way to handle this problem, but I couldnt get over the feeling I was taking the easy way out just to avoid having to solve the problem in sql. Thanks for the code, Im going to play with that a while. Ive played around with decode some but still dont like it. <br>
<br>
thanks again for the reply

 

[connectxyzzy]

defining a stored procedure that could be invoked might simplify your life......... I don't have access to any Oracle db's or I'd give a try at coding it for you.

 

[MikeLacey]

Taking the easy way out - being creatively lazy and making it possible for the next person to understand your code - is an excellent idea.<br>
<br>
Don't be clever. Clever sucks. &lt;smile&gt;<br>
<br>
Mike<br>
---<br>
Mike_Lacey@Cargill.Com<br>