CODEWITHSUNDEEP
sqlt-sql
CM_Heroman
CM_Heroman

Reputation: 378

Trying to sum based off an ID

I have this query that I have been working on and I am trying to figure out how to SUM the [Charge Amount] field based off of the [Unique_claim_identifying_number] field. I only need one [Unique_claim_identifying_number] to show the sum of that [Charge Amount]. I am at a point where I am stuck and any additonal help would be greatly appreciated. Thanks in advance.

SELECT DISTINCT 
                     CAST(dbo.TPatientDemographics.[PA-PT-NO-WOSCD] AS varchar(15)) + CAST(dbo.TPatientDemographics.[PA-PT-NO-SCD-1] AS varchar(15)) 
                     AS [Unique_claim_identifying_number], dbo.TPatientDemographics.[PA-ACCT-TYPE], CAST(dbo.TPatientDemographics.[PA-ADM-DATE] AS varchar(11)) AS Admit_Date, 
                     CAST(dbo.TPatientDemographics.[PA-DSCH-DATE] AS varchar(11)) AS Discharge_Date, dbo.TPatientDemographics.[PA-BAL-TOT-CHG-AMT] AS Total_Charges, 
                     dbo.TPatientDemographics.[PA-ADM-PRTY] AS Type_of_Admission, dbo.TInsuranceInformation.[PA-INS-CO-CD], dbo.TInsuranceInformation.[PA-INS-PRTY], 
                     dbo.TDRGInformation.[PA-DRG-NO-2], dbo.TDRGInformation.[PA-DRG-SEG-TYPE], dbo.TMiscellaneousInformation.[PA-ADM-AGE], 
                     dbo.TPatientDemographics.[PA-CURR-DISP], dbo.TDetailInformation.[PA-DTL-CHG-AMT] + DetailInformation_1.[PA-DTL-CHG-AMT] AS [Charge Amount], 
                     DetailInformation_2.[PA-DTL-CHG-AMT] AS [20389 Charge Amount], dbo.TDetailInformation.[PA-DTL-SVC-CD-WOSCD] AS [20129], 
                     DetailInformation_1.[PA-DTL-SVC-CD-WOSCD] AS [20139], DetailInformation_2.[PA-DTL-SVC-CD-WOSCD] AS [20389], DXI.DIAG_1, DXI.DIAG_2, DXI.DIAG_3, 
                     DXI.DIAG_4, DXI.DIAG_5, DXI.DIAG_6, DXI.DIAG_7, DXI.DIAG_8, DXI.DIAG_9, DXI.DIAG_10, PROCI.Procedure_Code_01, PROCI.Procedure_Code_02, 
                     PROCI.Procedure_Code_03, PROCI.Procedure_Code_04, PROCI.Procedure_Code_05, PROCI.Procedure_Code_06, PROCI.Procedure_Code_07, 
                     PROCI.Procedure_Code_08, PROCI.Procedure_Code_09, PROCI.Procedure_Code_10
FROM            dbo.TPatientDemographics WITH (NOLOCK) INNER JOIN
                     dbo.TInsuranceInformation ON dbo.TPatientDemographics.[PA-PT-NO-WOSCD] = dbo.TInsuranceInformation.[PA-PT-NO-WOSCD] AND 
                     dbo.TPatientDemographics.[PA-ACCT-TYPE] = dbo.TInsuranceInformation.[PA-ACCT-TYPE] INNER JOIN
                     dbo.TDRGInformation ON dbo.TPatientDemographics.[PA-PT-NO-WOSCD] = dbo.TDRGInformation.[PA-PT-NO-WOSCD] AND 
                     dbo.TPatientDemographics.[PA-ACCT-TYPE] = dbo.TDRGInformation.[PA-ACCT-TYPE] INNER JOIN
                     dbo.TMiscellaneousInformation ON dbo.TPatientDemographics.[PA-PT-NO-WOSCD] = dbo.TMiscellaneousInformation.[PA-PT-NO-WOSCD] AND 
                     dbo.TPatientDemographics.[PA-ACCT-TYPE] = dbo.TMiscellaneousInformation.[PA-ACCT-TYPE] INNER JOIN
                     dbo.TDetailInformation ON dbo.TPatientDemographics.[PA-PT-NO-WOSCD] = dbo.TDetailInformation.[PA-PT-NO-WOSCD] AND 
                     dbo.TPatientDemographics.[PA-ACCT-TYPE] = dbo.TDetailInformation.[PA-ACCT-TYPE] INNER JOIN
                     dbo.TDetailInformation AS DetailInformation_1 ON dbo.TPatientDemographics.[PA-PT-NO-WOSCD] = DetailInformation_1.[PA-PT-NO-WOSCD] AND 
                     dbo.TPatientDemographics.[PA-ACCT-TYPE] = DetailInformation_1.[PA-ACCT-TYPE] INNER JOIN
                     dbo.TDetailInformation AS DetailInformation_2 ON dbo.TPatientDemographics.[PA-PT-NO-WOSCD] = DetailInformation_2.[PA-PT-NO-WOSCD] AND 
                     dbo.TPatientDemographics.[PA-ACCT-TYPE] = DetailInformation_2.[PA-ACCT-TYPE] LEFT OUTER JOIN
                         (SELECT        [PA-PT-NO-WOSCD], [PA-ACCT-TYPE], MAX(CASE WHEN [PA-DX2-PRIO-NO] = 1 THEN [PA-DX2-CODE] ELSE '' END) AS DIAG_1, 
                                                     MAX(CASE WHEN [PA-DX2-PRIO-NO] = 2 THEN [PA-DX2-CODE] ELSE '' END) AS DIAG_2, 
                                                     MAX(CASE WHEN [PA-DX2-PRIO-NO] = 3 THEN [PA-DX2-CODE] ELSE '' END) AS DIAG_3, 
                                                     MAX(CASE WHEN [PA-DX2-PRIO-NO] = 4 THEN [PA-DX2-CODE] ELSE '' END) AS DIAG_4, 
                                                     MAX(CASE WHEN [PA-DX2-PRIO-NO] = 5 THEN [PA-DX2-CODE] ELSE '' END) AS DIAG_5, 
                                                     MAX(CASE WHEN [PA-DX2-PRIO-NO] = 6 THEN [PA-DX2-CODE] ELSE '' END) AS DIAG_6, 
                                                     MAX(CASE WHEN [PA-DX2-PRIO-NO] = 7 THEN [PA-DX2-CODE] ELSE '' END) AS DIAG_7, 
                                                     MAX(CASE WHEN [PA-DX2-PRIO-NO] = 8 THEN [PA-DX2-CODE] ELSE '' END) AS DIAG_8, 
                                                     MAX(CASE WHEN [PA-DX2-PRIO-NO] = 9 THEN [PA-DX2-CODE] ELSE '' END) AS DIAG_9, 
                                                     MAX(CASE WHEN [PA-DX2-PRIO-NO] = 10 THEN [PA-DX2-CODE] ELSE '' END) AS DIAG_10
                           FROM            dbo.TDiagnosisInformation
                           GROUP BY [PA-PT-NO-WOSCD], [PA-ACCT-TYPE]) AS DXI ON DXI.[PA-PT-NO-WOSCD] = TPatientDemographics.[PA-PT-NO-WOSCD] AND 
                     DXI.[PA-ACCT-TYPE] = TPatientDemographics.[PA-ACCT-TYPE] LEFT OUTER JOIN
                         (SELECT        [PA-PT-NO-WOSCD], [PA-ACCT-TYPE], MAX(CASE WHEN [PA-PROC3-PRTY] = 1 THEN [PA-PROC3-CD] ELSE '' END) AS Procedure_Code_01, 
                                                     MAX(CASE WHEN [PA-PROC3-PRTY] = 2 THEN [PA-PROC3-CD] ELSE '' END) AS Procedure_Code_02, 
                                                     MAX(CASE WHEN [PA-PROC3-PRTY] = 3 THEN [PA-PROC3-CD] ELSE '' END) AS Procedure_Code_03, 
                                                     MAX(CASE WHEN [PA-PROC3-PRTY] = 4 THEN [PA-PROC3-CD] ELSE '' END) AS Procedure_Code_04, 
                                                     MAX(CASE WHEN [PA-PROC3-PRTY] = 5 THEN [PA-PROC3-CD] ELSE '' END) AS Procedure_Code_05, 
                                                     MAX(CASE WHEN [PA-PROC3-PRTY] = 6 THEN [PA-PROC3-CD] ELSE '' END) AS Procedure_Code_06, 
                                                     MAX(CASE WHEN [PA-PROC3-PRTY] = 7 THEN [PA-PROC3-CD] ELSE '' END) AS Procedure_Code_07, 
                                                     MAX(CASE WHEN [PA-PROC3-PRTY] = 8 THEN [PA-PROC3-CD] ELSE '' END) AS Procedure_Code_08, 
                                                     MAX(CASE WHEN [PA-PROC3-PRTY] = 9 THEN [PA-PROC3-CD] ELSE '' END) AS Procedure_Code_09, 
                                                     MAX(CASE WHEN [PA-PROC3-PRTY] = 10 THEN [PA-PROC3-CD] ELSE '' END) AS Procedure_Code_10
                           FROM            dbo.TProcedureInformation
                           GROUP BY [PA-PT-NO-WOSCD], [PA-ACCT-TYPE]) AS PROCI ON PROCI.[PA-PT-NO-WOSCD] = TPatientDemographics.[PA-PT-NO-WOSCD] AND 
                     PROCI.[PA-ACCT-TYPE] = TPatientDemographics.[PA-ACCT-TYPE] 
WHERE        (dbo.TInsuranceInformation.[PA-INS-CO-CD] = 'M') AND (dbo.TInsuranceInformation.[PA-INS-PRTY] = 1) AND (dbo.TDRGInformation.[PA-DRG-SEG-TYPE] = '1') AND 
                     (dbo.TDetailInformation.[PA-DTL-SVC-CD-WOSCD] = 20129) AND (DetailInformation_1.[PA-DTL-SVC-CD-WOSCD] = 20139) AND 
                     (DetailInformation_2.[PA-DTL-SVC-CD-WOSCD] = 20389)

Upvotes: 0

Views: 66

Answers (1)

Consult Yarla
Consult Yarla

Reputation: 1150

Ok, so, try using this structure..

select 
    a.[Unique_claim_identifying_number],
    sum([Charge Amount]) as Total_Charge_Amount
from  
(

   ...... place your query here.....

) a
group by 
a.[Unique_claim_identifying_number]

Upvotes: 2

Related Questions