DECLARE @NonCoveredID UNIQUEIDENTIFIER = NULL;
DECLARE @End DATE = CONVERT(DATE, GETDATE());
SELECT
    f.FacilityName
    , p.PatientName
    , nc.RequestDate
    , nc.Discipline
    , nc.Frequency
    , nc.Weeks
    , nc.Status
    , nc.DescisionDate
    , nc.ApprovedBy
    , nc.ProjectedEquipmentNeeds
    , nc.SponsorInformedOfTreatment
    , nc.ABNToSponsorDate
    , mfsi.Rate
    --including this because I'm sort of interested in whether I'm using the correct rate; pretty sure this is a "bad" rate column.
    --, NonCoveredItemRate = nci.Rate
    , nci.Units
    , nci.CPT
    -- # of units * med fee sched rate * [if eval, 1, else frequency * weeks]
    -- * contract bps percentage for "Non Cov"
    -- we presume these patients to be, because they have non covered items.
    , FacilityCost =
        CONVERT(DECIMAL(18,2)
            , nci.Units * mfsi.Rate * IIF(tcc.Eval = 1, 1, nc.Frequency * nc.Weeks) * bps.Percentage
        )
FROM Facility.Facility AS f
JOIN dbo.DARPatient AS p ON p.FacilityID = f.FacilityID

JOIN dbo.NonCovered AS nc ON nc.PatientID = p.DARPtID
JOIN dbo.NonCoveredItem AS nci ON nci.NonCoveredID = nc.NonCoveredID
--used to determined whether the cpt (non covered item) is an eval
LEFT JOIN Billing.TemplateCPTCode AS tcc ON tcc.Code = nci.CPT
--gets the billing payer source percentage for this contract as though the payer source is 'non cov' (non covered).
OUTER APPLY (SELECT TOP 1 bps.Percentage
    FROM Billing.Contract AS bc
    JOIN Billing.BillingPayerSource AS bps ON bps.ContractID = bc.ID
        AND bps.Name = 'Non Cov'
        AND bps.EffectiveDate <= @End
    WHERE bc.Name = f.CurrentContract
    ORDER BY bps.EffectiveDate desc) AS bps
--gets the rate for this facility's med fee sched for this particular cpt (non covered item)
OUTER APPLY (SELECT TOP 1 mfsi.Rate FROM MedFeeSched AS mfs
    JOIN dbo.MedFeeSchedItems AS mfsi ON mfsi.MedFeeScheduleID = mfs.MedFeeScheduleID
    WHERE mfs.Name = f.MedFeeSchedName
        AND mfsi.CPT = nci.CPT
        AND mfs.EffectiveDate <= nc.RequestDate
    ORDER BY mfs.EffectiveDate DESC) AS mfsi
WHERE (nc.NonCoveredID = @NonCoveredID OR @NonCoveredID IS NULL)