?
Solved

SQL 2005 - SubQuery Help Needed

Posted on 2009-05-11
3
Medium Priority
?
204 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
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.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

777 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