Solved

SQL 2005 - SubQuery Help Needed

Posted on 2009-05-11
3
172 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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

772 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

12 Experts available now in Live!

Get 1:1 Help Now