VW_PCC_Stays_PPPL_Payor_Costs

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

patient_id

int

null

stay_begin_date

datetime

16,3

null

stay_end_date

datetime

16,3

√

null

patient_mrn

varchar

√

null

patient_last_name

varchar

√

null

patient_first_name

varchar

√

null

facility_id

int

null

ROX_Cache_Fac.Facility_ID

Implied ConstraintR

facility_code

varchar

null

facility_name

varchar

375

null

facility_type

varchar

254

null

facility_state

varchar

√

null

total_pharm_cost

money

21,4

√

null

std_ins_name

varchar

1000

√

null

pcc_insurance

varchar

√

null

pcc_payer_code

varchar

√

null

pcc_payer_name

varchar

√

null

wade_payer_type

varchar

√

null

Relationships

Close relationships within degrees of separation

View Definition

CREATE VIEW dbo.VW_PCC_Stays_PPPL_Payor_Costs AS

/*********************************************************************
Database:	    DataCStore
View:			VW_PCC_Stays_PPPL_Payor_Costs
Repo:           pcc_payor
Purpose:        Map patient "payor-stays" to pharmacy costs.
                Two data sources: Propay/Payless for pharmacy cost data.
                                  PCC for stay dates and payors.
                Intended for use by Director of Payor Relations
                (Wade Bennett).
*********************************************************************/

    WITH pharm AS
             (SELECT cenreimb.ClientID,
                     cenreimb.FacilityID,
                     cenreimb.CensusDate,
                     invdet.PatientName,
                     cenreimb.PatientLastName,
                     cenreimb.PatientFirstName,
                     cenreimb.ResidentNumber                                                AS patient_mrn,
                     invdet.DispenseDate,
                     invdet.Amount,
                     cenreimb.std_ins_name,
                     cenreimb.InsuranceCompanyDescription,
                     cenreimb.PayerCode,
                     cenreimb.PayerName,
                     cenreimb.wade_PayerType
              FROM dbo.PPPL_InvoiceDetail AS invdet
              JOIN dbo.PCC_Census_Reimb AS cenreimb
                   ON invdet.MedRecNo = cenreimb.ResidentNumber
                       AND invdet.DispenseDate = cenreimb.CensusDate
              /* Belt and suspenders precautionary filter, i.e.
                 MedRecNo value should be non-patient-specific, with general charges (IV's, consulting, etc.)
                 being excluded in above JOIN. */
              WHERE invdet.TransType <> '0'
              UNION ALL
              /* EDGE CASE: Dispense date is discharge date.
                 A separate query is needed because there's no census on date of discharge. */
              SELECT cenreimb.ClientID,
                     cenreimb.FacilityID,
                     cenreimb.CensusDate,
                     invdet.PatientName,
                     cenreimb.PatientLastName,
                     cenreimb.PatientFirstName,
                     cenreimb.ResidentNumber AS patient_mrn,
                     invdet.DispenseDate,
                     invdet.Amount,
                     cenreimb.std_ins_name,
                     cenreimb.InsuranceCompanyDescription,
                     cenreimb.PayerCode,
                     cenreimb.PayerName,
                     cenreimb.wade_PayerType
              FROM dbo.PPPL_InvoiceDetail AS invdet
              JOIN dbo.PCC_Stays AS stays
                   ON invdet.DispenseDate = CONVERT(DATE, stays.StayEndDate)
                       AND invdet.MedRecNo = stays.ResidentNumber
              JOIN dbo.PCC_Census_Reimb AS cenreimb
                   ON stays.ResidentNumber = cenreimb.ResidentNumber
                       AND CONVERT(DATE, stays.StayEndDate) = DATEADD(DD, 1, cenreimb.CensusDate)
              /* Belt and suspenders precautionary filter, i.e.
                 MedRecNo value should be non-patient-specific, with general charges (IV's, consulting, etc.)
                 being excluded in above JOIN. */
              WHERE invdet.TransType <> '0')

    SELECT stays.PatientId AS patient_id,
           stays.StayBeginDate AS stay_begin_date,
           stays.StayEndDate AS stay_end_date,
           pharm.patient_mrn,
           pharm.PatientLastName AS patient_last_name,
           pharm.PatientFirstName AS patient_first_name,
           faccache.FacilityID AS facility_id,
           faccache.FacilityCode AS facility_code,
           faccache.FacilityName AS facility_name,
           faccache.FacilityType AS facility_type,
           faccache.FacilityState AS facility_state,
           SUM(Amount) AS total_pharm_cost,
           pharm.std_ins_name,
           pharm.InsuranceCompanyDescription AS pcc_insurance,
           pharm.PayerCode AS pcc_payer_code,
           pharm.PayerName AS pcc_payer_name,
           pharm.wade_PayerType AS wade_payer_type
    FROM pharm
    JOIN dbo.PCC_Stays AS stays
         ON pharm.ClientID = stays.PatientId
             AND pharm.CensusDate >= CONVERT(DATE, stays.StayBeginDate)
             AND pharm.CensusDate <= CONVERT(DATE, stays.StayEndDate)
    JOIN dbo.PCC_Cache_FacID AS faccache
         ON pharm.FacilityID = faccache.FacilityID
    WHERE stays.StayBeginDate >= '2019-09-01'
    GROUP BY stays.PatientId,
             stays.StayBeginDate,
             stays.StayEndDate,
             pharm.patient_mrn,
             pharm.PatientLastName,
             pharm.PatientFirstName,
             faccache.FacilityID,
             faccache.FacilityCode,
             faccache.FacilityName,
             faccache.FacilityType,
             faccache.FacilityState,
             pharm.std_ins_name,
             pharm.InsuranceCompanyDescription,
             pharm.PayerCode,
             pharm.PayerName,
             pharm.wade_PayerType

Columns

Relationships

Close relationships within degrees of separation

View Definition

Possibly Referenced Tables/Views