SQL 2005 - Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Jeff S
Jeff S used Ask the Experts™
on
Please Note, the SQL is handled dynamically by the SQL server, therefore some items in my WHERE clause will look odd to you. Please disregard.

I am getting the following error. Any help is appreciated.

Msg 512, Level 16, State 1, Line 282
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

SET NOCOUNT ON
 
DECLARE @ResTypeID INT
 
SELECT
    @ResTypeID = medlistsID
FROM
    medlists
WHERE
    tablename = 'resourcetypes'
    AND description = 'Reporting'
 
DECLARE
        @startdate DATETIME ,
        @enddate DATETIME ,
        @groupby1 VARCHAR(20) ,
        @groupby2 VARCHAR(20) ,
        @groupby3 VARCHAR(20)
 
IF 1 = 1 
   BEGIN -- Date mode
         SET @startdate = ISNULL(NULL , '1/1/1900') 
         SET @enddate = DATEADD(DAY , 1 , ISNULL(NULL , '1/1/3000'))
   END
ELSE 
   BEGIN  --month mode
         SET @startdate = ISNULL(NULL , '1/1/1900') 
         SET @enddate = DATEADD(month , 1 , ISNULL(NULL , '1/1/3000'))
   END
 
SET @groupby1 = CONVERT(VARCHAR(20) , 'Company')
SET @groupby2 = CONVERT(VARCHAR(20) , 'None')
SET @groupby3 = CONVERT(VARCHAR(20) , 'None')
 
--load the charges
SELECT
    pv.ticketnumber ,
    pv.patientvisitID ,
    dr.listname AS ProviderName ,
    dr.doctorfacilityID AS DoctorID ,
    ISNULL(ref.Listname , 'No Referring Phys') AS ReferringDocName ,
    0 AS ResourceID ,
    fac.listname AS FacilityName ,
    comp.listname AS CompanyName ,
    p.cptcode ,
    p.code ,
    ISNULL(pvd.Code , '') AS Diag1 ,
    ISNULL(pvd1.Code , '') AS Diag2 ,
    ISNULL(pvd2.Code , '') AS Diag3 ,
    ISNULL(pvd3.Code , '') AS Diag4 ,
    ISNULL(pvd4.Code , '') AS Diag5 ,
    ISNULL(pvd5.Code , '') AS Diag6 ,
    ISNULL(pvd6.Code , '') AS Diag7 ,
    ISNULL(pvd7.Code , '') AS Diag8 ,
    ISNULL(mod1.code , '') + ISNULL(mod2.code , '') + ISNULL(mod3.code , '') + ISNULL(mod4.code , '') AS Modifier ,
    ISNULL(p.description , 'No Description') AS CPTDescription ,
    	--pp.last+', '+pp.first+' '+Isnull(pp.middle,'') as PatientName,
    ISNULL(dept.description , 'No Dept') AS Department ,
    pvp.totalfee AS Charges ,
    CAST('0.00' AS MONEY) AS Payments ,
    CAST('0.00' AS MONEY) AS Adjustments ,
    pvp.dateofentry AS DateOfEntry ,
    pvp.units AS Units ,
    pvp.patientvisitprocsID ,
    pv.Visit AS DateOfService ,
    ISNULL(fc.description , 'No Financial Class') AS FinancialClass ,
    ISNULL(ic.listname , 'No Insurance') AS CarrierName ,
    ISNULL(pi.InsuredId , '') AS InsuredId ,
    ISNULL(ig.name , 'No Ins Group') AS InsGroupName ,
    CASE @groupby1
      WHEN 'Carrier' THEN ISNULL(ic.listname , 'No Carrier')
      WHEN 'Company' THEN ISNULL(comp.listname , ' No Company')
      WHEN 'Department' THEN ISNULL(dept.description , 'No Dept')
      WHEN 'Facility' THEN fac.listname
      WHEN 'Financial Class' THEN ISNULL(fc.description , 'No Financial Class')
      WHEN 'Insurance Group' THEN ISNULL(ig.name , 'No Ins Group')
      WHEN 'Procedure' THEN ISNULL(p.Code , 'No Procedure')
      WHEN 'Provider' THEN dr.listname
      WHEN 'Referring Physician' THEN ISNULL(ref.listname , 'No Referring Doc')
      ELSE 'None'
    END AS Group1 ,
    CASE @groupby2
      WHEN 'Carrier' THEN ISNULL(ic.listname , 'No Carrier')
      WHEN 'Company' THEN ISNULL(comp.listname , ' No Company')
      WHEN 'Department' THEN ISNULL(dept.description , 'No Dept')
      WHEN 'Facility' THEN fac.listname
      WHEN 'Financial Class' THEN ISNULL(fc.description , 'No Financial Class')
      WHEN 'Insurance Group' THEN ISNULL(ig.name , 'No Ins Group')
      WHEN 'Procedure' THEN ISNULL(p.Code , 'No Procedure')
      WHEN 'Provider' THEN dr.listname
      WHEN 'Referring Physician' THEN ISNULL(ref.listname , 'No Referring Doc')
      ELSE fac.listname
    END AS Group2 ,
    CASE @groupby3
      WHEN 'Carrier' THEN ISNULL(ic.listname , 'No Carrier')
      WHEN 'Company' THEN ISNULL(comp.listname , ' No Company')
      WHEN 'Department' THEN ISNULL(dept.description , 'No Dept')
      WHEN 'Facility' THEN fac.listname
      WHEN 'Financial Class' THEN ISNULL(fc.description , 'No Financial Class')
      WHEN 'Insurance Group' THEN ISNULL(ig.name , 'No Ins Group')
      WHEN 'Procedure' THEN ISNULL(p.Code , 'No Procedure')
      WHEN 'Provider' THEN dr.listname
      WHEN 'Referring Physician' THEN ISNULL(ref.listname , 'No Referring Doc')
      ELSE fac.listname
    END AS Group3
INTO
    #tbl_temp
FROM
    patientvisit pv
    INNER JOIN patientprofile pp ON pp.patientprofileID = pv.patientProfileID
	LEFT JOIN PatientInsurance pi ON pp.PatientProfileID = pi.PatientProfileID
                                                  AND (
                                                        pi.Inactive IS NULL
                                                        OR pi.Inactive = 0
                                                      )
                                                  AND pi.OrderForClaims = 1
    INNER JOIN patientvisitprocs pvp ON pv.patientvisitID = pvp.patientvisitID
    INNER JOIN batch b ON pvp.batchID = b.batchID
    INNER JOIN patientvisitprocsagg pvpa ON pvp.patientvisitprocsID = pvpa.patientvisitprocsID
    INNER JOIN procedures p ON pvp.proceduresID = p.proceduresID
    LEFT JOIN insurancecarriers ic ON pv.primaryinsurancecarriersID = ic.insurancecarriersID
    LEFT JOIN insurancegroup ig ON ic.insurancegroupID = ig.insurancegroupID
    LEFT JOIN (
                SELECT * FROM medlists WHERE tablename = 'department'
              ) dept ON p.departmentMID = dept.medlistsID
    INNER JOIN (
                 SELECT * FROM doctorfacility WHERE type = 1 OR type = 7
               ) dr ON pv.doctorID = dr.doctorfacilityID
    INNER JOIN (
                 SELECT * FROM doctorfacility WHERE type = 2
               ) fac ON pv.facilityID = fac.doctorfacilityID
    INNER JOIN (
                 SELECT * FROM doctorfacility WHERE type = 5
               ) comp ON pv.companyID = comp.doctorfacilityID
    LEFT JOIN (
                SELECT * FROM doctorfacility WHERE type = 3
              ) ref ON pv.referringdoctorID = ref.doctorfacilityID
    LEFT JOIN (
                SELECT * FROM medlists WHERE tablename = 'FinancialClass'
              ) fc ON pv.financialclassMID = fc.medlistsID
    LEFT JOIN (
                SELECT * FROM medlists WHERE tablename = 'Modifiers'
              ) mod1 ON pvp.Modifier1MID = mod1.medlistsID
    LEFT JOIN (
                SELECT * FROM medlists WHERE tablename = 'Modifiers'
              ) mod2 ON pvp.Modifier2MID = mod2.medlistsID
    LEFT JOIN (
                SELECT * FROM medlists WHERE tablename = 'Modifiers'
              ) mod3 ON pvp.Modifier3MID = mod3.medlistsID
    LEFT JOIN (
                SELECT * FROM medlists WHERE tablename = 'Modifiers'
              ) mod4 ON pvp.Modifier4MID = mod4.medlistsID
    LEFT OUTER JOIN PatientVisitDiags pvd ON pv.PatientVisitId = pvd.PatientVisitId
                                             AND pvp.PatientVisitDiags1 = pvd.ListOrder
    LEFT OUTER JOIN PatientVisitDiags pvd1 ON pv.PatientVisitId = pvd1.PatientVisitId
                                              AND pvp.PatientVisitDiags2 = pvd1.ListOrder
    LEFT OUTER JOIN PatientVisitDiags pvd2 ON pv.PatientVisitId = pvd2.PatientVisitId
                                              AND pvp.PatientVisitDiags3 = pvd2.ListOrder
    LEFT OUTER JOIN PatientVisitDiags pvd3 ON pv.PatientVisitId = pvd3.PatientVisitId
                                              AND pvp.PatientVisitDiags4 = pvd3.ListOrder
    LEFT OUTER JOIN PatientVisitDiags pvd4 ON pv.PatientVisitId = pvd4.PatientVisitId
                                              AND pvp.PatientVisitDiags5 = pvd4.ListOrder
    LEFT OUTER JOIN PatientVisitDiags pvd5 ON pv.PatientVisitId = pvd5.PatientVisitId
                                              AND pvp.PatientVisitDiags6 = pvd5.ListOrder
    LEFT OUTER JOIN PatientVisitDiags pvd6 ON pv.PatientVisitId = pvd6.PatientVisitId
                                              AND pvp.PatientVisitDiags7 = pvd6.ListOrder
    LEFT OUTER JOIN PatientVisitDiags pvd7 ON pv.PatientVisitId = pvd7.PatientVisitId
                                              AND pvp.PatientVisitDiags8 = pvd7.ListOrder
WHERE
    b.Entry >= @startdate
    AND b.Entry < @enddate
    AND  --Filter on Carrier
    (
      (
        NULL IS NOT NULL
        AND pv.primaryinsurancecarriersID IN ( NULL )
      )
      OR ( NULL IS NULL )
    )
    AND  --Filter on company
    (
      (
        NULL IS NOT NULL
        AND pv.CompanyID IN ( NULL )
      )
      OR ( NULL IS NULL )
    )
    AND  --Filter on Department
    (
      (
        NULL IS NOT NULL
        AND p.DepartmentMId IN ( NULL )
      )
      OR ( NULL IS NULL )
    )
    AND  --Filter on facility
    (
      (
        NULL IS NOT NULL
        AND pv.FacilityID IN ( NULL )
      )
      OR ( NULL IS NULL )
    )
    AND  --Filter on Financial Class
    (
      (
        NULL IS NOT NULL
        AND pv.FinancialClassMID IN ( NULL )
      )
      OR ( NULL IS NULL )
    )
    AND --Filter on Ins Group
    (
      (
        NULL IS NOT NULL
        AND ic.insuranceGroupID IN ( NULL )
      )
      OR ( NULL IS NULL )
    )
    AND  --Filter on Procedure
    (
      (
        NULL IS NOT NULL
        AND pvp.ProceduresId IN ( NULL )
      )
      OR ( NULL IS NULL )
    )
    AND  --Filter on Provider
    (
      (
        NULL IS NOT NULL
        AND pv.DoctorID IN ( NULL )
      )
      OR ( NULL IS NULL )
    )
    AND  --Filter on Referring Doc
    (
      (
        NULL IS NOT NULL
        AND pv.ReferringDoctorID IN ( NULL )
      )
      OR ( NULL IS NULL )
    )
    AND  --Filter on Diagnosis
    (
      (
        NULL IS NOT NULL
        AND pvd.DiagnosisId IN ( NULL )
      )
      OR (
           NULL IS NOT NULL
           AND pvd1.DiagnosisId IN ( NULL )
         )
      OR (
           NULL IS NOT NULL
           AND pvd2.DiagnosisId IN ( NULL )
         )
      OR (
           NULL IS NOT NULL
           AND pvd3.DiagnosisId IN ( NULL )
         )
      OR (
           NULL IS NOT NULL
           AND pvd4.DiagnosisId IN ( NULL )
         )
      OR (
           NULL IS NOT NULL
           AND pvd5.DiagnosisId IN ( NULL )
         )
      OR (
           NULL IS NOT NULL
           AND pvd6.DiagnosisId IN ( NULL )
         )
      OR (
           NULL IS NOT NULL
           AND pvd7.DiagnosisId IN ( NULL )
         )
      OR ( NULL IS NULL )
    )	
 
--Insert rows with payments and adjustments
INSERT INTO
    #tbl_temp
    (
      ticketnumber ,
      patientvisitID ,
      providername ,
      doctorID ,
      referringdocName ,
      resourceID ,
      facilityname ,
      companyname ,
      CPTCode ,
      code ,
      Diag1 ,
      Diag2 ,
      Diag3 ,
      Diag4 ,
      Diag5 ,
      Diag6 ,
      Diag7 ,
      Diag8 ,
      modifier ,
      CPTDescription ,
      department ,
      charges ,
      payments ,
      adjustments ,
      dateofentry ,
      units ,
      patientvisitprocsID ,
      dateofservice ,
      FinancialClass ,
      CarrierName ,
      InsuredId ,
      InsGroupName ,
      Group1 ,
      Group2 ,
      Group3
	
    )
    SELECT
        pv.ticketnumber ,
        pv.patientvisitID ,
        dr.listname AS ProviderName ,
        pv.doctorID AS DoctorID ,
        ISNULL(ref.Listname , 'No Referring Phys') AS ReferringDocName ,
        0 AS ResourceID ,
        fac.listname AS FacilityName ,
        comp.listname AS CompanyName ,
        ISNULL(p.cptcode , 'Deposit') AS CPTCode ,
        CASE tr.type
          WHEN 'P' THEN ISNULL(p.code , 'Deposit')
          ELSE ISNULL(p.code , 'None Adj')
        END AS Code ,
        ISNULL(pvd.Code , '') AS Diag1 ,
        ISNULL(pvd1.Code , '') AS Diag2 ,
        ISNULL(pvd2.Code , '') AS Diag3 ,
        ISNULL(pvd3.Code , '') AS Diag4 ,
        ISNULL(pvd4.Code , '') AS Diag5 ,
        ISNULL(pvd5.Code , '') AS Diag6 ,
        ISNULL(pvd6.Code , '') AS Diag7 ,
        ISNULL(pvd7.Code , '') AS Diag8 ,
        ISNULL(mod1.code , '') + ISNULL(mod2.code , '') + ISNULL(mod3.code , '') + ISNULL(mod4.code , '') AS Modifier ,
        ISNULL(p.description , 'No Description') AS CPTDescription ,
    	--pp.last+', '+pp.first+' '+Isnull(pp.middle,'') as PatientName,
        ISNULL(dept.description , 'No Dept') AS Department ,
        0 AS Charges ,
        CASE tr.type
          WHEN 'P' THEN td.amount
          ELSE 0
        END AS Payments ,
        CASE tr.type
          WHEN 'P' THEN 0
          ELSE td.amount
        END AS Adjustments ,
        b.entry AS DateOfEntry ,
        0 AS Units ,
        ISNULL(pvp.patientvisitprocsID , 0) AS patientvisitprocsID ,
        pv.Visit AS DateOfService ,
        ISNULL(fc.description , 'No Financial Class') AS FinancialClass ,
        ISNULL(ic.listname , 'No Insurance') AS CarrierName ,
        (
		SELECT 
			ISNULL(pi.InsuredId , '')
        FROM	
			PatientVisit pv2
			LEFT JOIN PatientVisitInsurance pvi ON pv2.PatientVisitId = pvi.PatientVisitId
			LEFT JOIN PatientInsurance pi ON pv2.CurrentPICarrierId = pi.PatientInsuranceId AND pvi.PatientInsuranceId = pi.PatientInsuranceId
			LEFT JOIN InsuranceCarriers ic ON pv2.CurrentInsuranceCarriersId = ic.InsuranceCarriersId AND pi.InsuranceCarriersId = ic.InsuranceCarriersId
		WHERE
			pv2.PatientVisitId = pv.PatientVisitId
        )
        AS InsuredId ,
        ISNULL(ig.name , 'No Ins Group') AS InsGroupName ,
        CASE @groupby1
          WHEN 'Carrier' THEN ISNULL(ic.listname , 'No Carrier')
          WHEN 'Company' THEN ISNULL(comp.listname , ' No Company')
          WHEN 'Department' THEN ISNULL(dept.description , 'No Dept')
          WHEN 'Facility' THEN fac.listname
          WHEN 'Financial Class' THEN ISNULL(fc.description , 'No Financial Class')
          WHEN 'Insurance Group' THEN ISNULL(ig.name , 'No Ins Group')
          WHEN 'Procedure' THEN ISNULL(p.Code , 'Deposit')
          WHEN 'Provider' THEN dr.listname
          WHEN 'Referring Physician' THEN ISNULL(ref.listname , 'No Referring Doc')
          ELSE fac.listname
        END AS Group1 ,
        CASE @groupby2
          WHEN 'Carrier' THEN ISNULL(ic.listname , 'No Carrier')
          WHEN 'Company' THEN ISNULL(comp.listname , ' No Company')
          WHEN 'Department' THEN ISNULL(dept.description , 'No Dept')
          WHEN 'Facility' THEN fac.listname
          WHEN 'Financial Class' THEN ISNULL(fc.description , 'No Financial Class')
          WHEN 'Insurance Group' THEN ISNULL(ig.name , 'No Ins Group')
          WHEN 'Procedure' THEN ISNULL(p.Code , 'No Procedure')
          WHEN 'Provider' THEN dr.listname
          WHEN 'Referring Physician' THEN ISNULL(ref.listname , 'No Referring Doc')
          ELSE fac.listname
        END AS Group2 ,
        CASE @groupby3
          WHEN 'Carrier' THEN ISNULL(ic.listname , 'No Carrier')
          WHEN 'Company' THEN ISNULL(comp.listname , ' No Company')
          WHEN 'Department' THEN ISNULL(dept.description , 'No Dept')
          WHEN 'Facility' THEN fac.listname
          WHEN 'Financial Class' THEN ISNULL(fc.description , 'No Financial Class')
          WHEN 'Insurance Group' THEN ISNULL(ig.name , 'No Ins Group')
          WHEN 'Procedure' THEN ISNULL(p.Code , 'No Procedure')
          WHEN 'Provider' THEN dr.listname
          WHEN 'Referring Physician' THEN ISNULL(ref.listname , 'No Referring Doc')
          ELSE fac.listname
        END AS Group3
       FROM
        transactiondistributions td
        INNER JOIN transactions tr ON td.transactionsID = tr.transactionsID
        INNER JOIN visittransactions vt ON tr.visittransactionsID = vt.visittransactionsID
        INNER JOIN paymentmethod pm ON vt.paymentmethodID = pm.paymentmethodID
        LEFT JOIN insurancecarriers ic ON vt.insurancecarriersID = ic.insurancecarriersID
        LEFT JOIN insurancegroup ig ON ic.insurancegroupID = ig.insurancegroupID 
        INNER JOIN patientvisit pv ON pv.patientvisitID = vt.patientvisitID                     
        LEFT JOIN patientvisitprocs pvp ON td.patientvisitprocsID = pvp.patientvisitprocsID
        LEFT JOIN procedures p ON pvp.proceduresID = p.proceduresID
        INNER JOIN batch b ON pm.batchID = b.batchID
        LEFT JOIN (
                    SELECT * FROM medlists WHERE tablename = 'department'
                  ) dept ON p.departmentMID = dept.medlistsID
        LEFT JOIN (
                    SELECT * FROM doctorfacility WHERE type = 2
                  ) fac ON pv.facilityID = fac.doctorfacilityID
        LEFT JOIN (
                    SELECT * FROM doctorfacility WHERE type = 5
                  ) comp ON pv.companyID = comp.doctorfacilityID
        LEFT JOIN (
                    SELECT * FROM doctorfacility WHERE type = 1 OR type = 7
                  ) dr ON pv.doctorID = dr.doctorfacilityID
        LEFT JOIN (
                    SELECT * FROM doctorfacility WHERE type = 3
                  ) ref ON pv.referringdoctorID = ref.doctorfacilityID
        LEFT JOIN (
                    SELECT * FROM medlists WHERE tablename = 'FinancialClass'
                  ) fc ON pv.financialclassMID = fc.medlistsID
        LEFT JOIN (
                    SELECT * FROM medlists WHERE tablename = 'Modifiers'
                  ) mod1 ON pvp.Modifier1MID = mod1.medlistsID
        LEFT JOIN (
                    SELECT * FROM medlists WHERE tablename = 'Modifiers'
                  ) mod2 ON pvp.Modifier2MID = mod2.medlistsID
        LEFT JOIN (
                    SELECT * FROM medlists WHERE tablename = 'Modifiers'
                  ) mod3 ON pvp.Modifier3MID = mod3.medlistsID
        LEFT JOIN (
                    SELECT * FROM medlists WHERE tablename = 'Modifiers'
                  ) mod4 ON pvp.Modifier4MID = mod4.medlistsID
        LEFT OUTER JOIN PatientVisitDiags pvd ON pv.PatientVisitId = pvd.PatientVisitId
                                                 AND pvp.PatientVisitDiags1 = pvd.ListOrder
        LEFT OUTER JOIN PatientVisitDiags pvd1 ON pv.PatientVisitId = pvd1.PatientVisitId
                                                  AND pvp.PatientVisitDiags2 = pvd1.ListOrder
        LEFT OUTER JOIN PatientVisitDiags pvd2 ON pv.PatientVisitId = pvd2.PatientVisitId
                                                  AND pvp.PatientVisitDiags3 = pvd2.ListOrder
        LEFT OUTER JOIN PatientVisitDiags pvd3 ON pv.PatientVisitId = pvd3.PatientVisitId
                                                  AND pvp.PatientVisitDiags4 = pvd3.ListOrder
        LEFT OUTER JOIN PatientVisitDiags pvd4 ON pv.PatientVisitId = pvd4.PatientVisitId
                                                  AND pvp.PatientVisitDiags5 = pvd4.ListOrder
        LEFT OUTER JOIN PatientVisitDiags pvd5 ON pv.PatientVisitId = pvd5.PatientVisitId
                                                  AND pvp.PatientVisitDiags6 = pvd5.ListOrder
        LEFT OUTER JOIN PatientVisitDiags pvd6 ON pv.PatientVisitId = pvd6.PatientVisitId
                                                  AND pvp.PatientVisitDiags7 = pvd6.ListOrder
        LEFT OUTER JOIN PatientVisitDiags pvd7 ON pv.PatientVisitId = pvd7.PatientVisitId
                                                  AND pvp.PatientVisitDiags8 = pvd7.ListOrder
    WHERE
        (
          tr.type = 'P'
          OR tr.type = 'A'
        )
--    and tr.action = 'P'
--    and pv.ticketnumber = @ticketnum
        AND b.entry >= @startdate
        AND b.entry < @enddate
        AND td.amount <> 0
        AND  --Filter on Carrier
        (
          (
            NULL IS NOT NULL
            AND pv.primaryinsurancecarriersID IN ( NULL )
          )
          OR ( NULL IS NULL )
        )
        AND  --Filter on company
        (
          (
            NULL IS NOT NULL
            AND pv.CompanyID IN ( NULL )
          )
          OR ( NULL IS NULL )
        )
        AND  --Filter on Department
        (
          (
            NULL IS NOT NULL
            AND p.DepartmentMId IN ( NULL )
          )
          OR ( NULL IS NULL )
        )
        AND  --Filter on facility
        (
          (
            NULL IS NOT NULL
            AND pv.FacilityID IN ( NULL )
          )
          OR ( NULL IS NULL )
        )
        AND  --Filter on Financial Class
        (
          (
            NULL IS NOT NULL
            AND pv.FinancialClassMID IN ( NULL )
          )
          OR ( NULL IS NULL )
        )
        AND --Filter on Ins Group
        (
          (
            NULL IS NOT NULL
            AND ic.insuranceGroupID IN ( NULL )
          )
          OR ( NULL IS NULL )
        )
        AND  --Filter on Procedure
        (
          (
            NULL IS NOT NULL
            AND pvp.ProceduresId IN ( NULL )
          )
          OR ( NULL IS NULL )
        )
        AND  --Filter on Provider
        (
          (
            NULL IS NOT NULL
            AND pv.DoctorID IN ( NULL )
          )
          OR ( NULL IS NULL )
        )
        AND  --Filter on Referring Doc
        (
          (
            NULL IS NOT NULL
            AND pv.ReferringDoctorID IN ( NULL )
          )
          OR ( NULL IS NULL )
        )
        AND  --Filter on Diagnosis
        (
          (
            NULL IS NOT NULL
            AND pvd.DiagnosisId IN ( NULL )
          )
          OR (
               NULL IS NOT NULL
               AND pvd1.DiagnosisId IN ( NULL )
             )
          OR (
               NULL IS NOT NULL
               AND pvd2.DiagnosisId IN ( NULL )
             )
          OR (
               NULL IS NOT NULL
               AND pvd3.DiagnosisId IN ( NULL )
             )
          OR (
               NULL IS NOT NULL
               AND pvd4.DiagnosisId IN ( NULL )
             )
          OR (
               NULL IS NOT NULL
               AND pvd5.DiagnosisId IN ( NULL )
             )
          OR (
               NULL IS NOT NULL
               AND pvd6.DiagnosisId IN ( NULL )
             )
          OR (
               NULL IS NOT NULL
               AND pvd7.DiagnosisId IN ( NULL )
             )
          OR ( NULL IS NULL )
        )	
 
--Update the 'reporting' resource types
UPDATE
    #tbl_temp
SET 
    resourceID = res.doctorfacilityID
FROM
    #tbl_temp t
    LEFT JOIN patientvisitresource pvr2 ON t.patientvisitID = pvr2.patientvisitID
    LEFT JOIN doctorfacility res ON pvr2.resourceID = res.doctorfacilityID
    LEFT JOIN ResourceTypeAssignments rta ON res.doctorfacilityID = rta.resourceID
WHERE
    rta.resourcetypeID = @ResTypeID
 
--Update the non-reporting resources
UPDATE
    #tbl_temp
SET 
    resourceID = ISNULL(res.doctorfacilityID , 0)
FROM
    #tbl_temp t
    LEFT JOIN patientvisitresource pvr2 ON t.patientvisitID = pvr2.patientvisitID
    LEFT JOIN doctorfacility res ON pvr2.resourceID = res.doctorfacilityID
WHERE
    t.ResourceID = 0
 
--Update everything that is left with the doctor
--update #tbl_temp
--set resourceID = t.doctorID
--from #tbl_temp t 
--where t.ResourceID = 0
 
 
SELECT
    t.Ticketnumber ,
    t.ProviderName ,
    t.ReferringDocName ,
    ISNULL(res.ListName , 'No Resource') AS ResourceName ,
    t.FacilityName ,
    t.CompanyName ,
    t.CPTCode ,
    t.Code ,
    t.Diag1 ,
    t.Diag2 ,
    t.Diag3 ,
    t.Diag4 ,
    t.Diag5 ,
    t.Diag6 ,
    t.Diag7 ,
    t.Diag8 ,
    t.Modifier ,
    t.Units ,
    t.Department ,
    t.Charges ,
    t.Payments ,
    t.Adjustments ,
    t.DateOfEntry ,
    t.DateOfService ,
    t.FinancialClass ,
    t.Carriername ,
    t.InsuredId ,
    t.InsGroupName AS InsGroupName ,
    t.CPTDescription ,
    t.ticketnumber + '_' + t.code + '_' + t.modifier AS TicketCode ,
    CASE @groupby1
      WHEN 'Resource' THEN ISNULL(res.listname , 'No Resource')
      ELSE t.Group1
    END AS Group1 ,
    CASE @groupby2
      WHEN 'Resource' THEN ISNULL(res.listname , 'No Resource')
      ELSE t.Group2
    END AS Group2 ,
    CASE @groupby3
      WHEN 'Resource' THEN ISNULL(res.listname , 'No Resource')
      ELSE t.Group3
    END AS Group3
 
--select sum(t.Charges), sum(t.Payments), sum(t.adjustments)
 
--into cus_ProcCrossRef
 
--drop table cus_ProcCrossRef
FROM
    #tbl_temp t
    LEFT JOIN doctorfacility res ON t.resourceID = res.doctorfacilityID
WHERE
    --Filter on resource
    (
      (
        NULL IS NOT NULL
        AND t.ResourceID IN ( NULL )
      )
      OR ( NULL IS NULL )
    )
 
 
--order by ticketnumber desc
 
DROP TABLE #tbl_temp

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Database Consultant
Top Expert 2009
Commented:
SET NOCOUNT ON
 
DECLARE @ResTypeID INT
 
SELECT
    @ResTypeID = medlistsID
FROM
    medlists
WHERE
    tablename = 'resourcetypes'
    AND description = 'Reporting'
 
DECLARE
        @startdate DATETIME ,
        @enddate DATETIME ,
        @groupby1 VARCHAR(20) ,
        @groupby2 VARCHAR(20) ,
        @groupby3 VARCHAR(20)
 
IF 1 = 1
   BEGIN -- Date mode
         SET @startdate = ISNULL(NULL , '1/1/1900')
         SET @enddate = DATEADD(DAY , 1 , ISNULL(NULL , '1/1/3000'))
   END
ELSE
   BEGIN  --month mode
         SET @startdate = ISNULL(NULL , '1/1/1900')
         SET @enddate = DATEADD(month , 1 , ISNULL(NULL , '1/1/3000'))
   END
 
SET @groupby1 = CONVERT(VARCHAR(20) , 'Company')
SET @groupby2 = CONVERT(VARCHAR(20) , 'None')
SET @groupby3 = CONVERT(VARCHAR(20) , 'None')
 
--load the charges
SELECT
    pv.ticketnumber ,
    pv.patientvisitID ,
    dr.listname AS ProviderName ,
    dr.doctorfacilityID AS DoctorID ,
    ISNULL(ref.Listname , 'No Referring Phys') AS ReferringDocName ,
    0 AS ResourceID ,
    fac.listname AS FacilityName ,
    comp.listname AS CompanyName ,
    p.cptcode ,
    p.code ,
    ISNULL(pvd.Code , '') AS Diag1 ,
    ISNULL(pvd1.Code , '') AS Diag2 ,
    ISNULL(pvd2.Code , '') AS Diag3 ,
    ISNULL(pvd3.Code , '') AS Diag4 ,
    ISNULL(pvd4.Code , '') AS Diag5 ,
    ISNULL(pvd5.Code , '') AS Diag6 ,
    ISNULL(pvd6.Code , '') AS Diag7 ,
    ISNULL(pvd7.Code , '') AS Diag8 ,
    ISNULL(mod1.code , '') + ISNULL(mod2.code , '') + ISNULL(mod3.code , '') + ISNULL(mod4.code , '') AS Modifier ,
    ISNULL(p.description , 'No Description') AS CPTDescription ,
          --pp.last+', '+pp.first+' '+Isnull(pp.middle,'') as PatientName,
    ISNULL(dept.description , 'No Dept') AS Department ,
    pvp.totalfee AS Charges ,
    CAST('0.00' AS MONEY) AS Payments ,
    CAST('0.00' AS MONEY) AS Adjustments ,
    pvp.dateofentry AS DateOfEntry ,
    pvp.units AS Units ,
    pvp.patientvisitprocsID ,
    pv.Visit AS DateOfService ,
    ISNULL(fc.description , 'No Financial Class') AS FinancialClass ,
    ISNULL(ic.listname , 'No Insurance') AS CarrierName ,
    ISNULL(pi.InsuredId , '') AS InsuredId ,
    ISNULL(ig.name , 'No Ins Group') AS InsGroupName ,
    CASE @groupby1
      WHEN 'Carrier' THEN ISNULL(ic.listname , 'No Carrier')
      WHEN 'Company' THEN ISNULL(comp.listname , ' No Company')
      WHEN 'Department' THEN ISNULL(dept.description , 'No Dept')
      WHEN 'Facility' THEN fac.listname
      WHEN 'Financial Class' THEN ISNULL(fc.description , 'No Financial Class')
      WHEN 'Insurance Group' THEN ISNULL(ig.name , 'No Ins Group')
      WHEN 'Procedure' THEN ISNULL(p.Code , 'No Procedure')
      WHEN 'Provider' THEN dr.listname
      WHEN 'Referring Physician' THEN ISNULL(ref.listname , 'No Referring Doc')
      ELSE 'None'
    END AS Group1 ,
    CASE @groupby2
      WHEN 'Carrier' THEN ISNULL(ic.listname , 'No Carrier')
      WHEN 'Company' THEN ISNULL(comp.listname , ' No Company')
      WHEN 'Department' THEN ISNULL(dept.description , 'No Dept')
      WHEN 'Facility' THEN fac.listname
      WHEN 'Financial Class' THEN ISNULL(fc.description , 'No Financial Class')
      WHEN 'Insurance Group' THEN ISNULL(ig.name , 'No Ins Group')
      WHEN 'Procedure' THEN ISNULL(p.Code , 'No Procedure')
      WHEN 'Provider' THEN dr.listname
      WHEN 'Referring Physician' THEN ISNULL(ref.listname , 'No Referring Doc')
      ELSE fac.listname
    END AS Group2 ,
    CASE @groupby3
      WHEN 'Carrier' THEN ISNULL(ic.listname , 'No Carrier')
      WHEN 'Company' THEN ISNULL(comp.listname , ' No Company')
      WHEN 'Department' THEN ISNULL(dept.description , 'No Dept')
      WHEN 'Facility' THEN fac.listname
      WHEN 'Financial Class' THEN ISNULL(fc.description , 'No Financial Class')
      WHEN 'Insurance Group' THEN ISNULL(ig.name , 'No Ins Group')
      WHEN 'Procedure' THEN ISNULL(p.Code , 'No Procedure')
      WHEN 'Provider' THEN dr.listname
      WHEN 'Referring Physician' THEN ISNULL(ref.listname , 'No Referring Doc')
      ELSE fac.listname
    END AS Group3
INTO
    #tbl_temp
FROM
    patientvisit pv
    INNER JOIN patientprofile pp ON pp.patientprofileID = pv.patientProfileID
      LEFT JOIN PatientInsurance pi ON pp.PatientProfileID = pi.PatientProfileID
                                                  AND (
                                                        pi.Inactive IS NULL
                                                        OR pi.Inactive = 0
                                                      )
                                                  AND pi.OrderForClaims = 1
    INNER JOIN patientvisitprocs pvp ON pv.patientvisitID = pvp.patientvisitID
    INNER JOIN batch b ON pvp.batchID = b.batchID
    INNER JOIN patientvisitprocsagg pvpa ON pvp.patientvisitprocsID = pvpa.patientvisitprocsID
    INNER JOIN procedures p ON pvp.proceduresID = p.proceduresID
    LEFT JOIN insurancecarriers ic ON pv.primaryinsurancecarriersID = ic.insurancecarriersID
    LEFT JOIN insurancegroup ig ON ic.insurancegroupID = ig.insurancegroupID
    LEFT JOIN (
                SELECT * FROM medlists WHERE tablename = 'department'
              ) dept ON p.departmentMID = dept.medlistsID
    INNER JOIN (
                 SELECT * FROM doctorfacility WHERE type = 1 OR type = 7
               ) dr ON pv.doctorID = dr.doctorfacilityID
    INNER JOIN (
                 SELECT * FROM doctorfacility WHERE type = 2
               ) fac ON pv.facilityID = fac.doctorfacilityID
    INNER JOIN (
                 SELECT * FROM doctorfacility WHERE type = 5
               ) comp ON pv.companyID = comp.doctorfacilityID
    LEFT JOIN (
                SELECT * FROM doctorfacility WHERE type = 3
              ) ref ON pv.referringdoctorID = ref.doctorfacilityID
    LEFT JOIN (
                SELECT * FROM medlists WHERE tablename = 'FinancialClass'
              ) fc ON pv.financialclassMID = fc.medlistsID
    LEFT JOIN (
                SELECT * FROM medlists WHERE tablename = 'Modifiers'
              ) mod1 ON pvp.Modifier1MID = mod1.medlistsID
    LEFT JOIN (
                SELECT * FROM medlists WHERE tablename = 'Modifiers'
              ) mod2 ON pvp.Modifier2MID = mod2.medlistsID
    LEFT JOIN (
                SELECT * FROM medlists WHERE tablename = 'Modifiers'
              ) mod3 ON pvp.Modifier3MID = mod3.medlistsID
    LEFT JOIN (
                SELECT * FROM medlists WHERE tablename = 'Modifiers'
              ) mod4 ON pvp.Modifier4MID = mod4.medlistsID
    LEFT OUTER JOIN PatientVisitDiags pvd ON pv.PatientVisitId = pvd.PatientVisitId
                                             AND pvp.PatientVisitDiags1 = pvd.ListOrder
    LEFT OUTER JOIN PatientVisitDiags pvd1 ON pv.PatientVisitId = pvd1.PatientVisitId
                                              AND pvp.PatientVisitDiags2 = pvd1.ListOrder
    LEFT OUTER JOIN PatientVisitDiags pvd2 ON pv.PatientVisitId = pvd2.PatientVisitId
                                              AND pvp.PatientVisitDiags3 = pvd2.ListOrder
    LEFT OUTER JOIN PatientVisitDiags pvd3 ON pv.PatientVisitId = pvd3.PatientVisitId
                                              AND pvp.PatientVisitDiags4 = pvd3.ListOrder
    LEFT OUTER JOIN PatientVisitDiags pvd4 ON pv.PatientVisitId = pvd4.PatientVisitId
                                              AND pvp.PatientVisitDiags5 = pvd4.ListOrder
    LEFT OUTER JOIN PatientVisitDiags pvd5 ON pv.PatientVisitId = pvd5.PatientVisitId
                                              AND pvp.PatientVisitDiags6 = pvd5.ListOrder
    LEFT OUTER JOIN PatientVisitDiags pvd6 ON pv.PatientVisitId = pvd6.PatientVisitId
                                              AND pvp.PatientVisitDiags7 = pvd6.ListOrder
    LEFT OUTER JOIN PatientVisitDiags pvd7 ON pv.PatientVisitId = pvd7.PatientVisitId
                                              AND pvp.PatientVisitDiags8 = pvd7.ListOrder
WHERE
    b.Entry >= @startdate
    AND b.Entry < @enddate
    AND  --Filter on Carrier
    (
      (
        NULL IS NOT NULL
        AND pv.primaryinsurancecarriersID IN ( NULL )
      )
      OR ( NULL IS NULL )
    )
    AND  --Filter on company
    (
      (
        NULL IS NOT NULL
        AND pv.CompanyID IN ( NULL )
      )
      OR ( NULL IS NULL )
    )
    AND  --Filter on Department
    (
      (
        NULL IS NOT NULL
        AND p.DepartmentMId IN ( NULL )
      )
      OR ( NULL IS NULL )
    )
    AND  --Filter on facility
    (
      (
        NULL IS NOT NULL
        AND pv.FacilityID IN ( NULL )
      )
      OR ( NULL IS NULL )
    )
    AND  --Filter on Financial Class
    (
      (
        NULL IS NOT NULL
        AND pv.FinancialClassMID IN ( NULL )
      )
      OR ( NULL IS NULL )
    )
    AND --Filter on Ins Group
    (
      (
        NULL IS NOT NULL
        AND ic.insuranceGroupID IN ( NULL )
      )
      OR ( NULL IS NULL )
    )
    AND  --Filter on Procedure
    (
      (
        NULL IS NOT NULL
        AND pvp.ProceduresId IN ( NULL )
      )
      OR ( NULL IS NULL )
    )
    AND  --Filter on Provider
    (
      (
        NULL IS NOT NULL
        AND pv.DoctorID IN ( NULL )
      )
      OR ( NULL IS NULL )
    )
    AND  --Filter on Referring Doc
    (
      (
        NULL IS NOT NULL
        AND pv.ReferringDoctorID IN ( NULL )
      )
      OR ( NULL IS NULL )
    )
    AND  --Filter on Diagnosis
    (
      (
        NULL IS NOT NULL
        AND pvd.DiagnosisId IN ( NULL )
      )
      OR (
           NULL IS NOT NULL
           AND pvd1.DiagnosisId IN ( NULL )
         )
      OR (
           NULL IS NOT NULL
           AND pvd2.DiagnosisId IN ( NULL )
         )
      OR (
           NULL IS NOT NULL
           AND pvd3.DiagnosisId IN ( NULL )
         )
      OR (
           NULL IS NOT NULL
           AND pvd4.DiagnosisId IN ( NULL )
         )
      OR (
           NULL IS NOT NULL
           AND pvd5.DiagnosisId IN ( NULL )
         )
      OR (
           NULL IS NOT NULL
           AND pvd6.DiagnosisId IN ( NULL )
         )
      OR (
           NULL IS NOT NULL
           AND pvd7.DiagnosisId IN ( NULL )
         )
      OR ( NULL IS NULL )
    )      
 
--Insert rows with payments and adjustments
INSERT INTO
    #tbl_temp
    (
      ticketnumber ,
      patientvisitID ,
      providername ,
      doctorID ,
      referringdocName ,
      resourceID ,
      facilityname ,
      companyname ,
      CPTCode ,
      code ,
      Diag1 ,
      Diag2 ,
      Diag3 ,
      Diag4 ,
      Diag5 ,
      Diag6 ,
      Diag7 ,
      Diag8 ,
      modifier ,
      CPTDescription ,
      department ,
      charges ,
      payments ,
      adjustments ,
      dateofentry ,
      units ,
      patientvisitprocsID ,
      dateofservice ,
      FinancialClass ,
      CarrierName ,
      InsuredId ,
      InsGroupName ,
      Group1 ,
      Group2 ,
      Group3
     
    )
    SELECT
        pv.ticketnumber ,
        pv.patientvisitID ,
        dr.listname AS ProviderName ,
        pv.doctorID AS DoctorID ,
        ISNULL(ref.Listname , 'No Referring Phys') AS ReferringDocName ,
        0 AS ResourceID ,
        fac.listname AS FacilityName ,
        comp.listname AS CompanyName ,
        ISNULL(p.cptcode , 'Deposit') AS CPTCode ,
        CASE tr.type
          WHEN 'P' THEN ISNULL(p.code , 'Deposit')
          ELSE ISNULL(p.code , 'None Adj')
        END AS Code ,
        ISNULL(pvd.Code , '') AS Diag1 ,
        ISNULL(pvd1.Code , '') AS Diag2 ,
        ISNULL(pvd2.Code , '') AS Diag3 ,
        ISNULL(pvd3.Code , '') AS Diag4 ,
        ISNULL(pvd4.Code , '') AS Diag5 ,
        ISNULL(pvd5.Code , '') AS Diag6 ,
        ISNULL(pvd6.Code , '') AS Diag7 ,
        ISNULL(pvd7.Code , '') AS Diag8 ,
        ISNULL(mod1.code , '') + ISNULL(mod2.code , '') + ISNULL(mod3.code , '') + ISNULL(mod4.code , '') AS Modifier ,
        ISNULL(p.description , 'No Description') AS CPTDescription ,
          --pp.last+', '+pp.first+' '+Isnull(pp.middle,'') as PatientName,
        ISNULL(dept.description , 'No Dept') AS Department ,
        0 AS Charges ,
        CASE tr.type
          WHEN 'P' THEN td.amount
          ELSE 0
        END AS Payments ,
        CASE tr.type
          WHEN 'P' THEN 0
          ELSE td.amount
        END AS Adjustments ,
        b.entry AS DateOfEntry ,
        0 AS Units ,
        ISNULL(pvp.patientvisitprocsID , 0) AS patientvisitprocsID ,
        pv.Visit AS DateOfService ,
        ISNULL(fc.description , 'No Financial Class') AS FinancialClass ,
        ISNULL(ic.listname , 'No Insurance') AS CarrierName ,
        (
            SELECT  
                  MAX(ISNULL(pi.InsuredId , '') )
        FROM      
                  PatientVisit pv2
                  LEFT JOIN PatientVisitInsurance pvi ON pv2.PatientVisitId = pvi.PatientVisitId
                  LEFT JOIN PatientInsurance pi ON pv2.CurrentPICarrierId = pi.PatientInsuranceId AND pvi.PatientInsuranceId = pi.PatientInsuranceId
                  LEFT JOIN InsuranceCarriers ic ON pv2.CurrentInsuranceCarriersId = ic.InsuranceCarriersId AND pi.InsuranceCarriersId = ic.InsuranceCarriersId
            WHERE
                  pv2.PatientVisitId = pv.PatientVisitId
        )
        AS InsuredId ,
        ISNULL(ig.name , 'No Ins Group') AS InsGroupName ,
        CASE @groupby1
          WHEN 'Carrier' THEN ISNULL(ic.listname , 'No Carrier')
          WHEN 'Company' THEN ISNULL(comp.listname , ' No Company')
          WHEN 'Department' THEN ISNULL(dept.description , 'No Dept')
          WHEN 'Facility' THEN fac.listname
          WHEN 'Financial Class' THEN ISNULL(fc.description , 'No Financial Class')
          WHEN 'Insurance Group' THEN ISNULL(ig.name , 'No Ins Group')
          WHEN 'Procedure' THEN ISNULL(p.Code , 'Deposit')
          WHEN 'Provider' THEN dr.listname
          WHEN 'Referring Physician' THEN ISNULL(ref.listname , 'No Referring Doc')
          ELSE fac.listname
        END AS Group1 ,
        CASE @groupby2
          WHEN 'Carrier' THEN ISNULL(ic.listname , 'No Carrier')
          WHEN 'Company' THEN ISNULL(comp.listname , ' No Company')
          WHEN 'Department' THEN ISNULL(dept.description , 'No Dept')
          WHEN 'Facility' THEN fac.listname
          WHEN 'Financial Class' THEN ISNULL(fc.description , 'No Financial Class')
          WHEN 'Insurance Group' THEN ISNULL(ig.name , 'No Ins Group')
          WHEN 'Procedure' THEN ISNULL(p.Code , 'No Procedure')
          WHEN 'Provider' THEN dr.listname
          WHEN 'Referring Physician' THEN ISNULL(ref.listname , 'No Referring Doc')
          ELSE fac.listname
        END AS Group2 ,
        CASE @groupby3
          WHEN 'Carrier' THEN ISNULL(ic.listname , 'No Carrier')
          WHEN 'Company' THEN ISNULL(comp.listname , ' No Company')
          WHEN 'Department' THEN ISNULL(dept.description , 'No Dept')
          WHEN 'Facility' THEN fac.listname
          WHEN 'Financial Class' THEN ISNULL(fc.description , 'No Financial Class')
          WHEN 'Insurance Group' THEN ISNULL(ig.name , 'No Ins Group')
          WHEN 'Procedure' THEN ISNULL(p.Code , 'No Procedure')
          WHEN 'Provider' THEN dr.listname
          WHEN 'Referring Physician' THEN ISNULL(ref.listname , 'No Referring Doc')
          ELSE fac.listname
        END AS Group3
       FROM
        transactiondistributions td
        INNER JOIN transactions tr ON td.transactionsID = tr.transactionsID
        INNER JOIN visittransactions vt ON tr.visittransactionsID = vt.visittransactionsID
        INNER JOIN paymentmethod pm ON vt.paymentmethodID = pm.paymentmethodID
        LEFT JOIN insurancecarriers ic ON vt.insurancecarriersID = ic.insurancecarriersID
        LEFT JOIN insurancegroup ig ON ic.insurancegroupID = ig.insurancegroupID
        INNER JOIN patientvisit pv ON pv.patientvisitID = vt.patientvisitID                    
        LEFT JOIN patientvisitprocs pvp ON td.patientvisitprocsID = pvp.patientvisitprocsID
        LEFT JOIN procedures p ON pvp.proceduresID = p.proceduresID
        INNER JOIN batch b ON pm.batchID = b.batchID
        LEFT JOIN (
                    SELECT * FROM medlists WHERE tablename = 'department'
                  ) dept ON p.departmentMID = dept.medlistsID
        LEFT JOIN (
                    SELECT * FROM doctorfacility WHERE type = 2
                  ) fac ON pv.facilityID = fac.doctorfacilityID
        LEFT JOIN (
                    SELECT * FROM doctorfacility WHERE type = 5
                  ) comp ON pv.companyID = comp.doctorfacilityID
        LEFT JOIN (
                    SELECT * FROM doctorfacility WHERE type = 1 OR type = 7
                  ) dr ON pv.doctorID = dr.doctorfacilityID
        LEFT JOIN (
                    SELECT * FROM doctorfacility WHERE type = 3
                  ) ref ON pv.referringdoctorID = ref.doctorfacilityID
        LEFT JOIN (
                    SELECT * FROM medlists WHERE tablename = 'FinancialClass'
                  ) fc ON pv.financialclassMID = fc.medlistsID
        LEFT JOIN (
                    SELECT * FROM medlists WHERE tablename = 'Modifiers'
                  ) mod1 ON pvp.Modifier1MID = mod1.medlistsID
        LEFT JOIN (
                    SELECT * FROM medlists WHERE tablename = 'Modifiers'
                  ) mod2 ON pvp.Modifier2MID = mod2.medlistsID
        LEFT JOIN (
                    SELECT * FROM medlists WHERE tablename = 'Modifiers'
                  ) mod3 ON pvp.Modifier3MID = mod3.medlistsID
        LEFT JOIN (
                    SELECT * FROM medlists WHERE tablename = 'Modifiers'
                  ) mod4 ON pvp.Modifier4MID = mod4.medlistsID
        LEFT OUTER JOIN PatientVisitDiags pvd ON pv.PatientVisitId = pvd.PatientVisitId
                                                 AND pvp.PatientVisitDiags1 = pvd.ListOrder
        LEFT OUTER JOIN PatientVisitDiags pvd1 ON pv.PatientVisitId = pvd1.PatientVisitId
                                                  AND pvp.PatientVisitDiags2 = pvd1.ListOrder
        LEFT OUTER JOIN PatientVisitDiags pvd2 ON pv.PatientVisitId = pvd2.PatientVisitId
                                                  AND pvp.PatientVisitDiags3 = pvd2.ListOrder
        LEFT OUTER JOIN PatientVisitDiags pvd3 ON pv.PatientVisitId = pvd3.PatientVisitId
                                                  AND pvp.PatientVisitDiags4 = pvd3.ListOrder
        LEFT OUTER JOIN PatientVisitDiags pvd4 ON pv.PatientVisitId = pvd4.PatientVisitId
                                                  AND pvp.PatientVisitDiags5 = pvd4.ListOrder
        LEFT OUTER JOIN PatientVisitDiags pvd5 ON pv.PatientVisitId = pvd5.PatientVisitId
                                                  AND pvp.PatientVisitDiags6 = pvd5.ListOrder
        LEFT OUTER JOIN PatientVisitDiags pvd6 ON pv.PatientVisitId = pvd6.PatientVisitId
                                                  AND pvp.PatientVisitDiags7 = pvd6.ListOrder
        LEFT OUTER JOIN PatientVisitDiags pvd7 ON pv.PatientVisitId = pvd7.PatientVisitId
                                                  AND pvp.PatientVisitDiags8 = pvd7.ListOrder
    WHERE
        (
          tr.type = 'P'
          OR tr.type = 'A'
        )
--    and tr.action = 'P'
--    and pv.ticketnumber = @ticketnum
        AND b.entry >= @startdate
        AND b.entry < @enddate
        AND td.amount <> 0
        AND  --Filter on Carrier
        (
          (
            NULL IS NOT NULL
            AND pv.primaryinsurancecarriersID IN ( NULL )
          )
          OR ( NULL IS NULL )
        )
        AND  --Filter on company
        (
          (
            NULL IS NOT NULL
            AND pv.CompanyID IN ( NULL )
          )
          OR ( NULL IS NULL )
        )
        AND  --Filter on Department
        (
          (
            NULL IS NOT NULL
            AND p.DepartmentMId IN ( NULL )
          )
          OR ( NULL IS NULL )
        )
        AND  --Filter on facility
        (
          (
            NULL IS NOT NULL
            AND pv.FacilityID IN ( NULL )
          )
          OR ( NULL IS NULL )
        )
        AND  --Filter on Financial Class
        (
          (
            NULL IS NOT NULL
            AND pv.FinancialClassMID IN ( NULL )
          )
          OR ( NULL IS NULL )
        )
        AND --Filter on Ins Group
        (
          (
            NULL IS NOT NULL
            AND ic.insuranceGroupID IN ( NULL )
          )
          OR ( NULL IS NULL )
        )
        AND  --Filter on Procedure
        (
          (
            NULL IS NOT NULL
            AND pvp.ProceduresId IN ( NULL )
          )
          OR ( NULL IS NULL )
        )
        AND  --Filter on Provider
        (
          (
            NULL IS NOT NULL
            AND pv.DoctorID IN ( NULL )
          )
          OR ( NULL IS NULL )
        )
        AND  --Filter on Referring Doc
        (
          (
            NULL IS NOT NULL
            AND pv.ReferringDoctorID IN ( NULL )
          )
          OR ( NULL IS NULL )
        )
        AND  --Filter on Diagnosis
        (
          (
            NULL IS NOT NULL
            AND pvd.DiagnosisId IN ( NULL )
          )
          OR (
               NULL IS NOT NULL
               AND pvd1.DiagnosisId IN ( NULL )
             )
          OR (
               NULL IS NOT NULL
               AND pvd2.DiagnosisId IN ( NULL )
             )
          OR (
               NULL IS NOT NULL
               AND pvd3.DiagnosisId IN ( NULL )
             )
          OR (
               NULL IS NOT NULL
               AND pvd4.DiagnosisId IN ( NULL )
             )
          OR (
               NULL IS NOT NULL
               AND pvd5.DiagnosisId IN ( NULL )
             )
          OR (
               NULL IS NOT NULL
               AND pvd6.DiagnosisId IN ( NULL )
             )
          OR (
               NULL IS NOT NULL
               AND pvd7.DiagnosisId IN ( NULL )
             )
          OR ( NULL IS NULL )
        )      
 
--Update the 'reporting' resource types
UPDATE
    #tbl_temp
SET
    resourceID = res.doctorfacilityID
FROM
    #tbl_temp t
    LEFT JOIN patientvisitresource pvr2 ON t.patientvisitID = pvr2.patientvisitID
    LEFT JOIN doctorfacility res ON pvr2.resourceID = res.doctorfacilityID
    LEFT JOIN ResourceTypeAssignments rta ON res.doctorfacilityID = rta.resourceID
WHERE
    rta.resourcetypeID = @ResTypeID
 
--Update the non-reporting resources
UPDATE
    #tbl_temp
SET
    resourceID = ISNULL(res.doctorfacilityID , 0)
FROM
    #tbl_temp t
    LEFT JOIN patientvisitresource pvr2 ON t.patientvisitID = pvr2.patientvisitID
    LEFT JOIN doctorfacility res ON pvr2.resourceID = res.doctorfacilityID
WHERE
    t.ResourceID = 0
 
--Update everything that is left with the doctor
--update #tbl_temp
--set resourceID = t.doctorID
--from #tbl_temp t
--where t.ResourceID = 0
 
 
SELECT
    t.Ticketnumber ,
    t.ProviderName ,
    t.ReferringDocName ,
    ISNULL(res.ListName , 'No Resource') AS ResourceName ,
    t.FacilityName ,
    t.CompanyName ,
    t.CPTCode ,
    t.Code ,
    t.Diag1 ,
    t.Diag2 ,
    t.Diag3 ,
    t.Diag4 ,
    t.Diag5 ,
    t.Diag6 ,
    t.Diag7 ,
    t.Diag8 ,
    t.Modifier ,
    t.Units ,
    t.Department ,
    t.Charges ,
    t.Payments ,
    t.Adjustments ,
    t.DateOfEntry ,
    t.DateOfService ,
    t.FinancialClass ,
    t.Carriername ,
    t.InsuredId ,
    t.InsGroupName AS InsGroupName ,
    t.CPTDescription ,
    t.ticketnumber + '_' + t.code + '_' + t.modifier AS TicketCode ,
    CASE @groupby1
      WHEN 'Resource' THEN ISNULL(res.listname , 'No Resource')
      ELSE t.Group1
    END AS Group1 ,
    CASE @groupby2
      WHEN 'Resource' THEN ISNULL(res.listname , 'No Resource')
      ELSE t.Group2
    END AS Group2 ,
    CASE @groupby3
      WHEN 'Resource' THEN ISNULL(res.listname , 'No Resource')
      ELSE t.Group3
    END AS Group3
 
--select sum(t.Charges), sum(t.Payments), sum(t.adjustments)
 
--into cus_ProcCrossRef
 
--drop table cus_ProcCrossRef
FROM
    #tbl_temp t
    LEFT JOIN doctorfacility res ON t.resourceID = res.doctorfacilityID
WHERE
    --Filter on resource
    (
      (
        NULL IS NOT NULL
        AND t.ResourceID IN ( NULL )
      )
      OR ( NULL IS NULL )
    )
 
 
--order by ticketnumber desc
 
DROP TABLE #tbl_temp
Awarded 2008
Awarded 2008
Commented:
this statement is your culprit.  your where clause isn't filtering out enough rows...maybe you can use a top(1)?

        (
            SELECT  --put a TOP(1) here???
                  ISNULL(pi.InsuredId , '')
        FROM      
                  PatientVisit pv2
                  LEFT JOIN PatientVisitInsurance pvi ON pv2.PatientVisitId = pvi.PatientVisitId
                  LEFT JOIN PatientInsurance pi ON pv2.CurrentPICarrierId = pi.PatientInsuranceId AND pvi.PatientInsuranceId = pi.PatientInsuranceId
                  LEFT JOIN InsuranceCarriers ic ON pv2.CurrentInsuranceCarriersId = ic.InsuranceCarriersId AND pi.InsuranceCarriersId = ic.InsuranceCarriersId
            WHERE
                  pv2.PatientVisitId = pv.PatientVisitId
        )
        AS InsuredId ,

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial