Sum (Case... ) ??? - 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 2000 Forums

Transact-SQL (2000)

Sum (Case... ) ???

Author

Topic

Richard Branson
Yak Posting Veteran

84 Posts

Posted - 2005-06-20 : 04:15:08

Hi Fellow code warriors

I have a problem with the following code. It's returning the following error:

Server: Msg 170, Level 15, State 1, Line 15
Line 15: Incorrect syntax near 'sum'.

Here's the code:

Declare @L_Period datetime

Select @L_Period = max(Load_Period)
from bf_Load..vat_update

Select 	top 10
	e.Enterprise_nbr		,
	e.Life_cycle_code		,
	count(r.Vat_Nbr	) Vat_Count	,
	Sum (Case LIFE_STATUS_CODE When '' then 1 else 0 end) as [Active]		,
	Sum (Case LIFE_STATUS_CODE When 'S' then 1 else 0 end) as [S-SUSPEND]		,
	Sum (Case LIFE_STATUS_CODE When 'Y' then 1 else 0 end) as [DE-REGISTERED]	,
	Sum (Case LIFE_STATUS_CODE When 'B' then 1 else 0 end) as [ESTATE],
	Sum (Case LIFE_STATUS_CODE When 'X' then 1 else 0 end) as [X-suspense],
	sum (Case processed_date when Datediff(m, Max(Tax_Period_Date), @L_Period) < 18 then 1 else 0 end) [Within 18 months]		 ,
	Sum (Case processed_date when Datediff(m, Max(Tax_Period_Date), @L_Period) between 18 and 24 then 1 else 0 end) [18 - 24 months] ,
	Sum (Case processed_date when Datediff(m, Max(Tax_Period_Date), @L_Period) between 25 and 36 then 1 else 0 end) [25 - 36 months] ,
	Sum (Case processed_date when Datediff(m, Max(Tax_Period_Date), @L_Period) > 37 then 1 else 0 end) [36 months and greater] 
from Enterprise	e
Inner Join Enterprise_External_Link el
	on e.enterprise_nbr = el.enterprise_nbr
Inner join Representative_Tax_Rel r
	on el.external_nbr = r.tax_nbr	
Inner Join Representative re
	on external_nbr = REPRESENTATIVE_VAT_NBR
Inner Join Branch b
	on b.REPRESENTATIVE_VAT_NBR = re.REPRESENTATIVE_VAT_NBR
Inner Join Vat_return_adjusted v
	On r.Vat_nbr = v.Vat_nbr
Group by e.Enterprise_nbr, Life_cycle_code
Having Count(r.Vat_nbr) > 10
Order by e.Enterprise_nbr

Anybody have a clue as to what the problem might be????

You can't teach an old mouse new clicks.

Richard Branson
Yak Posting Veteran

84 Posts

Posted - 2005-06-20 : 04:19:38

I posted the wrong error, here's the correct one:

Server: Msg 170, Level 15, State 1, Line 15
Line 15: Incorrect syntax near '<'.

You can't teach an old mouse new clicks.

mr_mist
Grunnio

1870 Posts

Posted - 2005-06-20 : 04:28:31

With these lines

quote:

sum (Case processed_date when Datediff(m, Max(Tax_Period_Date), @L_Period) < 18 then 1 else 0 end) [Within 18 months] ,
Sum (Case processed_date when Datediff(m, Max(Tax_Period_Date), @L_Period) between 18 and 24 then 1 else 0 end) [18 - 24 months] ,
Sum (Case processed_date when Datediff(m, Max(Tax_Period_Date), @L_Period) between 25 and 36 then 1 else 0 end) [25 - 36 months] ,
Sum (Case processed_date when Datediff(m, Max(Tax_Period_Date), @L_Period) > 37 then 1 else 0 end) [36 months and greater]

I think you have processed_date in there when you don't need it.


sum (Case when Datediff(m, Max(Tax_Period_Date), @L_Period) < 18 then 1 else 0 end) [Within 18 months]		 ,
Sum (Case when Datediff(m, Max(Tax_Period_Date), @L_Period) between 18 and 24 then 1 else 0 end) [18 - 24 months] ,
Sum (Case when Datediff(m, Max(Tax_Period_Date), @L_Period) between 25 and 36 then 1 else 0 end) [25 - 36 months] ,
Sum (Case when Datediff(m, Max(Tax_Period_Date), @L_Period) > 37 then 1 else 0 end) [36 months and greater]

-------
Moo. :)

Richard Branson
Yak Posting Veteran

84 Posts

Posted - 2005-06-20 : 04:36:07

I tried that and it throws out the following err:

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

I suppose I could use derived tables but I thought this way would be much shorter

You can't teach an old mouse new clicks.

Bee-Z
Starting Member

6 Posts

Posted - 2005-06-20 : 09:41:43

the problem is the MAX(Tax_period_date) in the SUM(CASE)

sum (Case when Datediff(m, Max(Tax_Period_Date), @L_Period) < 18 then 1 else 0 end) [Within 18 months]

this would work without trouble:
sum (Case when Datediff(m, Tax_Period_Date, @L_Period) < 18 then 1 else 0 end) [Within 18 months]

but if you need to find the MAX(tax_period_date) first, I think you should first do a statement for that and then apply your initial statement on the result.

Richard Branson
Yak Posting Veteran

84 Posts

Posted - 2005-06-28 : 04:43:44

Thanks Bee-Z

I found another way around this but it's much longer and involves #temp-tables (which I don't like using).

With Bee-Z's solution it doesn't throw out any errors anymore but instead it's not distributing the numbers properly.
Instead of breaking up the total count picked up from

count(r.Vat_Nbr) Vat_Count

, it just takes the number and put's it in one of the groups ("within 18 months", "18 - 24 months",...).

This is what she looks like now:

Declare @L_Period datetime,
	@M_Tax_Period Datetime

Select @L_Period = max(Load_Period)
from bf_Load..vat_update

Select @M_Tax_Period = Max(Tax_Period_Date)
from Vat_Return_Adjusted

Select 	top 100 
	e.Enterprise_nbr			,
	e.Life_cycle_code			,
	count(r.Vat_Nbr) Vat_Count	,
	Sum (Case b.LIFE_STATUS_CODE When '' then 1 else 0 end) as [Active]			,
	Sum (Case b.LIFE_STATUS_CODE When 'S' then 1 else 0 end) as [S-SUSPEND]			,	
	Sum (Case b.LIFE_STATUS_CODE When 'Y' then 1 else 0 end) as [DE-REGISTERED]		,
	Sum (Case b.LIFE_STATUS_CODE When 'B' then 1 else 0 end) as [ESTATE]			,
	Sum (Case b.LIFE_STATUS_CODE When 'X' then 1 else 0 end) as [X-suspense]		,
	sum (Case when Datediff(m, @M_Tax_Period, @L_Period) < 18 then 1 else 0 end) [Within 18 months]		 ,
	Sum (Case when Datediff(m, @M_Tax_Period, @L_Period) between 18 and 24 then 1 else 0 end) [18 - 24 months] ,
	Sum (Case when Datediff(m, @M_Tax_Period, @L_Period) between 25 and 36 then 1 else 0 end) [25 - 36 months] ,
	Sum (Case when Datediff(m, @M_Tax_Period, @L_Period) > 37 then 1 else 0 end) [36 months and greater] 
from Enterprise	e
Inner Join Enterprise_External_Link el
	on e.enterprise_nbr = el.enterprise_nbr
Inner Join Representative_Tax_Rel r
	on el.external_nbr = r.tax_nbr	
Inner Join Representative re
	on r.Vat_Nbr = re.Representative_VAT_NBR
Inner Join Branch b
	on b.Representative_VAT_NBR = re.Representative_VAT_NBR
Where Life_cycle_code = 'acti'
Group by e.Enterprise_nbr, Life_cycle_code

Any ideas??

You can't teach an old mouse new clicks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources