Solved

SQL 2005 - SubQuery Help Needed

Posted on 2009-05-11
3
180 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

911 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

21 Experts available now in Live!

Get 1:1 Help Now