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