Earlier today, I recvd fast accurate help on my question. In testing my data, I found one minor issue, I did not plan for in my original post. My issue has to do with the ProcedureFiled. For example, in testing, I had a claim filed with 6 procedures. The totalallowed subquery calculated right on this one, because it contained all the procedure. In another filing only one of the 6 procedures was filed and the subquery told it to produce a result as if all 6 codes had been filed. So I think I need to factor in the ProcedureFiled. /* Claims report */SET NOCOUNT ON-- paper claims and claim statusSELECT 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.TotalAllowedFROM 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.PatientProfileIdWHERE etc, etc, etc .....
Subquery Help Part 2
Posted - 2008-01-17 : 17:04:54
