Solved

SQL 2005 - SubQuery Help Needed

Posted on 2009-05-11
3
185 Views
Last Modified: 2012-05-06
Please Note, the SQL is handled dynamically by the Server, therefore a lot of what's in my WHERE clause will look odd to you. Please disregard this.

My Subquery:

    (
          SELECT
            ISNULL(pvp.Fee , 0)
          FROM
            PatientVisitProcs pvp
            INNER JOIN PatientVisit pv ON pvp.PatientVisitId = pv.PatientVisitId
          WHERE
            (
              pv.Entered < DATEADD(month , 1 , '5/01/2009')
              AND pv.Entered >= '5/01/2009'
            )
        ) AS TotalCharge ,

Right now its totaling them all up and putting that value in each result set versus each one independently. Any help is appreciated.
SELECT
        pvp.patientvisitprocsid ,
        pvp.Code ,
        pvp.CPTCode ,
--        ISNULL(pvp.Fee , 0) AS TotalCharge ,
        (
          SELECT
            ISNULL(pvp.Fee , 0)
          FROM
            PatientVisitProcs pvp
            INNER JOIN PatientVisit pv ON pvp.PatientVisitId = pv.PatientVisitId
          WHERE
            (
              pv.Entered < DATEADD(month , 1 , '5/01/2009')
              AND pv.Entered >= '5/01/2009'
            )
        ) AS TotalCharge ,
        SUM(CASE WHEN t.Action = 'P' THEN td.Amount
                 ELSE 0
            END) AS TotalPayment ,
        SUM(CASE WHEN t.Action = 'A' THEN td.Amount
                 ELSE 0
            END) AS TotalAdjustment ,
        MONTH(pm.DateOfEntry) AS Month ,
        ISNULL(dept.Description , 'Unspecified') AS Department ,
        ISNULL(fc.Description , 'Unspecified') AS FinancialClass ,
        doc.ListName AS Doctor ,
        fac.ListName AS Facility ,
        ISNULL(dffr.ListName , 'Unspecified') AS Resource ,
        co.ListName AS Company ,
        prc.Description ,
        MonthCount = 1
    FROM
        PatientVisitProcs pvp
        INNER JOIN PatientVisit pv ON pvp.PatientVisitId = pv.PatientVisitId
        INNER JOIN Procedures prc ON pvp.ProceduresId = prc.ProceduresId
        LEFT JOIN MedLists dept ON prc.DepartmentMId = dept.MedListsId
        INNER JOIN TransactionDistributions td ON pvp.PatientVisitProcsId = td.PatientVisitProcsId
        INNER JOIN Transactions t ON t.TransactionsId = td.TransactionsId
        INNER JOIN VisitTransactions vt ON t.VisitTransactionsId = vt.VisitTransactionsId
        INNER JOIN PaymentMethod pm ON vt.PaymentMethodId = pm.PaymentMethodId
                                       AND (
                                             pm.InsuranceTransfer IS NULL
                                             OR pm.InsuranceTransfer = 0
                                           )
        LEFT JOIN MedLists fc ON pv.FinancialClassMId = fc.MedListsId
        INNER JOIN DoctorFacility doc ON pv.DoctorId = doc.DoctorFacilityId
        INNER JOIN DoctorFacility fac ON pv.FacilityId = fac.DoctorFacilityId
        INNER JOIN DoctorFacility co ON pv.CompanyId = co.DoctorFacilityId
        LEFT OUTER JOIN PatientVisitResource pvr ON pv.PatientVisitId = pvr.PatientVisitId
                                                    AND 1 <> 1
        LEFT OUTER JOIN DoctorFacility dffr ON pvr.ResourceId = dffr.DoctorFacilityId
    WHERE
        --Filter on Department
        (
          (
            NULL IS NOT NULL
            AND prc.DepartmentMId 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 Doctor
        (
          (
            NULL IS NOT NULL
            AND pv.DoctorId 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 company
        (
          (
            NULL IS NOT NULL
            AND pv.CompanyId IN ( NULL )
          )
          OR ( NULL IS NULL )
        )
        AND --Filter on resource
        (
          (
            NULL IS NOT NULL
            AND pvr.ResourceId IN ( NULL )
          )
          OR ( NULL IS NULL )
        )
        AND --Filter on date
        (
          pm.DateOfEntry < DATEADD(month , 1 , '5/01/2009')
          AND pm.DateOfEntry >= '5/01/2009'
        )
    GROUP BY
        pvp.patientvisitprocsid ,
        pvp.Code ,
        pvp.CPTCode ,
        pvp.Fee ,
        MONTH(pm.DateOfEntry) ,
        dept.Description ,
        fc.Description ,
        doc.ListName ,
        co.ListName ,
        dffr.ListName ,
        fac.ListName ,
        prc.Description
    ORDER BY
        pvp.Code

Open in new window

0
Comment
Question by:Jeff S
  • 2
3 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24356091
The problem is that you have PatientVisitProcs pvp and PatientVisit pv, inside the sub-select with the same aliases as outside the sub-select.  Since they both exist, your query is not being treated as a correlated sub-select.  So there is NO correlation between the sub-select and the outer records so every value from the sub-select will be the same.


    (
          SELECT
            ISNULL(pvp1.Fee , 0)
          FROM
            PatientVisitProcs pvp1
            INNER JOIN PatientVisit pv1 ON pvp1.PatientVisitId = pv1.PatientVisitId
          WHERE
            (
              pv1.Entered < DATEADD(month , 1 , '5/01/2009')
              AND pv1.Entered >= '5/01/2009'
            )
and pvp1.SOMETHING = pvp.something /* the innner PVP to the outer PVP */
and pv1.SOMETHING = pv.something /* the innner PV to the outer PV */
        ) AS TotalCharge ,

Open in new window

0
 
LVL 7

Author Comment

by:Jeff S
ID: 24356167
Brandon ....
I used this:

(
SELECT
ISNULL(pvp1.Fee , 0)
FROM
PatientVisitProcs pvp1
INNER JOIN PatientVisit pv1 ON pvp1.PatientVisitId = pv1.PatientVisitId
WHERE
(
pv1.Entered < DATEADD(month , 1 , '5/01/2009')
AND pv1.Entered >= '5/01/2009'
)
and pvp1.PatientVisitId = pvp.PatientVisitId /* the innner PVP to the outer PVP */
and pv1.PatientVisitId = pv.PatientVisitId /* the innner PV to the outer PV */
) AS TotalCharge ,
I get NULL now ... anyway to get 0 instead?
0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 500 total points
ID: 24356201
You have to put your isnull out side of the sub-query.
isnull(
      (SELECT
      pvp1.Fee
      FROM
      PatientVisitProcs pvp1
      INNER JOIN PatientVisit pv1 ON pvp1.PatientVisitId = pv1.PatientVisitId
      WHERE
      (
      pv1.Entered < DATEADD(month , 1 , '5/01/2009')
      AND pv1.Entered >= '5/01/2009'
      )
      and pvp1.PatientVisitId = pvp.PatientVisitId /* the innner PVP to the outer PVP */
      and pv1.PatientVisitId = pv.PatientVisitId /* the innner PV to the outer PV */
      )
,0) AS TotalCharge ,

Open in new window

0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SYbase 4 24
Query Help - MSSQL - Averages 5 25
convert null in sql server 12 31
sql select record as one long string 21 22
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now