Jeff S
asked on
SQL 2005 - SubQuery Help Needed
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.
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
ASKER
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Open in new window