Help with a while loop

Question

Post reply

Help with a while loop

Turtlarn

SSC Veteran

Points: 221
More actions
June 24, 2008 at 4:57 am

#187495

Hi,
First of all I just want to mention that I first started with sql last week, so I'm kinda new to this.
My problem is that I want my query to return several "counts", eg:
declare @month int
set @month = 1
while @month <= 12
begin
select count(e.id)
from example1 as e
where (year(e.created_date) = 2008)
and (month(e.created_date) = @month)
set @month = @month + 1
end
This works well (if a bit slowly) in my ordinary Microsoft SQL Server Management Studio (2005), where it creates 12 fields (?) containing the information I want for each specific month. Now the trouble is that I can't get this query to work in SSRS (reporting services) where the query only displays the value for the first month. My guess would be that SSRS can't handle more than one field with a single name, in which case I would have to name every newly created field (for each successive lap in my loop) uniquely. I'm wondering if there is some way of doing this. I know that creating a Stored Procedure would seem great for this, but it's not possible to create one on the database in question.
Thankful for any answers and tips
Jonatan

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Kev Riley SSCrazy Eights Points: 9075 More actions · Answer 1

Jonatan,

do you mean you want all the 'counts' in one record set?

select

month(a.created_date), count(e.id)

from example1 as e

where year(a.created_date) = 2008

group by

month(a.created_date)

this will give you one record set with 12 records, one for each month.....

Kev

Turtlarn SSC Veteran Points: 221 More actions · Answer 2

Turtlarn

SSC Veteran

Points: 221

June 24, 2008 at 5:26 am

#833231

wow that easy... thanks a lot for your help!

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

You'll find that loops have problems like the one you just saw because they return multiple result sets. You also find that, comparitively speaking, they're horribly slow. Since you're brand new to SQL Server, let me run you though one of the basics...

How would you create a table with the numbers 1 to 11,000? Think about it, write your code to do it, and then go read the following article...

http://www.sqlservercentral.com/articles/TSQL/62867/

... don't get me wrong... the subject of that article is not a panacea for getting rid of all loops. But, it's the first step in realizing that loops are bad and 99.9% of all loops have a high speed, set based replacement. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Turtlarn SSC Veteran Points: 221 More actions · Answer 4

Cheers Jeff, really interesting article! My queries are notably faster now without the while loop however still a bit slow. The databases are quite enormous (and as easy to find your way in as a labyrinth I might add). They're not very well indexed either, and as I'm only a summer worker I'm not allowed to create things, only to extract information...

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

Thanks for the feedback, Jonatan. As a summer worker, though, you are allowed to make a suggestion here and there, aren't you?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Turtlarn SSC Veteran Points: 221 More actions · Answer 6

I am and I will ask them if there are any indexes which need to be updated to improve the speed of some of my queries. Problem might be that there are a lot of other users and an index will not always benefit everyone if I'm not mistaken?

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 7

That would be true... and, except for what may occur on Inserts, indexes don't harm another's query.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Turtlarn SSC Veteran Points: 221 More actions · Answer 8

Alright, then there's no problem. I'm working on a backup database, so no one's actually adding or removing anything on the tables