Subquery SUM() help needed - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Subquery SUM() help needed

Author

Topic

JeffS23
Posting Yak Master

212 Posts

Posted - 2008-01-17 : 12:11:06

I need help with a subquery and the results its returning. Currently, I am getting a summarized total of all visits in the subquery, not individual results back. Not sure what I goofed.

Snippet of section of code giving me issues:

SELECT pv.TicketNumber,
dbo.FormatName(pp.Prefix, pp.First, pp.Middle, pp.Last, pp.Suffix) AS Name,
pv.Visit,
'' AS FileName,
FilingMethod = ISNULL(fm.Description, '<unknown>') + CASE WHEN ISNULL(pvpc.ClaimStatus, 0) <> 0 THEN ' Claim Status' ELSE '' END,
pv.FilingType,
'' AS Clearinghouse,
pvpc.CreatedBy AS FiledBy,
pvpc.Name AS FiledTo,
NULL AS SubmissionNumber,
pvpc.CreatedBy AS CreatedBy,
pvpc.Created AS DateCreated,
pvpc.Created AS DateTransmitted,
pvpc.Procedures AS ProcedureFiled,
pvpc.Charges AS AmountFiled,
(SELECT SUM(pvp.totalallowed)
FROM PatientVisitProcs pvp
JOIN PatientVisit pv ON pvp.PatientVisitId = pv.PatientVisitId
WHERE pv.PatientVisitId = pvp.patientvisitid
)AS TotalAllowed

FROM PatientVisitPaperClaim pvpc
INNER JOIN PatientVisit pv ON pvpc.PatientVisitId = pv.PatientVisitId
LEFT JOIN MedLists fm ON pvpc.FilingMethodMId = fm.MedListsId
INNER JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId

WHERE etc, etc, etc .....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-17 : 12:20:03

Can you explain what you are trying to achieve by subquery?

JeffS23
Posting Yak Master

212 Posts

Posted - 2008-01-17 : 12:22:52

A summarized total of the totalallowed column for each distinct patient visit. With what I have now, It sums all the patients up and each row in the dataset includes the same value in it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-17 : 12:31:42

quote:
Originally posted by JeffS23

A summarized total of the totalallowed column for each distinct patient visit. With what I have now, It sums all the patients up and each row in the dataset includes the same value in it.

use like this

SELECT pv.TicketNumber, 
dbo.FormatName(pp.Prefix, pp.First, pp.Middle, pp.Last, pp.Suffix) AS Name,
pv.Visit, 
'' AS FileName, 
FilingMethod = ISNULL(fm.Description, '<unknown>') + CASE WHEN ISNULL(pvpc.ClaimStatus, 0) <> 0 THEN ' Claim Status' ELSE '' END, 
pv.FilingType, 
'' AS Clearinghouse, 
pvpc.CreatedBy AS FiledBy, 
pvpc.Name AS FiledTo, 
NULL AS SubmissionNumber, 
pvpc.CreatedBy AS CreatedBy, 
pvpc.Created AS DateCreated, 
pvpc.Created AS DateTransmitted, 
pvpc.Procedures AS ProcedureFiled, 
pvpc.Charges AS AmountFiled,
tmp.TotalAllowed

FROM PatientVisitPaperClaim pvpc 
INNER JOIN PatientVisit pv ON pvpc.PatientVisitId = pv.PatientVisitId 
INNER JOIN (SELECT pv1.PatientVisitId ,SUM(pvp.totalallowed) AS TotalAllowed
FROM PatientVisitProcs pvp
JOIN PatientVisit pv1 
ON pvp.PatientVisitId = pv1.PatientVisitId
GROUP BY pv1.PatientVisitId )tmp
ON  tmp.PatientVisitId =pv.PatientVisitId 
LEFT JOIN MedLists fm ON pvpc.FilingMethodMId = fm.MedListsId 
INNER JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId

JeffS23
Posting Yak Master

212 Posts

Posted - 2008-01-17 : 12:44:13

Wonderful. I appreciate the help and it looks like I am getting the results I need.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources