SELECT Within a CASE problem - 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 2008 Forums

Transact-SQL (2008)

SELECT Within a CASE problem

Author

Topic

bh0526
Yak Posting Veteran

71 Posts

Posted - 2011-08-05 : 09:45:02

I have the following SQL statement:

SELECT CTR.ACCOUNTNUM
,CT.Name
,sum(case when DateDiff("d", CTR.DUEDATE, getdate()) <= 30 then CTR.AMOUNTCUR else 0 end) as [Less than 30 Days]
,sum(case when DateDiff("d", CTR.DUEDATE, getdate()) <= 30 then CTR.AMOUNTCUR -(SELECT SUM(AX2009_Carib_Dev.dbo.CustTransOpen.AMOUNTCUR) FROM AX2009_Carib_Dev.dbo.CUSTTRANSOPEN
WHERE AX2009_Carib_Dev.dbo.CustTransOpen.AccountNum = CTR.AccountNum
GROUP BY AX2009_Carib_Dev.dbo.CustTransOpen.AccountNum) else 0 end) as [Previous Month Balance] FROM AX2009_Carib_Dev.dbo.CUSTTrans CTR INNER JOIN
AX2009_Carib_Dev.dbo.CUSTTABLE CT ON CTR.ACCOUNTNUM = CT.ACCOUNTNUM
AND CTR.DATAAREAID = CT.DATAAREAID
GROUP BY CTR.ACCOUNTNUM, CT.NAME
ORDER BY CTR.ACCOUNTNUM, CT.NAME

The part in Bold is what I just added. It works fine without this part. But I want to take the AccountCur and subtract the SUM from another table. But I can't get this to work. When i run this query I get this error:

Msg 130, Level 15, State 1, Line 13
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

Can anyone help?

Bob

Lewie
Starting Member

42 Posts

Posted - 2011-08-05 : 10:21:35

SELECT CTR.ACCOUNTNUM
,CT.Name
,sum(case when DateDiff("d", CTR.DUEDATE, getdate()) <= 30 then CTR.AMOUNTCUR else 0 end) as [Less than 30 Days]
,sum(case when DateDiff("d", CTR.DUEDATE, getdate()) <= 30 then CTR.AMOUNTCUR - isnull(AMC,0) else 0 end) as [Previous Month Balance]
FROM AX2009_Carib_Dev.dbo.CUSTTrans CTR INNER JOIN
AX2009_Carib_Dev.dbo.CUSTTABLE CT
ON CTR.ACCOUNTNUM = CT.ACCOUNTNUM
AND CTR.DATAAREAID = CT.DATAAREAID
Left join
(SELECT AX2009_Carib_Dev.dbo.CustTransOpen.AccountNum as AccNum,SUM(AX2009_Carib_Dev.dbo.CustTransOpen.AMOUNTCUR) as AMC
FROM AX2009_Carib_Dev.dbo.CUSTTRANSOPEN
GROUP BY AX2009_Carib_Dev.dbo.CustTransOpen.AccountNum) z
on z.AccNum = CTR.AccountNum
GROUP BY CTR.ACCOUNTNUM, CT.NAME
ORDER BY CTR.ACCOUNTNUM, CT.NAME

bh0526
Yak Posting Veteran

71 Posts

Posted - 2011-08-05 : 13:11:27

Lewie,

Your solution did not work but I think you are on the right track. AMC was much too large. So something is not quite right.

Bob

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources