[MT-L] SQL: charges by CPT

[Sexton Kevin - Fauquier]

SQL People,

Does anyone have a SQL report that will list charges BY CPT (not Bar Procedure and not just a dump of BarChargeTransactions). Here is what I have, but as soon as I add the CPT rows, it duplicates the charge info.  –Thanks, Kevin

 

SELECT

BV.Name,

BV.AccountNumber,

BV.UnitNumber AS MedicalRecordNumber,

BCT.TransactionProcedureID as BarProcedureID,

DBarProcedures.[Description],

BCT.Amount,

PatientStatus =

       CASE BVFD.PatientStatus

              WHEN 'ER' THEN 'OUT'

              WHEN 'OBSERV' THEN 'OUT'

              WHEN 'OUT' THEN 'OUT'

              WHEN 'REC' THEN 'REC'

              WHEN 'SDC' THEN 'SDC'

              ELSE 'undefined' END,

c.Code as Cpt,

c.CptSeqID as CptSeqID,

ISNULL(m1.ModifierCptID,'') as CptMod1,

ISNULL(m2.ModifierCptID,'') as CptMod2,

BVFD.BarStatus AS AccountStatus,

DBarProcedures.ChargeDeptID,

DBarProcedures.ChargeCategoryID

FROM

livedb.dbo.BarVisits BV

INNER JOIN livedb.dbo.BarVisitFinancialData BVFD       ON BV.SourceID = BVFD.SourceID    AND BV.BillingID = BVFD.BillingID

INNER JOIN livedb.dbo.BarChargeTransactions BCT        ON BV.SourceID = BCT.SourceID     AND BV.BillingID = BCT.BillingID

JOIN DBarProcedures ON DBarProcedures.ProcedureID=BCT.TransactionProcedureID AND DBarProcedures.SourceID=BCT.SourceID

LEFT JOIN BarCptCodes c ON c.BillingID=BV.BillingID  and c.SourceID=BV.SourceID

LEFT JOIN BarCptModifiers m1 ON m1.BillingID=c.BillingID and c.CptSeqID=m1.CptSeqID and m1.ModifierSeqID=1 and m1.SourceID= c.SourceID

LEFT JOIN BarCptModifiers m2 ON m1.BillingID=c.BillingID and c.CptSeqID=m2.CptSeqID and m1.ModifierSeqID=2 and m2.SourceID= c.SourceID

ORDER BY CptSeqID

 

Thanks,

 

Kevin Sexton

Business Applications Analyst

Fauquier Health  500 Hospital Drive, Warrenton, VA  20186

p. 540-316-5522  f.540-316-5501 sex...@fauquierhealth.org

 

The information transmitted via this e-mail is intended only for the person or entity to which it is addressed and may contain confidential and/or proprietary information. Any use, review, retransmission, dissemination or other use of, or pursuing of any action in reliance upon this information by persons or entities other than the intended recipient is strictly prohibited. If you are the recipient of this e-mail transmission in error, please reply to the sender and delete the material from any computer. Thank you.

[Paolino, Jamie]

Hi Kevin,

 

The issue here is that there is no real link between the BarChargeTransactions table and the BarCPTCodes.

 

Are you trying to list out all of the charges for an account and then list all of the CPTs and up to two modifiers for each CPT?

 

In the above example you would see the same CPT’s for each charge transaction.

 

Right off the top I did notice an issue with your last join.  You are joining on m1 rather than m2.  The line should read like this:

 

LEFT OUTER JOIN BarCptModifiers m2 ON m2.SourceID= c.SourceID and m2.BillingID=c.BillingID and c.CptSeqID=m2.CptSeqID and m2.ModifierSeqID=2

 

 

Jamie Paolino

Business Intelligence Developer

CharterCARE Health Partners

200 High Service Ave

North Providence, RI 02904

401-456-3096

[Ganesh Seshadri]

The way we’ve set up our BAR charge procedure dictionary, one procedure code could have multiple alt code types , each of which could have multiple effective dates and alt codes specific to that type and date.  So a join without further qualifiers will result in duplicates.  I don’t know what criteria you would use to restrict the alt code type – I believe this is based on the claim dictionary which has the alt code priority sequence based on the account type and insurance, but it’s way over my head to get the one.  I believe the date can be set based on the effective date being the last one prior to the charge date.

 

Anyway, in the reports that I need CPT, I follow one of these strategies – 1)  go to ABS and get the CDM CPT from there (eg. AltCodeID in table AbsPatCptChgs),  or 2) we have a CDS screen tied to our fin class dictionary where I have assigned the alt code type for that fin class, and use that as the alt code type.

 

Ganesh Seshadri

Analyst

Indiana Regional Medical Center

(724) 357-7012

 

From: Meditech-l [mailto:meditech-...@mtusers.com] On Behalf Of Sexton Kevin - Fauquier
Sent: Thursday, October 20, 2016 9:11 AM
To: MEDIT...@MTUsers.com
Subject: [MT-L] SQL: charges by CPT

 

SQL People,

[Ian Proffer]

Hi Keven and Jamie,

 

I have a couple of comments:

 

1.    You can link a charge procedure directly to a CPT (a hard CPT anyway) via the DBarProcAltCodeEffectDates table.  Here’s an example from a MAGIC site (would be the same for C/S and 6.0) of what the JOIN looks like:

 

INNER JOIN Testmdb.dbo.DBarProcAltCodeEffectDates DBP

      ON BCT.SourceID = DBP.SourceID

      AND BCT.TransactionProcedureID = DBP.ProcedureID

      AND DBP.TypeID = 'CPT4'

      AND DBP.EffectiveDateTime = (SELECT MAX(EffectiveDateTime)

                                    FROM DBarProcAltCodeEffectDates DBPm

                                    WHERE DBP.SourceID = DBPm.SourceID

                                          AND DBP.ProcedureID = DBPm.ProcedureID

                                    AND DBP.TypeID = DBPm.TypeID)

 

The kicker is to limit the code type to CPT-4 and get the most recent effective date time via the JOIN.

 

2.    The BarCptCodes table also has the CptSeqID column as part of the primary key, so if you’re not accounting for this in your join or output, you’ll potentially get multiple rows per transaction, per cpt.  You could either limit the CPT to the first one, or either PIVOT on the CptSeqID to display each unique combination of CPT and SeqID in its own column in the output.

 

Cordially,

-- Ian

 

Ian Proffer

Vice President, Consulting Services

781.329.4300 x202

Data Repository Experts Since 1998

 

[William Conklin]

I created a function for this purpose (see below).  It can be called in a select statement:

SELECT

       Fn_BAR_ProcedureAltCode(bct.SourceID,bct.TransactionProcedureID,bct.ServiceDateTime,'CPT')

FROM  

       BarChargeTransactions bct

 

 

Function:

CREATE FUNCTION [dbo].[Fn_BAR_ProcedureAltCode]

(

       @SourceID varchar(256),

       @ProcedureID varchar(256),

       @ServiceDateTime datetime,

       @TypeID varchar(256)

 

)

RETURNS varchar(256)

AS

BEGIN

       RETURN

              (SELECT

                     TOP 1 Code

              FROM

                     livedb.dbo.DBarProcAltCodeEffectDates AS ed

              WHERE

                     ed.SourceID = @SourceID

                     AND ed.ProcedureID = @ProcedureID

                     AND ed.TypeID = @TypeID

                     AND ed.EffectiveDateTime <= @ServiceDateTime

              ORDER BY

                     ed.EffectiveDateTime DESC)

 

END

 

Hope that helps,

 

Will Conklin

Data Management Coordinator

Charlotte Hungerford Hospital – IT

860.496.6724 '

wcon...@hungerford.org *

CONFIDENTIALITY NOTICE Information contained in this transmission, together with any other documents or attachments, is privileged and confidential, and is intended only for the use of the individual or entity to which it is addressed. This transmission may contain information or materials protected by the applicable laws of the State of Connecticut, and /or protected health information as defined by the Health Insurance Portability and Accountability Act of 1996 (HIPAA). This information is intended exclusively for the use of the individual or entity named as addressee(s). The authorized recipient of this information is STRICTLY PROHIBITED from disclosing this information after its stated need has been fulfilled. Misuse or distribution of the information contained in this transmission is punishable by civil and/or criminal penalties under state or federal law. If you are not the intended recipient, you are hereby notified that any disclosure, dissemination, saving, printing, copying, or action taken in reliance on the contents of these documents of this message, or any attachment, is strictly prohibited. Please notify the original sender (only) immediately by telephone or by reply e-mail and delete this message, along with any attachments from your computer immediately.