The way I use my method today, does not provide patient details when drilling in since I aggregate to the hour and site and unit (and ...). For us, my census method works without any issues for 100+ hospitals, thousands of Units, and thousands of attending physicians to calculate the census against. With my method, I could only envision being able to add a detail level lookup if I grouped the census by an encouter ID ... this would of course make the tables larger by a factor of the average number of hours in the length of stay (20-200)... and hence would only be practical on a "smaller" data set. If I used my method against a smaller data set AND at the encounter ID level, I would then try using a data blend within Tableau on the Encounter ID to display a second viz with the patient list.
I'd be curious to know from Mark, how large his data sets are and if he has had any performance issues as the data sets grew.
Curiosity got the better of me ... I gave it a test and it worked ... but my existing code would have to be tuned for this use case to reduce census counts of zero that add no value in the output table.
Left side is output of the census calculations, right side is details of each record from the original data set.
Pre action filter (set to exclude all until a selection is made)
Post action filter (only 25 encounter details displayed)
1 of 1 people found this helpful
This is an old one. My currently hourly census in the ED source is summarized and doesn't include the accounts. If I wanted to include the accounts I would restrict the extract to a rolling date range so it didn't become too large. I would also take a different approach today than I originally did. I'd recommend starting with a base table that has all your date/time instances, then doing a non-equi join (<,>) to figure out if the base table timestamp is between the arrival and departure date. If you cannot create your own tables, then you could do something like this in SQL Server to generate the dates on the fly:
WITH Dates AS (
[Date] = CONVERT(DATETIME, DATEADD(MONTH, -6, dateadd(hour, datediff(hour, 0, GETDATE()), 0)))
UNION ALL SELECT
[Date] = DATEADD(hour, 1, [Date])
Date <= CONVERT(DATE,GETDATE())
FROM Dates OPTION (MAXRECURSION 10000)
I like the solution to this date generation problem better in Exasol though:
SELECT ADD_HOURS(DATE_TRUNC('hour', ADD_MONTHS(CURRENT_TIMESTAMP,-6)), le) AS GENERATED_DATE
SELECT level le
CONNECT BY LEVEL < HOURS_BETWEEN(CURRENT_TIMESTAMP,ADD_MONTHS(CURRENT_TIMESTAMP,-6))
Thanks guys - this is extremely helpful. I think the missing link was the non-equi join that you mentioned Mark. Such an obvious answer, but I had not even considered it. This cuts my records down from 800,000 (cross-join against all dates) to 6,000 for a six month time frame. Perfectly usable. Now I'll just have to go through and validate the data.
Mark - can I ask why you decided to convert the dates to INT in your orginal code? Easier/more accurate calculations?
Here's the code I'm using to generate each day for my dates table. The only thing that needs to be changed is the WHILE statement to something that makes more sense. Other than that it works great.
declare @CalendarMonths table (date DATETIME, PRIMARY KEY (date)
@basedate = '01 Jan 2016',
@offset = 1
INSERT INTO @CalendarMonths SELECT @basedate
--WHILE ( DATEADD(DAY, @offset, @basedate) < CURRENT_TIMESTAMP)
WHILE (@offset < 500)
INSERT INTO @CalendarMonths SELECT DATEADD(DAY, @offset, date) FROM @CalendarMonths
--where DATEADD(DAY, @offset, date) < CURRENT_TIMESTAMP
SELECT @offset = @offset + @offset
Mark, if you get a moment, I'm trying to figure out how to account for NULL Discharge Dates. Currently, they're excluded from being counted because of the logic in the CASE statement. Do you know how I could modify it to still perform the count even if the patient hasn't yet been discharged?
WHEN CONVERT(int, DATEADD(dd, 0, DATEDIFF(dd, 0, AdmitDateTime))) <= CONVERT(int, d.[date])
AND CONVERT(int, DATEADD(dd, 0, DATEDIFF(dd, 0, DischargeDateTime))) >= CONVERT(int, d.[date])
(CONVERT(int, DATEADD(dd, 0, DATEDIFF(dd, 0, AdmitDateTime))) < CONVERT(int, d.[date])
OR DATEPART(hh, AdmitDateTime)<=23)
(DATEPART(hh, DischargeDateTime)>= 23
OR CONVERT(int, DATEADD(dd, 0, DATEDIFF(dd, 0, DischargeDateTime)))> CONVERT(int, d.[date]))
THEN 1 END
) AS Count_2300
Edit: We decided that if the patient is still in the unit to use GETDATE() as their DischargeDateTime, so that the data is relevant to the time when the report is ran.