1 2 Previous Next 19 Replies Latest reply on Mar 28, 2017 1:12 PM by mark.welsh Go to original post
      • 15. Re: Utilization, Queues, Census, Throughput for Blocks, Floors, Units, Rooms, Offices, etc. - What do you do?
        Jason Scarlett

        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.

        Mark Jackson

        • 16. Re: Utilization, Queues, Census, Throughput for Blocks, Floors, Units, Rooms, Offices, etc. - What do you do?
          Jason Scarlett

          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)

          • 17. Re: Utilization, Queues, Census, Throughput for Blocks, Floors, Units, Rooms, Offices, etc. - What do you do?
            Mark Jackson

            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 (

                    SELECT

                     [Date] = CONVERT(DATETIME, DATEADD(MONTH, -6, dateadd(hour, datediff(hour, 0, GETDATE()), 0)))

                    UNION ALL SELECT

                     [Date] = DATEADD(hour, 1, [Date])

                    FROM

                     Dates

                    WHERE

                     Date <= CONVERT(DATE,GETDATE())

            )SELECT

            [Date]

            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

            FROM (

              SELECT level le

              FROM dual

              CONNECT BY LEVEL < HOURS_BETWEEN(CURRENT_TIMESTAMP,ADD_MONTHS(CURRENT_TIMESTAMP,-6))

            );

            1 of 1 people found this helpful
            • 18. Re: Utilization, Queues, Census, Throughput for Blocks, Floors, Units, Rooms, Offices, etc. - What do you do?
              mark.welsh

              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)

              )

              DECLARE

                @basedate DATETIME,

                @offset   INT

              SELECT

                @basedate = '01 Jan 2016',

                @offset = 1

                INSERT INTO @CalendarMonths SELECT @basedate

               

               

              --WHILE ( DATEADD(DAY, @offset, @basedate) < CURRENT_TIMESTAMP)

              WHILE (@offset < 500)

              BEGIN

                INSERT INTO @CalendarMonths SELECT DATEADD(DAY, @offset, date) FROM @CalendarMonths

                --where DATEADD(DAY, @offset, date) < CURRENT_TIMESTAMP

                SELECT @offset = @offset + @offset

              END

              • 19. Re: Utilization, Queues, Census, Throughput for Blocks, Floors, Units, Rooms, Offices, etc. - What do you do?
                mark.welsh

                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?

                 

                ,SUM(CASE

                            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])

                                AND

                                    (CONVERT(int, DATEADD(dd, 0, DATEDIFF(dd, 0, AdmitDateTime))) < CONVERT(int, d.[date])

                                        OR DATEPART(hh, AdmitDateTime)<=23)

                                AND

                                    (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. 

                1 2 Previous Next