Report Builder 2.0 Example with OBS Pt Charge Levels

ED OBS Patient with B/AR Charge Levels

    A recent request came in to provide a listing of patients, seen and admitted from our Emergency Room, who had been placed in an Observation Bed.  They wanted to group it by the B/AR Chrage Levels to assure that we were utilizing the Observation Status admissions properly and to assure proper reimbursement.  Here is there stored procedure I came up with.  I'll add the .rdl from the report server a little later file later


USE [livedb]  

GO

/****** Object:  StoredProcedure [dbo].[stp_ED_Observation_Pts_Charge_Levels]    Script Date: 12/27/2010 14:52:50 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:        Paul MacDonald

-- Create date: 12/27/2010

-- Description:   Supplies a list of patients who were admitted

-- through the ED with thier B/AR Charge Levels. This report

-- provides a date range lookup and list the results by date,

-- by charge level.

-- =============================================

CREATE PROCEDURE [dbo].[stp_ED_Observation_Pts_Charge_Levels]

      -- Add the parameters for the stored procedure here

    @StartDate DATETIME ,

    @EndDate DATETIME

AS

    BEGIN

      -- SET NOCOUNT ON added to prevent extra result sets from

      -- interfering with SELECT statements.

        SET NOCOUNT ON ;

    -- Insert statements for procedure here

        SELECT  AV.Name ,

                AV.AccountNumber ,

                BCT.ServiceDateTime ,

                COUNT(TransactionProcedureID) ,

                ED_Level = CASE TransactionProcedureID

                             WHEN '378025000' THEN 'Level I'

                             WHEN '378025100' THEN 'Level I'

                             WHEN '378025201' THEN 'Level II'

                             WHEN '378025301' THEN 'Level III'

                             WHEN '378025401' THEN 'Level IV'

                             WHEN '378025501' THEN 'Level V'

                             WHEN '378025601' THEN 'Level V'

                             WHEN '378025701' THEN 'Level V'

                             WHEN '378025801' THEN 'Level V'

                             ELSE TransactionProcedureID

                           END

        FROM    BarChargeTransactions BCT

                INNER JOIN ( SELECT VisitID

                             FROM   AdmVisitEvents

                             WHERE  EffectiveDateTime >= @StartDate

                                    AND Description IN ( 'ENTER OBSERV',

                                                         'CHANGE IN TO OBSERV ' )

                           ) AVE ON BCT.VisitID = AVE.VisitID

                INNER JOIN dbo.AdmVisits AV ON BCT.VisitID = AV.VisitID

        WHERE   BCT.ServiceDateTime >= @StartDate

                AND BCT.ServiceDateTime <= @EndDate

                AND Type = 'C'

                AND BCT.SourceID = 'CMC'

                AND ProcedureChargeCategory = '452'

        GROUP BY AV.Name ,

                AV.AccountNumber ,

                BCT.ServiceDateTime ,

                TransactionProcedureID

        HAVING  ( COUNT(TransactionProcedureID) ) < 2

        ORDER BY BCT.ServiceDateTime DESC

    END

GO

Current Census Question

An entry on my Linked-In "Meditech Data Repository Group" page (Meditech Data Repository uses in Clinical Settings using SSRS) concerning census led me to post a query there that didn't really answer the question.  The user wanted to get a real-time census of her hospital.  She was starting her search in the AdmNursingCensus table.  I took it and ran!  Big mistake –I didn't read what she really wanted to get out of the DR – the current census.  The AdmNursingCensus table is used to capture data at day's end.  That is, it will tell you who was on what unit at midnight.  It doesn't even post any data until the day is over. 

So if you're trying to find out who's on the floor as of 2:37 this afternoon, you're outta luck if you're looking in the AdmNursingCensus table.  It can tell you who was on the floor at midnight  last night (or any other night for that matter), but not who's there right now.  It is great for trending populations shifts over weeks, months and years even, but not for who's here now.  

So, to see who's where, right now, I turn to the AdmVisists table.  It is updated as a patient's status changes.  If they've been moved to another floor, it updates.  If they've been discharged, it updates.  If their accomodations changes, it updates etc.  Here is the query I use to capture current census:

 

/****** Script for Current Census ******/

SELECT [AccountNumber] ,

[Name] ,

[BedID] ,

[RoomID] ,

[RoomLocation] ,

[FacilityID] ,

[FacilityName] ,

[FinancialClassID] ,

[InpatientServiceID] ,

[LocationID] ,

[LocationName] ,

[ObservationPatient] ,

[ObservationDateTime] ,

[RoomID] ,

[RoomLocation] ,

[RoomLocationGlDept] ,

[RoomLocationName] ,

[RoomRateAccommodation] ,

[Sex] ,

[Status] ,

[UnitNumber] ,

[VisitorsAllowed]

FROM [livedb].[dbo].[AdmVisits]

WHERE
Status
=
'ADM IN'

Question about current census...

A question arose on how to find the current census of your facility.  The simplest way I found (and it is confirmed my running the Meditech Standard Census report), is below:

DR and Virtualization

Hey, anybody out the virtualized their DR yet? I'm about to – on Wednesday next. The 13^th of October, that is. I was wondering if there's been any 'glitches' I might need to look out for. Some of the answers I got when I first posed the question a few months ago had me a little worried. It's a straightforward move, turn off the data transfers, start the mirroring/migration process, verify databases are intact, turn on the data transfers. Simple, eh? Maybe a little bit of re-IP'ing and re-naming of the servers, but not too bad.

At least that's what the plan is…nothing ever goes wrong when we deal with upgrades, moves or implementations, now do they? I'll update as soon as I get a chance after it's done. But for right now, anybody got any words of encouragement?