Sub-Query returns wrong value

JBourne77 · Aug 13, 2009

In its current state, my SubQuery is returning the top transaction in the database and not for the specific patient I pull. I believe I need to hook it into the outer query somehow and I am not certain how. Any help is appreciated.

My SubQuery

Code:

 '999.LastPaymentDate' = 
(
 SELECT
   MAX(b.Entry) AS PaymentDate 
 FROM
   PaymentMethod pm
   INNER JOIN Batch b ON pm.BatchId = b.BatchId
   INNER JOIN patientprofile pp ON pm.PayerId = pp.PatientProfileId
   INNER JOIN Guarantor gr ON pp.GuarantorId = gr.GuarantorId
  WHERE
    pm.PayerType = 'Patient'OR pm.PayerType = 'Guarantor'
    AND pm.Amount <> 0
    )

FULL Stored Procedure

Code:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/****** Object:  Stored Procedure dbo.MPMSuperbill    Script Date: 7/22/2002 1:58:14 PM ******/

ALTER          PROCEDURE [dbo].[cusMPMSuperbillPardee]
      @pApptsId INT ,
      @pDummy INT = NULL
AS 
      BEGIN
            SET NOCOUNT ON

            CREATE TABLE #Contacts_tmp
                   (
                     id_col NUMERIC(3 , 0) IDENTITY ,
                     PatientProfileId INT NULL ,
                     First VARCHAR(50) NULL ,
                     Middle VARCHAR(50) NULL ,
                     Last VARCHAR(50) NULL ,
                     Phone1 VARCHAR(15) NULL ,
                     ContactRelToPatient VARCHAR(200) NULL ,
                     Created DATETIME NULL
                   )
  	
/* Get a ticket number */
            DECLARE @ApptSetId INT
            DECLARE @DoctorId INT
            DECLARE @CompanyId INT
            DECLARE @FacilityId INT
            DECLARE @CasesId INT
            DECLARE @TicketNumber VARCHAR(30)
            DECLARE @AppointmentsId INT
            DECLARE @ApptStop DATETIME
            DECLARE @PatientProfileId INT

            SELECT
                @ApptSetId = a.ApptSetId ,
                @DoctorId = ISNULL(a.DoctorId , 0) ,
                @CompanyId = ISNULL(a.CompanyId , 0) ,
                @FacilityId = ISNULL(a.FacilityId , 0) ,
                @CasesId = ISNULL(a.CasesId , 0) ,
                @TicketNumber = a.TicketNumber ,
                @AppointmentsId = a.AppointmentsId ,		/* this one is silly?  */
                @ApptStop = a.ApptStop ,
                @PatientProfileId = a.OwnerId
            FROM
                Appointments a
            WHERE
                a.AppointmentsId = @pApptsId

            IF @TicketNumber IS NULL /* only if we do not already have a ticket number */ 
               BEGIN
                     EXEC rpGetTicket @FacilityId , @TicketNumber OUTPUT

	/* Write Ticket Number back to all appoinments covered by this superbill */
                     IF @ApptSetId IS NULL 
                        BEGIN
                              UPDATE
                                Appointments
                              SET
                                TicketNumber = @TicketNumber
                              WHERE
                                AppointmentsId = @AppointmentsId
                        END
                     ELSE 
                        BEGIN
                              UPDATE
                                Appointments
                              SET
                                TicketNumber = @TicketNumber
                              WHERE
                                ApptSetId = @ApptSetId		/* hit entire Billing Set */
--								AND (DoctorId = @DoctorId OR DoctorId is null)
--								AND (CompanyId = @CompanyId OR CompanyId is null)
--								AND (FacilityId = @FacilityId OR FacilityId is null)
--								AND (CasesId = @CasesId OR CasesId is null)
                                AND ISNULL(DoctorId , 0) = @DoctorId
                                AND ISNULL(CompanyId , 0) = @CompanyId
                                AND ISNULL(FacilityId , 0) = @FacilityId
                                AND ISNULL(CasesId , 0) = @CasesId
                                AND TicketNumber IS NULL		/* any member already holding, retains it */
                        END
               END

            DECLARE @LastPatientVisitId INT
            DECLARE @LastVisitDate DATETIME

            SELECT TOP 1
                @LastPatientVisitId = pv.PatientVisitId ,
                @LastVisitDate = pv.Visit
            FROM
                PatientVisit pv
            WHERE
                pv.Visit < DATEADD(day , 1 , ISNULL(@ApptStop , '1/1/3000'))
                AND pv.PatientProfileId = @PatientProfileId
            ORDER BY
                pv.Visit DESC

            SELECT
                '1.Diag1Code' = ISNULL(pvd.ICD9Code , '') ,
                '2.Diag1Description' = ISNULL(pvd.Description , '')
            FROM
                PatientVisitDiags pvd
            WHERE
                pvd.PatientVisitId = @LastPatientVisitId
                AND pvd.ListOrder = 1

            SELECT
                '3.Diag2Code' = ISNULL(pvd.ICD9Code , '') ,
                '4.Diag2Description' = ISNULL(pvd.Description , '')
            FROM
                PatientVisitDiags pvd
            WHERE
                pvd.PatientVisitId = @LastPatientVisitId
                AND pvd.ListOrder = 2

            SELECT
                '5.Diag3Code' = ISNULL(pvd.ICD9Code , '') ,
                '6.Diag3Description' = ISNULL(pvd.Description , '')
            FROM
                PatientVisitDiags pvd
            WHERE
                pvd.PatientVisitId = @LastPatientVisitId
                AND pvd.ListOrder = 3

            SELECT
                '7.Diag4Code' = ISNULL(pvd.ICD9Code , '') ,
                '8.Diag4Description' = ISNULL(pvd.Description , '')
            FROM
                PatientVisitDiags pvd
            WHERE
                pvd.PatientVisitId = @LastPatientVisitId
                AND pvd.ListOrder = 4

/* Create a table to store the cases insurance information and change nulls to 0 for speed */
            DECLARE @tblCasesInsurance TABLE
                    (
                      [CasesId] [int] NULL ,
                      [PatientProfileId] [int] NULL ,
                      [OrderForClaims] [smallint] NULL ,
                      [PatientInsuranceId] [int] NULL ,
                      [InsuranceCarriersId] [int] NULL
                    )

            INSERT INTO
                @tblCasesInsurance
                (
                  CasesID ,
                  PatientProfileId ,
                  OrderForClaims ,
                  PatientInsuranceId ,
                  InsuranceCarriersId
                )
                SELECT
                    ISNULL(ci.CasesID , 0) ,
                    ci.PatientProfileId ,
                    ci.OrderForClaims ,
                    ci.PatientInsuranceId ,
                    ci.InsuranceCarriersId
                FROM
                    CasesInsurance ci
                WHERE
                    ci.PatientProfileId = @PatientProfileId
                    AND (
                          ci.Inactive IS NULL
                          OR ci.Inactive = 0
                        ) 

            SELECT
                '10.Carrier1Name' = ISNULL(ic.Name , '') ,
                '11.Carrier1ListName' = ISNULL(ic.Listname , '') ,
                '12.Carrier1Address1' = ISNULL(ic.Address1 , '') ,
                '13.Carrier1Address2' = ISNULL(ic.Address2 , '') ,
                '14.Carrier1City' = ISNULL(ic.City , '') ,
                '15.Carrier1State' = ISNULL(ic.State , '') ,
                '16.Carrier1Zip' = ISNULL(ic.Zip , '') ,
                '17.Carrier1Country' = ISNULL(ic.Country , '') ,
                '18.Carrier1Contact' = ISNULL(ic.Contact , '') ,
                '19.Carrier1Phone' = ISNULL(ic.Phone1 , '') ,
                '20.Carrier1FinancialClass' = ISNULL(ml.Description , '') ,
                '21.Carrier1AllocationType' = ISNULL(al.Name , '') ,
                '22.Carrier1CarrierType' = ISNULL(ml3.Description , '') ,
                '23.Carrier1PolicyType' = ISNULL(ml4.Description , '') ,
                '24.Carrier1GroupId' = ISNULL(ic.GroupId , '') ,
                '25.Carrier1GroupName' = ISNULL(ic.GroupName , '') ,
                '110.Insured1SameAsPatient' = ISNULL(pi.InsuredSameAsPatient , 0) ,
                '111.Insured1SameAsGuarantor' = ISNULL(pi.InsuredSameAsGuarantor , 0) ,
                '112.Insured1PatRelToInsured' = ISNULL(ml2.Description , '') ,
                '113.Insured1First' = ISNULL(pi.First , '') ,
                '114.Insured1Last' = ISNULL(pi.Last , '') ,
                '115.Insured1Middle' = ISNULL(pi.Middle , '') ,
                '116.Insured1Address1' = ISNULL(pi.Address1 , '') ,
                '117.Insured1Address2' = ISNULL(pi.Address2 , '') ,
                '118.Insured1City' = ISNULL(pi.City , '') ,
                '119.Insured1State' = ISNULL(pi.State , '') ,
                '120.Insured1Zip' = ISNULL(pi.Zip , '') ,
                '121.Insured1Country' = ISNULL(ic.Country , '') ,
                '122.Insured1Phone' = ISNULL(pi.Phone1 , '') ,
                '123.Insured1ID' = ISNULL(pi.InsuredID , '')
            FROM
                @tblCasesInsurance ci
                JOIN PatientInsurance pi ON ci.PatientInsuranceId = pi.PatientInsuranceId
                INNER JOIN InsuranceCarriers ic ON pi.InsuranceCarriersId = ic.InsuranceCarriersId
                LEFT JOIN Medlists ml ON ic.FinancialClassMid = ml.MedListsId
                LEFT JOIN AllocationSet al ON ic.AllocationSetid = al.AllocationSetid
                LEFT JOIN Medlists ml3 ON ic.CarrierTypeMid = ml3.MedListsId
                LEFT JOIN Medlists ml4 ON ic.PolicyTypeMid = ml4.MedListsId
                LEFT JOIN Medlists ml2 ON pi.PatRelToInsuredMID = ml2.MedListsId
            WHERE
                ci.CasesId = ISNULL(@CasesId , 0)
                AND ci.PatientProfileId = @PatientProfileId
                AND ci.OrderForClaims = 1

            SELECT
                '30.Carrier2Name' = ISNULL(ic.Name , '') ,
                '31.Carrier2ListName' = ISNULL(ic.ListName , '') ,
                '32.Carrier2Address1' = ISNULL(ic.Address1 , '') ,
                '33.Carrier2Address2' = ISNULL(ic.Address2 , '') ,
                '34.Carrier2City' = ISNULL(ic.City , '') ,
                '35.Carrier2State' = ISNULL(ic.State , '') ,
                '36.Carrier2Zip' = ISNULL(ic.Zip , '') ,
                '37.Carrier2Country' = ISNULL(ic.Country , '') ,
                '38.Carrier2Contact' = ISNULL(ic.Contact , '') ,
                '39.Carrier2Phone' = ISNULL(ic.Phone1 , '') ,
                '40.Carrier2FinancialClass' = ISNULL(ml.Description , '') ,
                '41.Carrier2AllocationType' = ISNULL(al.Name , '') ,
                '42.Carrier2CarrierType' = ISNULL(ml3.Description , '') ,
                '43.Carrier2PolicyType' = ISNULL(ml4.Description , '') ,
                '44.Carrier2GroupId' = ISNULL(ic.GroupId , '') ,
                '45.Carrier2GroupName' = ISNULL(ic.GroupName , '') ,
                '130.Insured2SameAsPatient' = ISNULL(pi.InsuredSameAsPatient , 0) ,
                '131.Insured2SameAsGuarantor' = ISNULL(pi.InsuredSameAsGuarantor , 0) ,
                '132.Insured2PatRelToInsured' = ISNULL(ml2.Description , '') ,
                '133.Insured2First' = ISNULL(pi.First , '') ,
                '134.Insured2Last' = ISNULL(pi.Last , '') ,
                '135.Insured2Middle' = ISNULL(pi.Middle , '') ,
                '136.Insured2Address1' = ISNULL(pi.Address1 , '') ,
                '137.Insured2Address2' = ISNULL(pi.Address2 , '') ,
                '138.Insured2City' = ISNULL(pi.City , '') ,
                '139.Insured2State' = ISNULL(pi.State , '') ,
                '140.Insured2Zip' = ISNULL(pi.Zip , '') ,
                '141.Insured2Country' = ISNULL(pi.Country , '') ,
                '142.Insured2Phone' = ISNULL(pi.Phone1 , '') ,
                '143.Insured2ID' = ISNULL(pi.InsuredID , '')
            FROM
                @tblCasesInsurance ci
                JOIN PatientInsurance pi ON ci.PatientInsuranceId = pi.PatientInsuranceId
                INNER JOIN InsuranceCarriers ic ON pi.InsuranceCarriersId = ic.InsuranceCarriersId
                LEFT JOIN Medlists ml ON ic.FinancialClassMid = ml.MedListsId
                LEFT JOIN AllocationSet al ON ic.AllocationSetid = al.AllocationSetid
                LEFT JOIN Medlists ml3 ON ic.CarrierTypeMid = ml3.MedListsId
                LEFT JOIN Medlists ml4 ON ic.PolicyTypeMid = ml4.MedListsId
                LEFT JOIN Medlists ml2 ON pi.PatRelToInsuredMID = ml2.MedListsId
            WHERE
                ci.CasesId = ISNULL(@CasesId , 0)
                AND ci.PatientProfileId = @PatientProfileId
                AND ci.OrderForClaims = 2

            SELECT
                '50.Carrier3Name' = ISNULL(ic.Name , '') ,
                '51.Carrier3ListName' = ISNULL(ic.ListName , '') ,
                '52.Carrier3Address1' = ISNULL(ic.Address1 , '') ,
                '53.Carrier3Address2' = ISNULL(ic.Address2 , '') ,
                '54.Carrier3City' = ISNULL(ic.City , '') ,
                '55.Carrier3State' = ISNULL(ic.State , '') ,
                '56.Carrier3Zip' = ISNULL(ic.Zip , '') ,
                '57.Carrier3Country' = ISNULL(ic.Country , '') ,
                '58.Carrier3Contact' = ISNULL(ic.Contact , '') ,
                '59.Carrier3Phone' = ISNULL(ic.Phone1 , '') ,
                '60.Carrier3FinancialClass' = ISNULL(ml.Description , '') ,
                '61.Carrier3AllocationType' = ISNULL(al.Name , '') ,
                '62.Carrier3CarrierType' = ISNULL(ml3.Description , '') ,
                '63.Carrier3PolicyType' = ISNULL(ml4.Description , '') ,
                '64.Carrier3GroupId' = ISNULL(ic.GroupId , '') ,
                '65.Carrier3GroupName' = ISNULL(ic.GroupName , '') ,
                '150.Insured3SameAsPatient' = ISNULL(pi.InsuredSameAsPatient , 0) ,
                '151.Insured3SameAsGuarantor' = ISNULL(pi.InsuredSameAsGuarantor , 0) ,
                '152.Insured3PatRelToInsured' = ISNULL(ml2.Description , '') ,
                '153.Insured3First' = ISNULL(pi.First , '') ,
                '154.Insured3Last' = ISNULL(pi.Last , '') ,
                '155.Insured3Middle' = ISNULL(pi.Middle , '') ,
                '156.Insured3Address1' = ISNULL(pi.Address1 , '') ,
                '157.Insured3Address2' = ISNULL(pi.Address2 , '') ,
                '158.Insured3City' = ISNULL(pi.City , '') ,
                '159.Insured3State' = ISNULL(pi.State , '') ,
                '160.Insured3Zip' = ISNULL(pi.Zip , '') ,
                '161.Insured3Country' = ISNULL(pi.Country , '') ,
                '162.Insured3Phone' = ISNULL(pi.Phone1 , '') ,
                '163.Insured3ID' = ISNULL(pi.InsuredID , '')
            FROM
                @tblCasesInsurance ci
                JOIN PatientInsurance pi ON ci.PatientInsuranceId = pi.PatientInsuranceId
                INNER JOIN InsuranceCarriers ic ON pi.InsuranceCarriersId = ic.InsuranceCarriersId
                LEFT JOIN Medlists ml ON ic.FinancialClassMid = ml.MedListsId
                LEFT JOIN AllocationSet al ON ic.AllocationSetid = al.AllocationSetid
                LEFT JOIN Medlists ml3 ON ic.CarrierTypeMid = ml3.MedListsId
                LEFT JOIN Medlists ml4 ON ic.PolicyTypeMid = ml4.MedListsId
                LEFT JOIN Medlists ml2 ON pi.PatRelToInsuredMID = ml2.MedListsId
            WHERE
                ci.CasesId = ISNULL(@CasesId , 0)
                AND ci.PatientProfileId = @PatientProfileId
                AND ci.OrderForClaims = 3

            SELECT
                '70.Carrier4Name' = ISNULL(ic.Name , '') ,
                '71.Carrier4ListName' = ISNULL(ic.ListName , '') ,
                '72.Carrier4Address1' = ISNULL(ic.Address1 , '') ,
                '73.Carrier4Address2' = ISNULL(ic.Address2 , '') ,
                '74.Carrier4City' = ISNULL(ic.City , '') ,
                '75.Carrier4State' = ISNULL(ic.State , '') ,
                '76.Carrier4Zip' = ISNULL(ic.Zip , '') ,
                '77.Carrier4Country' = ISNULL(ic.Country , '') ,
                '78.Carrier4Contact' = ISNULL(ic.Contact , '') ,
                '79.Carrier4Phone' = ISNULL(ic.Phone1 , '') ,
                '80.Carrier4FinancialClass' = ISNULL(ml.Description , '') ,
                '81.Carrier4AllocationType' = ISNULL(al.Name , '') ,
                '82.Carrier4CarrierType' = ISNULL(ml3.Description , '') ,
                '83.Carrier4PolicyType' = ISNULL(ml4.Description , '') ,
                '84.Carrier4GroupId' = ISNULL(ic.GroupId , '') ,
                '85.Carrier4GroupName' = ISNULL(ic.GroupName , '') ,
                '170.Insured4SameAsPatient' = ISNULL(pi.InsuredSameAsPatient , 0) ,
                '171.Insured4SameAsGuarantor' = ISNULL(pi.InsuredSameAsGuarantor , 0) ,
                '172.Insured4PatRelToInsured' = ISNULL(ml2.Description , '') ,
                '173.Insured4First' = ISNULL(pi.First , '') ,
                '174.Insured4Last' = ISNULL(pi.Last , '') ,
                '175.Insured4Middle' = ISNULL(pi.Middle , '') ,
                '176.Insured4Address1' = ISNULL(pi.Address1 , '') ,
                '177.Insured4Address2' = ISNULL(pi.Address2 , '') ,
                '178.Insured4City' = ISNULL(pi.City , '') ,
                '179.Insured4State' = ISNULL(pi.State , '') ,
                '180.Insured4Zip' = ISNULL(pi.Zip , '') ,
                '181.Insured4Country' = ISNULL(pi.Country , '') ,
                '182.Insured4Phone' = ISNULL(pi.Phone1 , '') ,
                '183.Insured4ID' = ISNULL(pi.InsuredID , '')
            FROM
                @tblCasesInsurance ci
                JOIN PatientInsurance pi ON ci.PatientInsuranceId = pi.PatientInsuranceId
                INNER JOIN InsuranceCarriers ic ON pi.InsuranceCarriersId = ic.InsuranceCarriersId
                LEFT JOIN Medlists ml ON ic.FinancialClassMid = ml.MedListsId
                LEFT JOIN AllocationSet al ON ic.AllocationSetid = al.AllocationSetid
                LEFT JOIN Medlists ml3 ON ic.CarrierTypeMid = ml3.MedListsId
                LEFT JOIN Medlists ml4 ON ic.PolicyTypeMid = ml4.MedListsId
                LEFT JOIN Medlists ml2 ON pi.PatRelToInsuredMID = ml2.MedListsId
            WHERE
                ci.CasesId = ISNULL(@CasesId , 0)
                AND ci.PatientProfileId = @PatientProfileId
                AND ci.OrderForClaims = 4

            SELECT
                '90.Carrier5Name' = ISNULL(ic.Name , '') ,
                '91.Carrier5ListName' = ISNULL(ic.ListName , '') ,
                '92.Carrier5Address1' = ISNULL(ic.Address1 , '') ,
                '93.Carrier5Address2' = ISNULL(ic.Address2 , '') ,
                '94.Carrier5City' = ISNULL(ic.City , '') ,
                '95.Carrier5State' = ISNULL(ic.State , '') ,
                '96.Carrier5Zip' = ISNULL(ic.Zip , '') ,
                '97.Carrier5Country' = ISNULL(ic.Country , '') ,
                '98.Carrier5Contact' = ISNULL(ic.Contact , '') ,
                '99.Carrier5Phone' = ISNULL(ic.Phone1 , '') ,
                '100.Carrier5FinancialClass' = ISNULL(ml.Description , '') ,
                '101.Carrier5AllocationType' = ISNULL(al.Name , '') ,
                '102.Carrier5CarrierType' = ISNULL(ml3.Description , '') ,
                '103.Carrier5PolicyType' = ISNULL(ml4.Description , '') ,
                '104.Carrier5GroupId' = ISNULL(ic.GroupId , '') ,
                '105.Carrier5GroupName' = ISNULL(ic.GroupName , '') ,
                '190.Insured5SameAsPatient' = ISNULL(pi.InsuredSameAsPatient , 0) ,
                '191.Insured5SameAsGuarantor' = ISNULL(pi.InsuredSameAsGuarantor , 0) ,
                '192.Insured5PatRelToInsured' = ISNULL(ml2.Description , '') ,
                '193.Insured5First' = ISNULL(pi.First , '') ,
                '194.Insured5Last' = ISNULL(pi.Last , '') ,
                '195.Insured5Middle' = ISNULL(pi.Middle , '') ,
                '196.Insured5Address1' = ISNULL(pi.Address1 , '') ,
                '197.Insured5Address2' = ISNULL(pi.Address2 , '') ,
                '198.Insured5City' = ISNULL(pi.City , '') ,
                '199.Insured5State' = ISNULL(pi.State , '') ,
                '200.Insured5Zip' = ISNULL(pi.Zip , '') ,
                '201.Insured5Country' = ISNULL(pi.Country , '') ,
                '202.Insured5Phone' = ISNULL(pi.Phone1 , '') ,
                '203.Insured5ID' = ISNULL(pi.InsuredID , '')
            FROM
                @tblCasesInsurance ci
                JOIN PatientInsurance pi ON ci.PatientInsuranceId = pi.PatientInsuranceId
                INNER JOIN InsuranceCarriers ic ON pi.InsuranceCarriersId = ic.InsuranceCarriersId
                LEFT JOIN Medlists ml ON ic.FinancialClassMid = ml.MedListsId
                LEFT JOIN AllocationSet al ON ic.AllocationSetid = al.AllocationSetid
                LEFT JOIN Medlists ml3 ON ic.CarrierTypeMid = ml3.MedListsId
                LEFT JOIN Medlists ml4 ON ic.PolicyTypeMid = ml4.MedListsId
                LEFT JOIN Medlists ml2 ON pi.PatRelToInsuredMID = ml2.MedListsId
            WHERE
                ci.CasesId = ISNULL(@CasesId , 0)
                AND ci.PatientProfileId = @PatientProfileId
                AND ci.OrderForClaims = 5

            INSERT
                #Contacts_tmp
                SELECT
                    PatientProfileId ,
                    First ,
                    Middle ,
                    Last ,
                    Phone1 ,
                    ContactRelToPatient ,
                    Created
                FROM
                    PatientContacts pc
                WHERE
                    pc.PatientProfileId = @PatientProfileId
                ORDER BY
                    Created

            SELECT
                '205.Contact1First' = ISNULL(First , '') ,
                '206.Contact1Middle' = ISNULL(Middle , '') ,
                '207.Contact1Last' = ISNULL(Last , '') ,
                '208.Contact1Phone' = ISNULL(Phone1 , '') ,
                '209.Contact1RelToPatient' = ISNULL(ContactRelToPatient , '')
            FROM
                #Contacts_tmp
            WHERE
                id_col = 1

            SELECT
                '210.Contact2First' = ISNULL(First , '') ,
                '211.Contact2Middle' = ISNULL(Middle , '') ,
                '212.Contact2Last' = ISNULL(Last , '') ,
                '213.Contact2Phone' = ISNULL(Phone1 , '') ,
                '214.Contact2RelToPatient' = ISNULL(ContactRelToPatient , '')
            FROM
                #Contacts_tmp
            WHERE
                id_col = 2

            SELECT
                '220.Contact3First' = ISNULL(First , '') ,
                '221.Contact3Middle' = ISNULL(Middle , '') ,
                '222.Contact3Last' = ISNULL(Last , '') ,
                '223.Contact3Phone' = ISNULL(Phone1 , '') ,
                '224.Contact3RelToPatient' = ISNULL(ContactRelToPatient , '')
            FROM
                #Contacts_tmp
            WHERE
                id_col = 3


            SELECT
                '230.PatientProfileID' = @PatientProfileID ,
                '231.DoctorID' = @DoctorID 

            SELECT
                '232.Doctor' = ISNULL(df.First , '') + ' ' + ISNULL(df.Middle , '') + ' ' + ISNULL(df.Last , '') + ' ' + ISNULL(df.Suffix , '') ,
                '233.DoctorFirst' = ISNULL(df.First , '') ,
                '234.DoctorMiddle' = ISNULL(df.Middle , '') ,
                '235.DoctorLast' = ISNULL(df.Last , '') ,
                '236.DoctorSuffix' = ISNULL(df.Suffix , '') ,
                '237.LiscenseNo' = ISNULL(df.StateLicenseNo , '') ,
                '238.UPIN' = ISNULL(df.UPIN , '') ,
                '239.FederalTaxID' = ISNULL(df.FederalTaxID , '')
            FROM
                DoctorFacility df
            WHERE
                df.DoctorFacilityId = @DoctorId

            SELECT
                '240.Facility' = ISNULL(df2.OrgName , '') ,
                '241.FacilityAddr1' = ISNULL(df2.Address1 , '') ,
                '242.FacilityAddr2' = ISNULL(df2.Address2 , '') ,
                '243.FacilityCity' = ISNULL(df2.City , '') ,
                '244.FacilityState' = ISNULL(df2.State , '') ,
                '245.FacilityZip' = ISNULL(df2.Zip , '') ,
                '246.FacilityPhone' = ISNULL(df2.Phone1 , '')
            FROM
                DoctorFacility df2
            WHERE
                df2.DoctorFacilityID = @FacilityId

            SELECT
                '247.NPI' = ISNULL(df.NPI , '')
            FROM
                DoctorFacility df
            WHERE
                df.DoctorFacilityId = @DoctorId
		
            SELECT
                '250.PatDoctor' = ISNULL(d.First , '') + ' ' + ISNULL(d.Middle , '') + ' ' + ISNULL(d.Last , '') + ' ' + ISNULL(d.Suffix , '')
            FROM
                PatientProfile pp
                INNER JOIN DoctorFacility d ON pp.DoctorId = d.DoctorFacilityId
            WHERE
                pp.PatientProfileId = @PatientProfileId
			

            SELECT
                '255.AppointmentsID' = a.AppointmentsID ,
                '256.ApptStart' = a.ApptStart ,
                '257.StartHour' = ISNULL(CONVERT(VARCHAR(2) , DATEPART(hour , a.ApptStart)) , '') ,
                '258.StartMinute' = ISNULL(CONVERT(VARCHAR(2) , DATEPART(minute , a.ApptStart)) , '') ,
                '259.StopHour' = ISNULL(CONVERT(VARCHAR(2) , DATEPART(hour , a.ApptStop)) , '') ,
                '260.StopMinute' = ISNULL(CONVERT(VARCHAR(2) , DATEPART(minute , a.ApptStop)) , '') ,
                '261.TicketNumber' = ISNULL(@TicketNumber , '')
            FROM
                Appointments a
            WHERE
                a.AppointmentsId = @AppointmentsId

            DECLARE
                    @InsDeposit MONEY ,
                    @PatDeposit MONEY ,
                    @InsBalance0 MONEY ,
                    @PatBalance0 MONEY ,
                    @InsBalance30 MONEY ,
                    @PatBalance30 MONEY ,
                    @InsBalance60 MONEY ,
                    @PatBalance60 MONEY ,
                    @InsBalance90 MONEY ,
                    @PatBalance90 MONEY ,
                    @InsBalance120 MONEY ,
                    @PatBalance120 MONEY ,
                    @InsBalance MONEY ,
                    @PatBalance MONEY
            DECLARE @DB VARCHAR(128)
            SELECT
                @DB = DB_NAME()

            EXEC master..mbcxp_AgingSummary70 @DB , @PatientProfileId , 1 , NULL , 1 , NULL , NULL , NULL , @InsDeposit OUTPUT , @PatDeposit OUTPUT ,
                 @InsBalance0 OUTPUT , @PatBalance0 OUTPUT , @InsBalance30 OUTPUT , @PatBalance30 OUTPUT , @InsBalance60 OUTPUT , @PatBalance60 OUTPUT ,
                 @InsBalance90 OUTPUT , @PatBalance90 OUTPUT , @InsBalance120 OUTPUT , @PatBalance120 OUTPUT , @InsBalance OUTPUT , @PatBalance OUTPUT

            SELECT
                '270.PatientBalance' = ISNULL(ppa.PatBalance , 0) ,
                '271.PatientBalance0' = ISNULL(@PatBalance0 , 0) ,
                '272.PatientBalance30' = ISNULL(@PatBalance30 , 0) ,
                '273.PatientBalance60' = ISNULL(@PatBalance60 , 0) ,
                '274.PatientBalance90' = ISNULL(@PatBalance90 , 0) ,
                '275.PatientBalance120' = ISNULL(@PatBalance120 , 0) ,
                '276.InsuranceBalance' = ISNULL(ppa.InsBalance , 0)
            FROM
                PatientProfileAgg ppa
            WHERE
                ppa.PatientProfileID = @PatientProfileID

            SELECT
                '280.AppointmentType' = ISNULL(MAX(ISNULL(at.Name , '')) , '')
            FROM
                Appointments a
                LEFT JOIN ApptType at ON a.ApptTypeId = at.ApptTypeId
            WHERE
                a.AppointmentsId = @pApptsId

            SELECT
                '290.PatientName' = ISNULL(pp.First , '') + ' ' + ISNULL(pp.Middle , '') + ' ' + ISNULL(pp.Last , '') + ' ' + ISNULL(pp.Suffix , '') ,
                '291.PatLast' = ISNULL(pp.Last , '') ,
                '292.PatFirst' = ISNULL(pp.First , '') ,
                '293.PatMiddle' = ISNULL(pp.Middle , '') ,
                '294.PatientAddr1' = ISNULL(pp.Address1 , '') ,
                '295.PatientAddr2' = ISNULL(pp.Address2 , '') ,
                '296.PatientCity' = ISNULL(pp.City , '') ,
                '297.PatientState' = ISNULL(pp.State , '') ,
                '298.PatientZip' = ISNULL(pp.Zip , '') ,
                '299.PatientCountry' = ISNULL(pp.Country , '') ,
                '300.PatientBirthdate' = pp.Birthdate ,
                '301.PatientSex' = ISNULL(pp.Sex , '') ,
                '302.PatientPhone1' = ISNULL(pp.Phone1 , '') ,
                '303.PatientSSN' = ISNULL(pp.SSN , '') ,
                '304.PatOccupation' = ISNULL(pp.EmpOccup , '') ,
                '305.PatSchool' = ISNULL(pp.SchoolName , '') ,
                '306.PatBudget' = ISNULL(g.Budget , 0) ,
                '307.PatientSameAsGuarantor' = ISNULL(pp.PatientSameAsGuarantor , 0) ,
                '308.PatSuffix' = ISNULL(pp.Suffix , '')
            FROM
                PatientProfile pp
                LEFT JOIN Guarantor g ON pp.GuarantorId = g.GuarantorId
            WHERE
                pp.PatientProfileID = @PatientProfileId

            SELECT
                '310.PatEmpStatus' = ISNULL(ml3.Description , '') ,
                '311.PatStudentStatus' = ISNULL(ml4.Description , '') ,
                '312.PatMaritalStatus' = ISNULL(ml5.Description , '') ,
                '313.PatResidence' = ISNULL(ml7.Description , '') ,
                '314.PatientID' = pp.PatientID ,
                '309.PatientIDNumeric' = CASE WHEN ISNUMERIC(pp.PatientID) = 1
                                                   AND LEN(pp.PatientId) <= 9 THEN CONVERT(INT , pp.PatientID)
                                              ELSE 0
                                         END ,
                '315.PatientRelationToGuarantor' = ISNULL(ml6.Description , '') ,
                '316.GuarantorName' = ISNULL(g.First , '') + ' ' + ISNULL(g.Middle , '') + ' ' + ISNULL(g.Last , '') ,
                '317.GuarantorAddr1' = ISNULL(g.Address1 , '') ,
                '318.GuarantorAddr2' = ISNULL(g.Address2 , '') ,
                '319.GuarantorCity' = ISNULL(g.City , '') ,
                '320.GuarantorState' = ISNULL(g.State , '') ,
                '321.GuarantorZip' = ISNULL(g.Zip , '') ,
                '322.GuarantorCountry' = ISNULL(g.Country , '') ,
                '323.GuarantorPhone' = ISNULL(g.Phone1 , '') ,
                '324.PatientFinancial' = ISNULL(ml.Description , '') ,
                '325.Allocation' = ISNULL(al.Name , '') ,
                '999.LastPaymentDate' = (
                                          SELECT
                                            MAX(b.Entry) AS PaymentDate 
                                          FROM
                                            PaymentMethod pm
                                            INNER JOIN Batch b ON pm.BatchId = b.BatchId
                                            INNER JOIN patientprofile pp ON pm.PayerId = pp.PatientProfileId
                                            INNER JOIN Guarantor gr ON pp.GuarantorId = gr.GuarantorId
                                          WHERE
                                            pm.PayerType = 'Patient'OR pm.PayerType = 'Guarantor'
                                            AND pm.Amount <> 0
                                        )
		
            FROM
                PatientProfile pp
                LEFT JOIN Guarantor g ON pp.GuarantorId = g.GuarantorId
                LEFT JOIN MedLists ml ON pp.FinancialClassMID = ml.MedListsID
                LEFT JOIN AllocationSet al ON pp.AllocationSetId = al.AllocationSetid
                LEFT JOIN MedLists ml3 ON pp.EmpStatusMID = ml3.MedListsID
                LEFT JOIN MedLists ml4 ON pp.StudentStatusMID = ml4.MedListsID
                LEFT JOIN MedLists ml5 ON pp.MaritalStatusMID = ml5.MedListsID
                LEFT JOIN MedLists ml6 ON pp.PatientRelationToGuarantorMID = ml6.MedListsID
                LEFT JOIN MedLists ml7 ON pp.ResidenceTypeMID = ml7.MedListsID
                LEFT JOIN Employer emp ON pp.EmployerId = emp.EmployerId
            WHERE
                pp.PatientProfileID = @PatientProfileId 

            SELECT
                '330.EmpName' = ISNULL(e.Name , '') ,
                '331.EmpAddr1' = ISNULL(e.Address1 , '') ,
                '332.EmpAddr2' = ISNULL(e.Address2 , '') ,
                '333.EmpCity' = ISNULL(e.City , '') ,
                '334.EmpState' = ISNULL(e.State , '') ,
                '335.EmpZip' = ISNULL(e.Zip , '') ,
                '336.EmpCountry' = ISNULL(e.Country , '') ,
                '337.EmpPhone' = ISNULL(e.Phone1 , '') ,
                '338.EmpAllocation' = ISNULL(al.Name , '') ,
                '339.EmpFinancialClass' = ISNULL(fc.Description , '') ,
                '340.EmpCarrierName' = ISNULL(ic.Name , '') ,
                '341.EmpCarrierListname' = ISNULL(ic.Listname , '') ,
                '342.EmpCarrierAddress1' = ISNULL(ic.Address1 , '') ,
                '343.EmpCarrierAddress2' = ISNULL(ic.Address2 , '') ,
                '344.EmpCarrierCity' = ISNULL(ic.City , '') ,
                '345.EmpCarrierState' = ISNULL(ic.State , '') ,
                '346.EmpCarrierZip' = ISNULL(ic.Zip , '') ,
                '347.EmpCarrierCountry' = ISNULL(ic.Country , '') ,
                '350.EmpCarrierContact' = ISNULL(ic.Contact , '') ,
                '351.EmpCarrierPhone' = ISNULL(ic.Phone1 , '') ,
                '352.EmpCarrierType' = ISNULL(ct.Description , '') ,
                '353.EmpCarrierPolicyType' = ISNULL(pt.Description , '') ,
                '354.EmpCarrierGroupId' = ISNULL(ic.GroupId , '') ,
                '355.EmpCarrierGroupName' = ISNULL(ic.GroupName , '') ,
                '356.PatientEmail' = ISNULL(pp.EMailAddress , '') ,
                '360.PatientNotes' = ISNULL(CONVERT(VARCHAR(255) , pp.ProfileNotes) , '') ,
                '361.BillingNotes' = ISNULL(CONVERT(VARCHAR(255) , pp.BillingNotes) , '') ,
                '362.ApptNotes' = ISNULL(CONVERT(VARCHAR(255) , a.Notes) , '') ,
                '370.WCClaimNo' = ISNULL(c.ClaimNumber , '') ,
                '371.WCCID' = CASE WHEN ISNULL(c.WorkersComp , 0) > 0 THEN a.CasesId
                                   ELSE 0
                              END ,
                '372.WCDateOfInjury' = CASE WHEN ISNULL(c.WorkersComp , 0) > 0 THEN c.DateOfInjury
                                            ELSE NULL
                                       END ,
                '373.WCDescription' = CASE WHEN ISNULL(c.WorkersComp , 0) > 0 THEN c.Name
                                           ELSE ''
                                      END ,
                '374.WCStatus' = CASE WHEN ISNULL(c.WorkersComp , 0) > 0 THEN c.Closed
                                      ELSE 0
                                 END ,
                '375.LastApptDate' = @LastVisitDate ,
                '380.ReferringPhysicianFirst' = ISNULL(UPPER(df.First) , '') ,
                '381.ReferringPhysicianMiddle' = ISNULL(UPPER(df.Middle) , '') ,
                '382.ReferringPhysicianLast' = ISNULL(UPPER(df.Last) , '') ,
                '383.ReferringPhysicianSuffix' = ISNULL(UPPER(df.Suffix) , '')
            FROM
                Appointments a
                INNER JOIN PatientProfile pp ON pp.PatientProfileID = a.OwnerID
                LEFT JOIN Cases c ON a.CasesId = c.CasesId
                                     AND ISNULL(c.WorkersComp , 0) <> 0
                LEFT JOIN DoctorFacility df ON pp.RefDoctorID = df.DoctorFacilityID
                LEFT JOIN Employer e ON (
                                          c.EmployerId = e.EmployerId
                                          OR (
                                               c.EmployerId IS NULL
                                               AND pp.EmployerId = e.EmployerId
                                             )
                                        )
                LEFT JOIN InsuranceCarriers ic ON (
                                                    c.PrimaryInsuranceCarriersId = ic.InsuranceCarriersId
                                                    OR (
                                                         c.PrimaryInsuranceCarriersId IS NULL
                                                         AND e.InsuranceCarrierId = ic.InsuranceCarriersId
                                                       )
                                                  )
                LEFT JOIN MedLists ct ON ic.CarrierTypeMId = ct.MedListsId
                LEFT JOIN MedLists pt ON ic.PolicyTypeMId = pt.MedListsId
                LEFT JOIN MedLists fc ON (
                                           c.FinancialClassMId = fc.MedListsId
                                           OR (
                                                c.FinancialClassMId IS NULL
                                                AND e.FinancialClassMId = fc.MedListsId
                                              )
                                         )
                LEFT JOIN AllocationSet al ON (
                                                c.AllocationSetId = al.AllocationSetId
                                                OR (
                                                     c.AllocationSetId IS NULL
                                                     AND e.AllocationSetId = al.AllocationSetId
                                                   )
                                              )
            WHERE
                a.AppointmentsId = @AppointmentsId
      END
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

markros · Aug 13, 2009

I'm unable to read the procedure code, but if you want to link the subquery with the table you're selecting from, then

'999.LastPaymentDate' =
(
SELECT
MAX(b.Entry) AS PaymentDate
FROM
PaymentMethod pm
INNER JOIN Batch b ON pm.BatchId = b.BatchId
INNER JOIN patientprofile pp ON pm.PayerId = pp.PatientProfileId
INNER JOIN Guarantor gr ON pp.GuarantorId = gr.GuarantorId
WHERE pm.ParentID = X.ParentID
pm.PayerType = 'Patient'OR pm.PayerType = 'Guarantor'
AND pm.Amount <> 0
)

from myTable X

Also in 99% of cases using derived tables is faster than subquery.

I do know an exception, though.

bborissov · Aug 13, 2009

Your where clause is wrong. Mixed ANDs and ORs better be surrounded with brackets to get what you need:
Depending HOW you surround thes statements with brackets you will have different results:
1:

Code:

 WHERE (pm.PayerType = 'Patient' OR
       pm.PayerType = 'Guarantor') AND
       pm.Amount <> 0

2:

Code:

 WHERE pm.PayerType = 'Patient' OR 
       (pm.PayerType = 'Guarantor' AND
       pm.Amount <> 0)

And finally IF you need that PayerType is either 'Patient' either 'Guarantor' but THAT PayerType Ammount is different by zero:

Code:

 WHERE pm.PayerType IN ('Patient', 'Guarantor') AND
       pm.Amount <> 0

BTW that is equal to 1.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

bborissov · Aug 13, 2009

markros:
Your WHERE clause is also wrong

Missed one AND/OR and NO brackets

Code:

  WHERE pm.ParentID = X.ParentID [COLOR=red][b]AND/OR[/b][color]
    pm.PayerType = 'Patient'OR pm.PayerType = 'Guarantor'
    AND pm.Amount <> 0

BTW I am curious when the subquery in field list is faster than joined table?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

markros · Aug 13, 2009

I didn't read the WHERE close enough, I was just pointing to the problem. But you're right - I like the IN version much better.

See this

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/how-to-search-for-all-words-inclusive-wi

for the sample of a good subquery.

BTW, see also

http://forums.asp.net/p/1458288/3344453.aspx#3344453

bborissov · Aug 13, 2009

But these are NOT in field list.
All of them are derived tables

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

markros · Aug 13, 2009

Code:

SELECT x.Id,
       x.Searched
  FROM ( SELECT m.Id,
                m.Searched,
                [b](SELECT COUNT(*)
                   FROM @Keys k
                  WHERE ' '+ m.Searched+' ' like '% ' + k.Word + ' %'
                ) AS n[/b]
           FROM @myTable m
       ) AS x
 WHERE n=(SELECT COUNT(*) FROM @Keys)

Looks very much subquery to me.

bborissov · Aug 13, 2009

THAT was the one I missed

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

JBourne77 · Aug 13, 2009

Msg 107, Level 16, State 2, Procedure cusMPMSuperbillPardee, Line 573
The column prefix 'X' does not match with a table name or alias name used in the query.

Code:

'999.LastPaymentDate' = (
                                          SELECT
                                            MAX(b.Entry) AS PaymentDate
                                          FROM
                                            PaymentMethod pm
                                            INNER JOIN Batch b ON pm.BatchId = b.BatchId
                                            INNER JOIN patientprofile pp ON pm.PayerId = pp.PatientProfileId
                                            INNER JOIN Guarantor gr ON pp.GuarantorId = gr.GuarantorId
                                          WHERE
                                            pm.PayerId = X.PatientProfileId
                                            AND pm.PayerType = 'Patient'
                                            OR pm.PayerType = 'Guarantor'
                                            AND pm.Amount <> 0
                                        )

bborissov · Aug 13, 2009

I gave up!

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

markros · Aug 13, 2009

Me too

Code:

WHERE  pm.PayerId = ci.PatientProfileId
       AND pm.PayerType IN ('Patient', 'Guarantor')
        AND pm.Amount <> 0

Use ci or whatever name is in your main table which is used in FROM clause of your SQL Statement. I really can not figure out what it should be - the SP is too monstrous.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Sub-Query returns wrong value

JBourne77

IS-IT--Management

markros

Programmer

bborissov

Programmer

bborissov

Programmer

markros

Programmer

bborissov

Programmer

markros

Programmer

bborissov

Programmer

JBourne77

IS-IT--Management

bborissov

Programmer

markros

Programmer

Similar threads

Part and Inventory Search

Sponsor