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'