query explaination - 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

General SQL Server Forums

New to SQL Server Programming

query explaination

Author

Topic

tadin
Yak Posting Veteran

63 Posts

Posted - 2007-04-19 : 16:48:24

i've the below query, i was wondering if someone could explain me what's goin in this query, and what's the purpose of this query. Since, i'm lacking detail knowledge and usability of such query. In what areas such query could be utilized.Open for any comments. I like to thank in advance.

declare @counter int

set @counter = 1990;
if exists (select * from tempdb..sysobjects where name = '##tempsales')
begin
drop table ##tempsales
end
select @counter as OrdYear, count(*) as yearQty into ##tempsales from sales where 1=2

while (@counter < 1999)
begin
insert into ##tempsales
select @counter, count(*) from sales
where year(ord_date) = @counter
set @counter = @counter + 1
end
select * from ##tempsales

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2007-04-20 : 09:11:40

It's returning the number of sales per year from the table [sales] in a fairly convoluted way!

At first glance, it looks like you could replace this with:

DECLARE @year SMALLINT
SET @year = 1990
SELECT YEAR(s.ord_date) AS OrdYear, COUNT(*) AS yearQty
FROM dbo.sales AS s
WHERE YEAR(s.ord_date) < @year
GROUP BY YEAR(s.ord_date)
ORDER BY YEAR(s.ord_date)

Mark

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-04-20 : 12:31:27

It is checking to see if the golbal temp table exists (##tempsales). If Exists is drops the table and re-creates the table by doing a SELECT INTO.

Then it loops from 1990 to 1998 selecting the count of sales per year from the sales table inserting the result into the global temp table. Finally, it selects all those counts.

quote:

Originally posted by mwjdavidson

SELECT YEAR(s.ord_date) AS OrdYear, COUNT(*) AS yearQty
FROM dbo.sales AS s
WHERE YEAR(s.ord_date) >= 1990
AND YEAR(s.ord_date) < 1999
GROUP BY YEAR(s.ord_date)
ORDER BY YEAR(s.ord_date)

Mark

-Ryan

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-04-20 : 13:47:59

yep ... code written by someone who doesn't know about GROUP BY ...... scary stuff. replace with a simple
SELECT..GROUP BY as demonstrated by mwjdavidson.

I have an entire database written by someone who doesn't know about derived tables or outer joins . temp tables with UPDATES, loops, cursors, etc -- all instead of simple SELECTs.

- Jeff
http://weblogs.sqlteam.com/JeffS

X002548
Not Just a Number

15586 Posts

Posted - 2007-04-20 : 13:50:49

[code]
if exists (select * from tempdb..sysobjects where name = '##tempsales')
begin
drop table ##tempsales
end
[/code]

Well this will never work either

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources