multiple use of case parts - 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)

multiple use of case parts

Author

Topic

callahan77
Starting Member

2 Posts

Posted - 2006-02-02 : 10:57:36

Hi all!

I hope anybody can help me

I try to make a query that groups and calculate lots of data

simple example:
SELECT name,
A1 = SUM(CASE WHEN TEMP IN (1,2,3,4,5) AND qtime < 5 THEN 1 ELSE 0 END)
A2 = SUM(CASE WHEN TEMP IN (1,2,3,4,5) AND qtime > 10 THEN 1 ELSE 0 END)
A_All = SUM(CASE WHEN TEMP IN (1,2,3,4,5) AND qtime < 5 THEN 1 ELSE 0 END) + SUM(CASE WHEN TEMP IN (1,2,3,4,5) AND qtime > 10 THEN 1 ELSE 0 END)
FROM tabelle
GROUP BY name;

So you can see that I need always the same part of the case statement.
??? Is there any possibility to make this more comfortable???

Thanks a lot for your answers!!!

Norwich
Posting Yak Master

158 Posts

Posted - 2006-02-02 : 11:58:29

Is the query working?

If I understand you correctly, you just want to make your query shorter.
If it's working then you could go the dynamic query route ie.:


Declare @case varchar(100),
		@Sql Varchar (400)

Set @case = 'SUM(CASE WHEN TEMP IN (1,2,3,4,5) AND qtime'

Set @Sql =	'SELECT name,
			A1 = ' + @case + '< 5 THEN 1 ELSE 0 END)	,
			A2 = ' + @case + '> 10 THEN 1 ELSE 0 END)	,
			A_All = ' + @case + ' < 5 THEN 1 ELSE 0 END) + '
					  + @case + ' > 10 THEN 1 ELSE 0 END) 
			FROM tabelle
			GROUP BY name'

Print @Sql
--Execute (@SQL)

but that would be overkill

It Doesn't even make the query shorter!

N

The revolution won't be televised!

callahan77
Starting Member

2 Posts

Posted - 2006-02-03 : 02:02:27

Thanks for your hint!

Yes - The query works!
i only want to make the query shorter and more confortable to modify, because like in the example above, my whole query has 13.000 characters and this is very hard to modify.

So i try your way, maybe it is shorter.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-03 : 03:03:11

Are you looking for Dynamic Crosstab?
http://weblogs.sqlteam.com/jeffs/archive/2005/05.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources