TSQL Help with a Stored Procedure.

Question

TSQL Help with a Stored Procedure.

Jeffs1977

SSC-Addicted

Points: 487
More actions
February 28, 2019 at 3:56 pm

#381565

I need help with my below mentioned Stored Procedure - specific to the [E&M] field in my dataset.
Per my clients specifications, they need the [E&M] field to return either a 'Yes' or a 'No' value based on a specific rule. IF the field [PPS/NonPPS] = 'Non-PPS', always return a 'No'. If the [PPS/NonPPS] field equals 'Dual', 'Medi-Medi' or 'PPS' AND any CPCode in the Visit (I created a field called [CPTFirst]) = a 9,D,G or H then Yes, else no.
In my result set, I have values returning incorrect. For Example, I have a value of 'Dual' in my [PPS/NonPPS] field and in my [CPTFirst] a value of a 9; however in my [E&M] I am getting back a 'No', when I should be seeing a 'Yes'.

/********************************************
EXEC [cusVA_SEHMedicaidReconciliationReport]
@DATETYPE        = 'DOS'
, @STARTDATE    = N'01/01/2014'
, @ENDDATE        = N'01/31/2014'
, @COMPANY        = NULL
, @FACILITY        = NULL
, @doctor        = NULL
, @INSFINCLASS    = NULL
, @INSCARRIER = NULL
, @FINPERIOD    = '01/01/2014 - 01/31/2014'
*********************************************/
ALTER PROCEDURE [dbo].[cusVA_SEHMedicaidReconciliationReport]
         (
      @DATETYPE VARCHAR(3)
     , @STARTDATE DATETIME
     , @ENDDATE  DATETIME
     , @COMPANY  VARCHAR(255)
            , @FACILITY VARCHAR(2000)
     , @doctor   VARCHAR(255)
     , @INSFINCLASS VARCHAR(255)
            , @INSCARRIER     VARCHAR(255)
            , @FINPERIOD     VARCHAR(90)

         )
AS
   BEGIN
     SET NOCOUNT ON;
         ---Set local variables
     DECLARE
       @VDATETYPE VARCHAR(3) = @DATETYPE
      , @VSTARTDATE DATETIME = @STARTDATE
      , @VENDDATE  DATETIME = @ENDDATE
      , @VCOMPANY  VARCHAR(255) = @COMPANY
      , @VFACILITY VARCHAR(2000) = @FACILITY
             , @VDOCTOR   VARCHAR(255) = @doctor
      , @VINSFINCLASS VARCHAR(255) = @INSFINCLASS
             , @VINSCARRIER VARCHAR(255) = @INSCARRIER
             , @VFFINPERIOD VARCHAR(90) = @FINPERIOD;
CREATE TABLE #Visit
(
     [PatientVisitId] INT
    , [TicketNumber] VARCHAR(30)
    , [PatientId] VARCHAR(20)
    , [LastName] VARCHAR(60)
    , [FirstName] VARCHAR(35)
    , [BirthDate] DATE
    , [PatientAgeTOS] INT
    , [DOSTicket] DATETIME
    , [DOETicket] DATETIME
    , [FinancialPeriod] VARCHAR(90)
    , [VisitFinancialClass] VARCHAR(60)
    , [PrimaryIns] VARCHAR(60)
    , [PrimaryInsFinancialClass] VARCHAR(60)
    , [SecondaryIns] VARCHAR(60)
    , [SecondaryInsFinancialClass] VARCHAR(60)
    , [TertiaryIns] VARCHAR(60)
    , [TertiaryInsFinancialClass] VARCHAR(60)
    , [MTH] VARCHAR(2)
    , [Year] VARCHAR(4)
    , [Doctor] VARCHAR(60)
    , [Facility] VARCHAR(60)
    , [Department] VARCHAR(10)
    , [Resource] VARCHAR(60)
    , [InsuranceFee] MONEY
    , [PatientFee] MONEY
    , [TotalFee] MONEY
    , [InsurancePayment] MONEY
    , [PatientPayment] MONEY
    , [TotalPayment] MONEY
    , [InsuranceAdjustment] MONEY
    , [PatientAdjustment] MONEY
    , [TotalAdjustment] MONEY
    , [InsuranceBalance] MONEY
    , [PatientBalance] MONEY
    , [TotalBalance] MONEY
    , [PPS/NonPPS] VARCHAR(30)
    , [E&M] VARCHAR(5)
    , [Covered] VARCHAR(1)
    , [CPTCodes] VARCHAR(255)
    , [CPTFirst] VARCHAR(255)
    , [LastCorrNote] VARCHAR(255)
)
INSERT INTO #Visit
SELECT
   [PatientVisitId] = cfa.PatientVisitId
    , [TicketNumber] = cfa.TicketNumber
    , [PatientId] = cp.PatientId
    , [LastName] = cp.LastName
    , [FirstName] = cp.FirstName
    , [BirthDate] = cp.BirthDate
    , [PatientAgeTOS] = (0 + CONVERT(VARCHAR(8), cfa.VisitDate, 112) - CONVERT(VARCHAR(8), cp.BirthDate, 112)) / 10000
    , [DOSTicket] = cfa.VisitDate
    , [DOETicket] = pv.Entered
    , [FinancialPeriod] = @VFFINPERIOD
    , [VisitFinancialClass] = fc.Description
    , [PrimaryIns] = ic.InsuranceCarrier
    , [PrimaryInsFinancialClass] = ic.FinancialClass
    , [SecondaryIns] = ic2.InsuranceCarrier
    , [SecondaryInsFinancialClass] = ic2.FinancialClass
    , [TertiaryIns] = ISNULL(ic3.InsuranceCarrier,'No Insurance Carrier')
    , [TertiaryInsFinancialClass] = ISNULL(ic3.FinancialClass,'No Financial Class')
    , [MTH] = SUBSTRING(CONVERT(NVARCHAR(6), cfa.VisitDate, 112), 5, 2)
    , [Year] = DATEPART(YEAR, cfa.VisitDate)
    , [Doctor] = doc.ListName
    , [Facility] = fac.ListName
    , [Department] = CASE WHEN fac.ListName LIKE '%Dental%' THEN '30020' WHEN fac.ListName LIKE '%Behavioral%' THEN '30012' ELSE '30010' END
    , [Resource] = ISNULL(res.ListName, 'No Resource')
    , [InsuranceFee] = SUM(CASE WHEN cfa.TransactionType = 'Chgs' THEN - cfa.InsuranceAmount ELSE 0 END)
    , [PatientFee] = SUM(CASE WHEN cfa.TransactionType = 'Chgs' THEN - cfa.PatientAmount ELSE 0 END)
    , [TotalFee] = SUM(CASE WHEN cfa.TransactionType = 'Chgs' THEN - cfa.InsuranceAmount + - cfa.PatientAmount ELSE 0 END)
    , [InsurancePayment] = SUM(CASE WHEN cfa.TransactionType = 'Pmts' THEN cfa.InsuranceAmount ELSE 0 END)
    , [PatientPayment] = SUM(CASE WHEN cfa.TransactionType = 'Pmts' THEN cfa.PatientAmount ELSE 0 END)
    , [TotalPayment] = SUM(CASE WHEN cfa.TransactionType = 'Pmts' THEN cfa.InsuranceAmount + cfa.PatientAmount ELSE 0 END)
    , [InsuranceAdjustment] = SUM(CASE WHEN cfa.TransactionType IN ('NAdj','CAdj') THEN cfa.InsuranceAmount ELSE 0 END)
    , [PatientAdjustment] = SUM(CASE WHEN cfa.TransactionType IN ('NAdj','CAdj') THEN cfa.PatientAmount ELSE 0 END)
    , [TotalAdjustment] = SUM(CASE WHEN cfa.TransactionType IN ('NAdj','CAdj') THEN cfa.InsuranceAmount + cfa.PatientAmount ELSE 0 END)
    , [InsuranceBalance] = pva.InsBalance
    , [PatientBalance] = pva.PatBalance
    , [TotalBalance] = (pva.InsBalance + pva.PatBalance)
    , [PPS/NonPPS] = CASE
                  WHEN ic.FinancialClass = 'Commercial' AND ic2.FinancialClass = 'AHCCCS' THEN 'Dual'
                  WHEN ic.FinancialClass = 'Commercial' AND ic3.FinancialClass = 'AHCCCS' THEN 'Dual'
                  WHEN ic.FinancialClass LIKE '%Medicare%' AND ic2.FinancialClass = 'AHCCCS' THEN 'Medi-Medi'
                  WHEN ic.FinancialClass LIKE '%Medicare%' AND ic3.FinancialClass = 'AHCCCS' THEN 'Medi-Medi'
                  WHEN ic.FinancialClass = 'AHCCCS' THEN 'PPS'
                  ELSE 'Non-PPS'
                 END
         /*
             Per SOW/Client
             --------------
             If Insurance Financial Class 1 = 'Commercial' and Insurance Financial Classes (2 and/or 3) = 'AHCCCS' THEN PPS/NonPPS = "Dual"
             If Insurance Financial Class 1 = 'Medicare' OR 'Medicare Advantage' and and Insurance Financial Classes (2 and/or 3) = 'AHCCCS' THEN PPS/NonPPS = "Medi-Medi"
             If Insurance Financial Class 1 = 'AHCCCS' and regardless of Insurance Financial Classes (2 or 3) THEN PPS/NonPPS = "PPS"
             IF None of the above (ELSE STATEMENT) - PPS/NonPPS = "Non-PPS"
         */
    , [E&M] = NULL
         /*
             If [PPS/NonPPS] = 'Non-PPS' then 'No'
             If CPT Code in ('9', 'D', 'G' , 'H') & [PPS/NonPPS] <> 'Non-PPS' THEN 'Yes' - Else 'No'
         */
    , [Covered] = NULL
         /*
             IF [E&M] = 'Yes' and InsurancePayment > 0 THEN 'Yes', Else 'No'
         */
    , [CPTCodes] = STUFF((
                     SELECT ', ' + LTRIM(RTRIM(ISNULL(pvp.CPTCode, pvp.Code)))
                     FROM PatientVisitProcs pvp
                     WHERE pv.PatientVisitId = pvp.PatientVisitId
                         AND ISNULL(pvp.Voided, 0) = 0
                     ORDER BY pvp.ListOrder
                     FOR XML PATH('')
                     ), 1, 1, '')
, [CPTFirst] = CPTFirst.CPT
, [LastCorrNote] = corr.CorrNote
FROM cusFinancialAggregates cfa
    JOIN PatientVisit pv ON cfa.PatientVisitId = pv.PatientVisitId
    JOIN PatientVisitAgg pva ON pv.PatientVisitId = pva.PatientVisitId
    LEFT JOIN MedLists fc ON pv.FinancialClassMId = fc.MedListsId
    JOIN DoctorFacility doc ON cfa.ResponsibleProviderId = doc.DoctorFacilityId
    JOIN DoctorFacility fac ON cfa.VisitFacilityId = fac.DoctorFacilityId
    JOIN cusDoctorFacility_VR res ON cfa.ResourceId = res.Id
    JOIN cusInsuranceCarrier_VR ic ON cfa.PrimaryInsuranceCarrierId = ic.InsuranceCarrierId
    JOIN cusInsuranceCarrier_VR ic2 ON cfa.SecondaryInsuranceCarrierId = ic2.InsuranceCarrierId
    LEFT JOIN cusInsuranceCarrier_VR ic3 ON cfa.CurrentCarrierId = ic3.InsuranceCarrierId AND cfa.CarrierOrder = 3
    JOIN cusPatient_VR cp ON cfa.PatientProfileId = cp.PatientProfileID
    OUTER APPLY
             (
                SELECT TOP 1 CASE WHEN pc.Description = '**long**' THEN CAST(pc.DescriptionLong AS VARCHAR(255)) ELSE pc.Description END AS CorrNote
                FROM PatientCorrespondence pc
                WHERE pv.PatientVisitId = pc.PatientVisitId
                ORDER BY pc.Created DESC
             ) corr
CROSS APPLY
             (
                SELECT DISTINCT
                  CPT = STUFF((
                                SELECT ',' + LEFT(LTRIM(RTRIM(ISNULL(pvp.CPTCode, pvp.Code))),1)
                                FROM PatientVisitProcs pvp
                                WHERE pv.PatientVisitId = pvp.PatientVisitId
                                  AND ISNULL(pvp.Voided, 0) = 0
                                ORDER BY pvp.ListOrder
                                FOR XML PATH('')
                                ), 1, 1, '')
             ) CPTFirst
WHERE
     (@VDATETYPE = 'DOS' AND cfa.VisitDate >= @VSTARTDATE AND cfa.VisitDate < DATEADD(D , 1 , @VENDDATE)
    OR @VDATETYPE = 'DOE' AND cfa.EventDate >= @VSTARTDATE AND cfa.EventDate < DATEADD(D , 1 , @VENDDATE))
    AND (@VCOMPANY IS NULL OR cfa.VisitCompanyId IN (SELECT Val FROM dbo.Split(@VCOMPANY , ',')))
    AND (@VDOCTOR IS NULL OR cfa.ResponsibleProviderId IN (SELECT Val FROM dbo.Split(@VDOCTOR , ',')))
    AND (@VFACILITY IS NULL OR cfa.VisitFacilityId IN (SELECT Val FROM dbo.Split(@VFACILITY , ',')))
    AND (@VINSCARRIER IS NULL OR cfa.PrimaryInsuranceCarrierId IN (SELECT Val FROM dbo.Split(@VINSCARRIER , ',')))
    AND (@VINSFINCLASS IS NULL OR ic.FinancialClassMId IN (SELECT Val FROM dbo.Split(@VINSFINCLASS , ',')))
GROUP BY
     cfa.PatientVisitId
    , cfa.TicketNumber
, pv.PatientVisitId
    , cp.PatientId
    , cp.LastName
    , cp.FirstName
    , cp.BirthDate
    , cfa.VisitDate
    , pv.Entered
    , fc.Description
    , ic.InsuranceCarrier
    , ic.FinancialClass
    , ic2.InsuranceCarrier
    , ic2.FinancialClass
    , ic3.InsuranceCarrier
    , ic3.FinancialClass
    , doc.ListName
    , fac.ListName
    , res.ListName
    , pva.PatBalance
    , pva.InsBalance
    , corr.CorrNote
    , CPTFirst.CPT
UPDATE v
SET [E&M] = CASE
                 WHEN [PPS/NonPPS] IN ('Dual' , 'Medi-Medi','PPS') AND [CPTFirst] IN ('9', 'D', 'G' , 'H') THEN 'Yes'
                 ELSE 'No'
             END
FROM #Visit v
SELECT v.*
FROM #Visit v
ORDER BY [PPS/NonPPS]
DROP TABLE #Visit
END;

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

LinksUp SSCertifiable Points: 6526 More actions · Answer 1

If fails because you have '9,T' in the column CPTFirst but you are only checking to see if CPTFirst is EQUAL to 9,D,G or H. I think you will need to parse out the first character of CPTFirst, substring(CPTFirst, 1, 1) and see if that matches your list.

Actual data & create statements are preferable to pictures.

__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/

Jeffs1977 SSC-Addicted Points: 487 More actions · Answer 2

Jeffs1977

SSC-Addicted

Points: 487

February 28, 2019 at 4:31 pm

#2023260

Thank you! That helped me out!

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 3

You CPTFirst column is returning more than a single character - and your condition is checking for a single character, therefore the condition is not true and will not be evaluated to a 'Yes' value. One way to solve this is to change it to:

... AND concat(',', CPTFirst, ',') Like '%,[9DGH],%'

You should consider moving these calculated columns to a CROSS\OUTER APPLY - that way the column values could be used instead of having post update statements. For example:

CROSS APPLY (SELECT CASE
      WHEN ic.FinancialClass = 'Commercial' AND ic2.FinancialClass = 'AHCCCS' THEN 'Dual'
      WHEN ic.FinancialClass = 'Commercial' AND ic3.FinancialClass = 'AHCCCS' THEN 'Dual'
      WHEN ic.FinancialClass LIKE '%Medicare%' AND ic2.FinancialClass = 'AHCCCS' THEN 'Medi-Medi'
      WHEN ic.FinancialClass LIKE '%Medicare%' AND ic3.FinancialClass = 'AHCCCS' THEN 'Medi-Medi'
      WHEN ic.FinancialClass = 'AHCCCS' THEN 'PPS'
      ELSE 'Non-PPS'
      END ) AS t([PPS/NonPPS])

You can also simplify the above using:

WHEN ic.FinancialClass = 'Commercial ' AND 'AHCCCS' IN (ic2.FinancialClass, ic3.FinancialClass) THEN 'Dual'
WHEN ic.FinancialClass LIKE '%Medicare% ' AND 'AHCCCS' IN (ic2.FinancialClass, ic3.FinancialClass) THEN 'Medi-Medi'

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

sgmunson SSC Guru Points: 110640 More actions · Answer 4

I took your original posted code and integrated Jeffrey Williams ideas into it:
/********************************************

EXEC [cusVA_SEHMedicaidReconciliationReport] @DATETYPE = 'DOS' , @STARTDATE = N'01/01/2014' , @ENDDATE = N'01/31/2014' , @COMPANY = NULL , @FACILITY = NULL , @doctor = NULL , @INSFINCLASS = NULL , @INSCARRIER = NULL , @FINPERIOD = '01/01/2014 - 01/31/2014'

*********************************************/

ALTER PROCEDURE dbo.cusVA_SEHMedicaidReconciliationReport ( @DATETYPE VARCHAR(3), @STARTDATE DATETIME, @ENDDATE DATETIME, @COMPANY VARCHAR(255), @FACILITY VARCHAR(2000), @DOCTOR VARCHAR(255), @INSFINCLASS VARCHAR(255), @INSCARRIER VARCHAR(255), @FINPERIOD VARCHAR(90) ) AS BEGIN; SET NOCOUNT ON; ---Set local variables DECLARE @VDATETYPE AS VARCHAR(3) = @DATETYPE, @VSTARTDATE AS DATETIME = @STARTDATE, @VENDDATE AS DATETIME = @ENDDATE, @VCOMPANY AS VARCHAR(255) = @COMPANY, @VFACILITY AS VARCHAR(2000) = @FACILITY, @VDOCTOR AS VARCHAR(255) = @doctor, @VINSFINCLASS AS VARCHAR(255) = @INSFINCLASS, @VINSCARRIER AS VARCHAR(255) = @INSCARRIER, @VFFINPERIOD AS VARCHAR(90) = @FINPERIOD;

CREATE TABLE #Visit ( PatientVisitId INT, TicketNumber VARCHAR(30), PatientId VARCHAR(20), LastName VARCHAR(60), FirstName VARCHAR(35), BirthDate DATE, PatientAgeTOS INT, DOSTicket DATETIME, DOETicket DATETIME, FinancialPeriod VARCHAR(90), VisitFinancialClass VARCHAR(60), PrimaryIns VARCHAR(60), PrimaryInsFinancialClass VARCHAR(60), SecondaryIns VARCHAR(60), SecondaryInsFinancialClass VARCHAR(60), TertiaryIns VARCHAR(60), TertiaryInsFinancialClass VARCHAR(60), MTH VARCHAR(2), [Year] VARCHAR(4), Doctor VARCHAR(60), Facility VARCHAR(60), Department VARCHAR(10), [Resource] VARCHAR(60), InsuranceFee MONEY, PatientFee MONEY, TotalFee MONEY, InsurancePayment MONEY, PatientPayment MONEY, TotalPayment MONEY, InsuranceAdjustment MONEY, PatientAdjustment MONEY, TotalAdjustment MONEY, InsuranceBalance MONEY, PatientBalance MONEY, TotalBalance MONEY, [PPS/NonPPS] VARCHAR(30), [E&M] VARCHAR(5), Covered VARCHAR(1), CPTCodes VARCHAR(255), CPTFirst VARCHAR(255), LastCorrNote VARCHAR(255) ); INSERT INTO #Visit SELECT PatientVisitId = cfa.PatientVisitId, TicketNumber = cfa.TicketNumber, PatientId = cp.PatientId, LastName = cp.LastName, FirstName = cp.FirstName, BirthDate = cp.BirthDate, PatientAgeTOS = (0 + CONVERT(VARCHAR(8), cfa.VisitDate, 112) - CONVERT(VARCHAR(8), cp.BirthDate, 112)) / 10000, DOSTicket = cfa.VisitDate, DOETicket = pv.Entered, FinancialPeriod = @VFFINPERIOD, VisitFinancialClass = fc.[Description], PrimaryIns = ic.InsuranceCarrier, PrimaryInsFinancialClass = ic.FinancialClass, SecondaryIns = ic2.InsuranceCarrier, SecondaryInsFinancialClass = ic2.FinancialClass, TertiaryIns = ISNULL(ic3.InsuranceCarrier,'No Insurance Carrier'), TertiaryInsFinancialClass = ISNULL(ic3.FinancialClass,'No Financial Class'), MTH = SUBSTRING(CONVERT(NVARCHAR(6), cfa.VisitDate, 112), 5, 2), [Year] = DATEPART(YEAR, cfa.VisitDate), Doctor = doc.ListName, Facility = fac.ListName, Department = CASE WHEN fac.ListName LIKE '%Dental%' THEN '30020' WHEN fac.ListName LIKE '%Behavioral%' THEN '30012' ELSE '30010' END, [Resource] = ISNULL(res.ListName, 'No Resource'), InsuranceFee = SUM(CASE WHEN cfa.TransactionType = 'Chgs' THEN - cfa.InsuranceAmount ELSE 0 END), PatientFee = SUM(CASE WHEN cfa.TransactionType = 'Chgs' THEN - cfa.PatientAmount ELSE 0 END), TotalFee = SUM(CASE WHEN cfa.TransactionType = 'Chgs' THEN - cfa.InsuranceAmount + - cfa.PatientAmount ELSE 0 END), InsurancePayment = SUM(CASE WHEN cfa.TransactionType = 'Pmts' THEN cfa.InsuranceAmount ELSE 0 END), PatientPayment = SUM(CASE WHEN cfa.TransactionType = 'Pmts' THEN cfa.PatientAmount ELSE 0 END), TotalPayment = SUM(CASE WHEN cfa.TransactionType = 'Pmts' THEN cfa.InsuranceAmount + cfa.PatientAmount ELSE 0 END), InsuranceAdjustment = SUM(CASE WHEN cfa.TransactionType IN ('NAdj','CAdj') THEN cfa.InsuranceAmount ELSE 0 END), PatientAdjustment = SUM(CASE WHEN cfa.TransactionType IN ('NAdj','CAdj') THEN cfa.PatientAmount ELSE 0 END), TotalAdjustment = SUM(CASE WHEN cfa.TransactionType IN ('NAdj','CAdj') THEN cfa.InsuranceAmount + cfa.PatientAmount ELSE 0 END), InsuranceBalance = pva.InsBalance, PatientBalance = pva.PatBalance, TotalBalance = pva.InsBalance + pva.PatBalance, PPS.[PPS/NonPPS], /* Per SOW/Client -------------- If Insurance Financial Class 1 = 'Commercial' and Insurance Financial Classes (2 and/or 3) = 'AHCCCS' THEN PPS/NonPPS = "Dual" If Insurance Financial Class 1 = 'Medicare' OR 'Medicare Advantage' and and Insurance Financial Classes (2 and/or 3) = 'AHCCCS' THEN PPS/NonPPS = "Medi-Medi" If Insurance Financial Class 1 = 'AHCCCS' and regardless of Insurance Financial Classes (2 or 3) THEN PPS/NonPPS = "PPS" IF None of the above (ELSE STATEMENT) - PPS/NonPPS = "Non-PPS" */ EM.[E&M], /* If [PPS/NonPPS] = 'Non-PPS' then 'No' If CPT Code in ('9', 'D', 'G' , 'H') & [PPS/NonPPS] <> 'Non-PPS' THEN 'Yes' - Else 'No' */ Covered = CASE WHEN EM.[E&M] = 'Yes' AND SUM(CASE WHEN cfa.TransactionType = 'Pmts' THEN cfa.InsuranceAmount ELSE 0 END) > 0 THEN 'Yes' ELSE 'No' END, /* IF [E&M] = 'Yes' and InsurancePayment > 0 THEN 'Yes', Else 'No' */ CPT.[CPTCodes], CPTFirst = CPTFirst.CPT, LastCorrNote = corr.CorrNote FROM cusFinancialAggregates AS cfa INNER JOIN PatientVisit AS pv ON cfa.PatientVisitId = pv.PatientVisitId INNER JOIN PatientVisitAgg AS pva ON pv.PatientVisitId = pva.PatientVisitId LEFT OUTER JOIN MedLists AS fc ON pv.FinancialClassMId = fc.MedListsId INNER JOIN DoctorFacility AS doc ON cfa.ResponsibleProviderId = doc.DoctorFacilityId INNER JOIN DoctorFacility AS fac ON cfa.VisitFacilityId = fac.DoctorFacilityId INNER JOIN cusDoctorFacility_VR AS res ON cfa.ResourceId = res.Id INNER JOIN cusInsuranceCarrier_VR AS ic ON cfa.PrimaryInsuranceCarrierId = ic.InsuranceCarrierId INNER JOIN cusInsuranceCarrier_VR AS ic2 ON cfa.SecondaryInsuranceCarrierId = ic2.InsuranceCarrierId LEFT OUTER JOIN cusInsuranceCarrier_VR AS ic3 ON cfa.CurrentCarrierId = ic3.InsuranceCarrierId AND cfa.CarrierOrder = 3 INNER JOIN cusPatient_VR cp ON cfa.PatientProfileId = cp.PatientProfileID OUTER APPLY ( SELECT TOP (1) CASE WHEN pc.[Description] = '**long**' THEN CAST(pc.DescriptionLong AS VARCHAR(255)) ELSE pc.Description END AS CorrNote FROM PatientCorrespondence pc WHERE pv.PatientVisitId = pc.PatientVisitId ORDER BY pc.Created DESC ) AS corr CROSS APPLY ( SELECT DISTINCT CPT = STUFF(( SELECT ',' + LEFT(LTRIM(RTRIM(ISNULL(pvp.CPTCode, pvp.Code))),1) FROM PatientVisitProcs pvp WHERE pv.PatientVisitId = pvp.PatientVisitId AND ISNULL(pvp.Voided, 0) = 0 ORDER BY pvp.ListOrder FOR XML PATH('') ), 1, 1, '') ) AS CPTFirst CROSS APPLY ( SELECT [PPS/NonPPS] = CASE WHEN ic.FinancialClass = 'Commercial' AND 'AHCCCS' IN (ic2.FinancialClass, ic3.FinancialClass) THEN 'Dual' WHEN ic.FinancialClass LIKE '%Medicare%' AND 'AHCCCS' IN (ic2.FinancialClass, ic3.FinancialClass) THEN 'Medi-Medi' WHEN ic.FinancialClass LIKE '%Medicare%' AND ic3.FinancialClass = 'AHCCCS' THEN 'Medi-Medi' ELSE 'Non-PPS' END ) AS PPS CROSS APPLY ( SELECT [CPTCodes] = STUFF(( SELECT ', ' + LTRIM(RTRIM(ISNULL(pvp.CPTCode, pvp.Code))) FROM PatientVisitProcs pvp WHERE pv.PatientVisitId = pvp.PatientVisitId AND ISNULL(pvp.Voided, 0) = 0 ORDER BY pvp.ListOrder FOR XML PATH('') ), 1, 1, '') ) AS CPT CROSS APPLY ( SELECT [E&M] = CASE WHEN PPS.[PPS/NonPPS] = 'Non-PPS' THEN 'No' WHEN CPTCodes LIKE '%[9DGH]%' THEN 'Yes' ELSE 'No' END ) AS EM WHERE ( @VDATETYPE = 'DOS' AND cfa.VisitDate >= @VSTARTDATE AND cfa.VisitDate < DATEADD(D , 1 , @VENDDATE) OR @VDATETYPE = 'DOE' AND cfa.EventDate >= @VSTARTDATE AND cfa.EventDate < DATEADD(D , 1 , @VENDDATE) ) AND (@VCOMPANY IS NULL OR cfa.VisitCompanyId IN (SELECT Val FROM dbo.Split(@VCOMPANY , ','))) AND (@VDOCTOR IS NULL OR cfa.ResponsibleProviderId IN (SELECT Val FROM dbo.Split(@VDOCTOR , ','))) AND (@VFACILITY IS NULL OR cfa.VisitFacilityId IN (SELECT Val FROM dbo.Split(@VFACILITY , ','))) AND (@VINSCARRIER IS NULL OR cfa.PrimaryInsuranceCarrierId IN (SELECT Val FROM dbo.Split(@VINSCARRIER , ','))) AND (@VINSFINCLASS IS NULL OR ic.FinancialClassMId IN (SELECT Val FROM dbo.Split(@VINSFINCLASS , ','))) GROUP BY cfa.PatientVisitId , cfa.TicketNumber, pv.PatientVisitId, cp.PatientId, cp.LastName, cp.FirstName, cp.BirthDate, cfa.VisitDate, pv.Entered, fc.[Description], ic.InsuranceCarrier, ic.FinancialClass, ic2.InsuranceCarrier, ic2.FinancialClass, ic3.InsuranceCarrier, ic3.FinancialClass, doc.ListName, fac.ListName, res.ListName, pva.PatBalance, pva.InsBalance, corr.CorrNote CPTFirst.CPT;

SELECT v.* FROM #Visit v ORDER BY [PPS/NonPPS]

DROP TABLE #Visit

END; GO

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)