Summing function is not working 1

[vba317]

I am using SQL 2005. When I get the results of my select query I am getting duplicate rows. I have tried to use orderby and groupby with no luck. Finally I tried to use sum Also this did not work. Any help is appreciated.

SELECT 
ar.UCI
,(case when ar.transagebucket < 1 then '0_30'
		when ar.transagebucket = 1 then '31_60'
		when ar.transagebucket = 2 then '61_90'
		when ar.transagebucket = 3 then '91_120'
		when ar.transagebucket = 4 then '121_150'
		when ar.transagebucket = 5 then '151_180'
		else 'Over180' end) as AgeBucket
,ar.PatName as PatientName
,ar.AcctNu as AccountNumber
,ar.dos as Svc_Date
,ar.cptdisplay
,(ins.insdesc+ ' - ' +ins.insmne) as Insurance
,Sum(ar.chgamt) as ChgAmt
,ISNULL(case when adj.trantype = 2 and adj.adjcat = 'DEBIT' then Sum(adj.adjamt) END,0) as Debit
,Sum(pmt.pmtamt) as Pmt
,ISNULL(case when adj.trantype = 3 and adj.adjcat = 'ADMIN_ADJ' then Sum(adj.adjamt) END,0) as Adjustments
,ISNULL(case when adj.trantype = 3 and adj.adjcat = 'WRITE_OFF' then Sum(adj.adjamt) END,0) as WO
,Sum(ar.curbal) as CurBal
FROM rpt_dat_ARDetail ar
	INNER JOIN rpt_dic_Ins ins ON ar.insmne = ins.insmne AND ar.clntid = ins.clntid
	INNER JOIN rpt_dat_PmtDetail pmt ON ar.clntid = pmt.clntid AND ar.AcctNu = pmt.AcctNu AND ar.dos = pmt.dos
    INNER JOIN rpt_dat_AdjustmentDetail adj ON ar.clntid = adj.clntid AND ar.aid = adj.aid 
WHERE ar.reportmonth ='5/1/2013' and ar.clntid =69 
GROUP BY ar.uci,ar.transagebucket,ar.PatName,ar.AcctNu,ar.dos,ar.cptdisplay,ins.insdesc,ins.insmne,ar.chgamt,adj.trantype,adj.adjcat,ar.chgamt,adj.adjamt,pmt.pmtamt,ar.curbal
ORDER BY ar.uci,ar.transagebucket,ar.PatName,ar.AcctNu,ar.dos,ar.cptdisplay,ins.insdesc,ins.insmne,adj.trantype,adj.adjcat,ar.chgamt,adj.adjamt,pmt.pmtamt,ar.curbal;

 

[imex]

Try:

SELECT 
ar.UCI
,(case when ar.transagebucket < 1 then '0_30'
		when ar.transagebucket = 1 then '31_60'
		when ar.transagebucket = 2 then '61_90'
		when ar.transagebucket = 3 then '91_120'
		when ar.transagebucket = 4 then '121_150'
		when ar.transagebucket = 5 then '151_180'
		else 'Over180' end) as AgeBucket
,ar.PatName as PatientName
,ar.AcctNu as AccountNumber
,ar.dos as Svc_Date
,ar.cptdisplay
,(ins.insdesc+ ' - ' +ins.insmne) as Insurance
,Sum(ar.chgamt) as ChgAmt
,Sum(case when adj.trantype = 2 and adj.adjcat = 'DEBIT' then adj.adjamt else 0 end) as Debit
,Sum(pmt.pmtamt) as Pmt
,Sum(case when adj.trantype = 3 and adj.adjcat = 'ADMIN_ADJ' then adj.adjamt else 0 end) as Adjustments
,Sum(case when adj.trantype = 3 and adj.adjcat = 'WRITE_OFF' then adj.adjamt else 0 end) as WO
,Sum(ar.curbal) as CurBal
FROM rpt_dat_ARDetail ar
	INNER JOIN rpt_dic_Ins ins ON ar.insmne = ins.insmne AND ar.clntid = ins.clntid
	INNER JOIN rpt_dat_PmtDetail pmt ON ar.clntid = pmt.clntid AND ar.AcctNu = pmt.AcctNu AND ar.dos = pmt.dos
    INNER JOIN rpt_dat_AdjustmentDetail adj ON ar.clntid = adj.clntid AND ar.aid = adj.aid 
WHERE ar.reportmonth ='5/1/2013' and ar.clntid =69 
GROUP BY 
    ar.uci,
    ar.transagebucket,
    ar.PatName,
    ar.AcctNu,
    ar.dos,
    ar.cptdisplay,
    ins.insdesc,
    ins.insmne,
    adj.trantype,
    adj.adjcat

Hope this helps.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos[/url]

 

[vba317]

Thanks that did it.

 

[gmmastros]

I would encourage you to double and triple check the results of the query.

The problem I see is this....

You are summing data from the adj table and pmt table, and ar table. The reason you are summing is because you (presumably) have multiple rows in the child tables.

The problem is that the joins may cause extra rows to appear in the output, which are getting summed together.

For example:

Declare @Parent Table(Id Int, Name VarChar(20))
Insert Into @Parent Values(1,'George')

Declare @Payments Table(id int, Amount Decimal(10,2))
Insert Into @Payments Values(1, 1.11)

Declare @Charges Table(Id Int, Amount Decimal(10,2))

Insert Into @Charges Values(1, 2.22)
Insert Into @Charges Values(1, 3.33)

Select  Parent.Id,
        Parent.Name,
        Sum(Payments.Amount) As TotalPayments,
        Sum(Charges.Amount) As TotalCharges
From    @Parent Parent
        Inner Join @Payments Payments
          On Parent.Id = Payments.Id
        Inner Join @Charges Charges
          On Parent.Id = Charges.Id
Group By Parent.Id,
        Parent.Name

Notice how there's only 1.11 in the payments table, but the query shows 2.22. This is because there are multiple rows in the charges table, so your joins are causing extra rows and therefore your sums are not correct.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom