Monday, December 27, 2010

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]

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


-- =============================================
-- 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
      -- 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
        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 ,
        HAVING  ( COUNT(TransactionProcedureID) ) < 2
        ORDER BY BCT.ServiceDateTime DESC


Monday, October 25, 2010

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] ,
FROM [livedb].[dbo].[AdmVisits]

Sunday, October 24, 2010

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:

Friday, October 8, 2010

DR and Virtualization

Hey, anybody out the virtualized their DR yet? I'm about to – on Wednesday next. The 13th 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?

Friday, October 1, 2010

Welome to PMacD and the DR

Just thought I would begin to write down some of my thoughts, ideas and plans for my life, my career and my family.  There's little time for me to give back everything that I've learned, everything I've soaked up and everything I've discovered on my own.  

While I've been on this beautiful planet for more than two score and ten, I've only recently (the last ten or so) begun to learn about life and how to truly enjoy it.  There's been such a transition and metamorphosis in my life, I don't know where to begin.

But I have to first explain  the blog's name: PMacD is me, naturally, and the "DR" is the data repository.  Which data repository you may ask - well, it's the SQL Server database that is populated with vast amounts of data supplied by a Meditech hospital information system (HIS). Now don't get your pants in a tizzy, there will be no patient information posted (Why, I do believe that's against some sort of HIPAA rule.)  I will merely be trying to collect my thoughts, my work and my accomplishments somewhere - somewhere I can find them.  (Hopefully I'll remember to bookmark this page) 

Oh, and it's not just about me...I want to here from folks out there.  I want input, questions, shrieks of horror at what I may suggest to do with the databases we have been so foolishly entrusted to maintain. 

Enough for now...I'll post again soon with perhaps something a little worthwhile for you to use....