Absolute function is not working 1

[vba317]

I started working with the absolute function today. Right now I am getting an error: Incorrect syntax near the keyword 'HAVING'.
The statement I am working with: HAVING (ABS(Sum(Case When CS.trantype =4 Then (CS.Amt) Else 0 End)) = AR.CurBal)
I originally tried to put this at the end of the select line but I got the error: incorrect syntax near the '='
In layman's terms what I am trying to do is compare the absolute values of CS.Amt and AR.Curbal when CS.trantype = 4 only if these values are equal to each other do I want the value CS.Amt to be shown.

Tom

DECLARE @begrptpd int,@currptpd int,@clntid varchar(3)
SET @currptpd = (SELECT pd FROM rptdata_monthly.dbo.dic_Period WHERE calpddiff =1)
SET @begrptpd = @currptpd-4
SET @clntid =(SELECT clntid FROM rptdata_monthly.dbo.rpt_FYInfo WHERE rptpddiff = 1 AND uci='TAM')


SELECT AR.clntid,CS.rptpd,P.PatName,P.AcctNu,AR.aid,AR.eid,AR.slid,CS.trancd,CS.trantype,CS.posid,CS.facid,CS.dptid,CS.mod1,CS.crcat,SUM(AR.curbal) as AR_Bal,Sum(Case When CS.trantype =4 Then (CS.Amt) Else 0 End) as CS_Amt
FROM rptdata_monthly.dbo.rpt_dat_ARTrend_Detail AR
JOIN rptdata_monthly.dbo.rpt_dat_CSDetail CS ON CS.clntid=AR.clntid and CS.aid=AR.aid
AND CS.eid=AR.eid AND CS.slid=AR.slid 
INNER JOIN rptdata_ahs.dbo.bi_PatientData P ON P.clntid = AR.clntid AND P.aid = AR.aid
WHERE AR.clntid =  @clntid AND P.AcctNu = '1498' AND AR.rptpd = @currptpd AND AR.curbal <0  
GROUP BY AR.clntid,CS.rptpd,P.PatName,P.AcctNu,CS.trantype,AR.aid,AR.eid,AR.slid,CS.trancd,CS.posid,CS.facid,CS.dptid,CS.mod1,CS.crcat,CS.Amt
ORDER BY AR.clntid,CS.rptpd,P.PatName,P.AcctNu,CS.trantype,AR.aid,AR.eid,AR.slid,CS.trancd,CS.posid,CS.facid,CS.dptid,CS.mod1,CS.crcat,CS.Amt
HAVING (ABS(Sum(Case When CS.trantype =4 Then (CS.Amt) Else 0 End)) = AR.CurBal)

 

[gmmastros]

The ORDER BY clause must come last. Move the HAVING clause immediately after the GROUP BY clause.

DECLARE @begrptpd int,@currptpd int,@clntid varchar(3)
SET @currptpd = (SELECT pd FROM rptdata_monthly.dbo.dic_Period WHERE calpddiff =1)
SET @begrptpd = @currptpd-4
SET @clntid =(SELECT clntid FROM rptdata_monthly.dbo.rpt_FYInfo WHERE rptpddiff = 1 AND uci='TAM')


SELECT AR.clntid,CS.rptpd,P.PatName,P.AcctNu,AR.aid,AR.eid,AR.slid,CS.trancd,CS.trantype,CS.posid,CS.facid,CS.dptid,CS.mod1,CS.crcat,SUM(AR.curbal) as AR_Bal,Sum(Case When CS.trantype =4 Then (CS.Amt) Else 0 End) as CS_Amt
FROM rptdata_monthly.dbo.rpt_dat_ARTrend_Detail AR
JOIN rptdata_monthly.dbo.rpt_dat_CSDetail CS ON CS.clntid=AR.clntid and CS.aid=AR.aid
AND CS.eid=AR.eid AND CS.slid=AR.slid 
INNER JOIN rptdata_ahs.dbo.bi_PatientData P ON P.clntid = AR.clntid AND P.aid = AR.aid
WHERE AR.clntid =  @clntid AND P.AcctNu = '1498' AND AR.rptpd = @currptpd AND AR.curbal <0  
GROUP BY AR.clntid,CS.rptpd,P.PatName,P.AcctNu,CS.trantype,AR.aid,AR.eid,AR.slid,CS.trancd,CS.posid,CS.facid,CS.dptid,CS.mod1,CS.crcat,CS.Amt
[blue]HAVING (ABS(Sum(Case When CS.trantype =4 Then (CS.Amt) Else 0 End)) = AR.CurBal) [/blue]
[green]ORDER BY AR.clntid,CS.rptpd,P.PatName,P.AcctNu,CS.trantype,AR.aid,AR.eid,AR.slid,CS.trancd,CS.posid,CS.facid,CS.dptid,CS.mod1,CS.crcat,CS.Amt[/green]

-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

 

[vba317]

That did it, Thanks!