Case Statement and Aggregate Function

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 2005 Forums

Transact-SQL (2005)

Case Statement and Aggregate Function

Author

Topic

JeffS23
Posting Yak Master

212 Posts

Posted - 2008-01-08 : 19:14:18

I have a view which I am trying to get an additional limitation on the case statements that are not using the Subledger of "Quoting".

My efforts so far have ended in confusion, the wrong result or have ended up with the old "Error 130 Cannot perform an aggregate function on an expression containing an aggregate or a subquery".

So lets start with the attached code. It runs, however the case statements that give me Costs Completed, Invoice Completed, CompletedGP and GrossProfit are including jobs that have no Quoted Amount.

I have to somehow get this clause into each of the relevent case statements "Sum((case when Jo.SubLedger= 'Quoting' then Jo.Amount else 0 end)) <> 0"

Basically add up only the jobs that have a sum of quote lines not = 0

CREATE VIEW dbo.U_QuoteAnalysisSummary (NoOfJobs,NoOfQuotedJobs,StartTime,
QuotesProspect,
QuotesInProgress,
QuotesLost,
QuotesCompleted,
CostsCompleted,
InvoiceCompleted,
CompletedGP,
GrossProfit,
JobPrice,
QuotedAmount)
AS
SELECT Count(distinct(jo1.jobnumber)),
COUNT(DISTINCT CASE WHEN Jo.SubLedger= 'Quoting' THEN jo1.jobnumber ELSE NULL END),
dbo.nf_DateToPeriod (Jo1.StartTime),

Sum((case when jo1.closed=0 and Jo.SubLedger= 'Quoting' and jo1.status = 'QS' then Jo.Amount else 0 end)),
Sum((case when jo1.closed=0 and Jo.SubLedger= 'Quoting' and jo1.status not in ('XQ','QR','QS','QT','P') then Jo.Amount else 0 end)),
Sum((case when Jo.SubLedger= 'Quoting' and jo1.status = 'XQ' then Jo.Amount else 0 end)),
Sum((case when jo1.closed=1 and Jo.SubLedger= 'Quoting' and jo1.status <> 'XQ' then Jo.Amount else 0 end)),
Sum((case when jo1.closed=1 and Jo.SubLedger= 'Costing' and jo1.status <> 'XQ' then Jo.Amount else 0 end)),
Sum((case when jo1.closed=1 and Jo.SubLedger= 'Invoice' and jo1.status <> 'XQ' then -Jo.Amount else 0 end)),
Sum((case when jo1.closed=1 and (Jo.SubLedger= 'Invoice' or Jo.SubLedger= 'Costing') and jo1.status <> 'XQ' then -Jo.Amount else 0 end)),
(Sum((case when Jo.SubLedger= 'Invoice' and jo1.status <> 'XQ' and jo1.closed = 1 then -Jo.Amount else 0 end))-Sum((case when Jo.SubLedger= 'Costing' and jo1.status <> 'XQ' and jo1.closed = 1 then Jo.Cost else 0 end))),
Max(Jo1.U_JobPrice),
Sum((case when Jo.SubLedger= 'Quoting' then Jo.Amount else 0 end))
FROM JobBalances Jo INNER JOIN Jobs Jo1
ON Jo.JobID = Jo1.JobID INNER JOIN Debtors De
ON Jo1.DebtorID = De.DebtorID
WHERE Jo.SubLedger <> 'INV GST'
And De.DebtorIndex <> 'zAIE'
And Jo1.Status Not In ('QWT','XQA')
GROUP BY dbo.nf_DateToPeriod (Jo1.StartTime)

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-01-08 : 19:27:01

COUNT(DISTINCT CASE WHEN Jo.SubLedger= 'Quoting' THEN jo1.jobnumber ELSE NULL END),
dbo.nf_DateToPeriod (Jo1.StartTime),

looks to be an issue

Change the COUNT(DISTINCT...etc) to

SUM(Case WHen Jo.SubLedger= 'Quoting' THEN 1 else 0 end)

You might also pass the result of your dbo.dt___(starttime) function as the start time parameter instead of calculating the function in the view and Group by...it may speed up the results.

Poor planning on your part does not constitute an emergency on my part.

JeffS23
Posting Yak Master

212 Posts

Posted - 2008-01-08 : 21:27:29

I tried this....
Change the COUNT(DISTINCT...etc) to
SUM(Case WHen Jo.SubLedger= 'Quoting' THEN 1 else 0 end)

I ended up with a number in the "NOOfQuotedJobs" greater than the "NoOfJobs".
I have reverted back because the "NOOfQuotedJobs" should always be a subset of the "NoOfJobs".

with regard to this...
You might also pass the result of your dbo.dt___(starttime) function as the start time parameter instead of calculating the function in the view and Group by...it may speed up the results.

Are you meaning I should declare a variable, then set that variable to be then used in the rest of the script?
As it stands I am converting each line line's starttime into the last day of the month, which is my lowest level of grouping, I can't see another method sorry.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-01-08 : 21:37:19

TO convert the starttime to the last day of the month use the native functionality
I didn't notice the other Count(DISTINCT...) in your query, so I hadn't anticpated that you would get a subset larger than the main count..

re-reading your post to include an additional clause to account for the <>0

to do this wrap the existing SUM(Case...) inside of


Case When (your existing case) <> 0 then (your existing case) else (something else) END AS columname

for example
Case When ( Sum((case when jo1.closed=0 and Jo.SubLedger= 'Quoting' and jo1.status = 'QS' then Jo.Amount else 0 end))) <> 0 then
(Sum((case when jo1.closed=0 and Jo.SubLedger= 'Quoting' and jo1.status = 'QS' then Jo.Amount else 0 end))) else NULL end

re: the starttime..

Assuming starttime is a datetime datatype with a DATE in it..

dateadd(m,1,starttime-day(starttime))

for example would give you the last day of the month based on the day present in starttime. It is minor of course, but using udf in the select and group by might hinder performance.

Poor planning on your part does not constitute an emergency on my part.

shijobaby
Starting Member

44 Posts

Posted - 2009-08-21 : 06:21:06

The ways to avoid this error is simple

just look into my post

http://sqlerrormessages.blogspot.com/2009/08/sql-server-error-message-msg-130-cannot.html

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources