Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL 2005 - SubQuery Help Needed

Posted on 2009-05-11
3
Medium Priority
?
213 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 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

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

Ready to get certified? Check out some courses that help you prepare for third-party exams.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

572 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