while loop help - 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 2005 Forums

Transact-SQL (2005)

while loop help

Author

Topic

gtrain
Starting Member

7 Posts

Posted - 2010-08-04 : 03:19:10

Ok guys I am trying get some info by dates and having a little trouble with my statement, i basically want to change the year and month i am looking at, but leave the day as the first
ie 2008-01-01, 2008-02-01....2010-07-01, 2010-08-01

i think i am doing something wrong in converting to a date and i am not actually sure my loops will work, first time looking at sql while loops forgive me.

here is my code, it only results in
ended welds current welds Total
0 0 0

obviously 48 times
any help?

declare @yr int
declare @mth int
set @yr = 2007
set @mth = 1
while @yr<=2010
begin
while @mth<=12
begin

SELECTcount(case when expirydate <= @yr +'-'+ @mth +'-01' then linecode end)as [ended],
count(case when deliverydate <=@yr +'-'+ @mth +'-01' then linecode end)as [current welds],
count(case when deliverydate <=@yr +'-'+ @mth +'-01' then linecode end) 
-count(case when expirydate <=@yr +'-'+ @mth +'-01' then linecode end)as [Total]
FROM sometable 
where code ='xxx'
set @mth = @mth +1

	 end
		 set @mth = 1
	set @yr = @yr +1
end

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-08-04 : 04:49:19

Don't know what you want to do but:

@yr +'-'+ @mth +'-01'

You can't concatenate without converting @yr and @mth to varchar.

convert(varchar(4),@yr) +'-'+ convert(varchar(2),@mth) +'-01'

No, you're never too old to Yak'n'Roll if you're too young to die.

gtrain
Starting Member

7 Posts

Posted - 2010-08-04 : 05:31:32

Great that got my data out, and it basically runs the query 48 times, but is there anyway with out creating a temp table to read the data as one result set
ie

Col1 Col2 Col3
1 2 3
1 2 3
1 2 3

rather than

Col1 Col2 Col3
1 2 3
Col1 Col2 Col3
1 2 3
Col1 Col2 Col3
1 2 3

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-08-04 : 05:47:16

Maybe if you can give table structure,some sample data and wanted output then someone will provide a solution without a WHILE.
Maybe it is possible with some kind of GROUP BY...

Another way would be to build a SQL-String (using UNION) inside the WHILE and after that execute that dynamic sql.

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources