Another simple looking question. Incrementing.

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

SQL Server Development (2000)

Another simple looking question. Incrementing.

Author

Topic

Tim_Field
Yak Posting Veteran

87 Posts

Posted - 2005-10-11 : 08:23:28

/*
I'd like to know how to do this for the next 6 rows...
Basically this is a cut down version of an insert statement. How do I get the year to -1 each time.
Obviously my code doesn't work!!!
*/

declare @todaysyear int
select @todaysyear = 2005
declare @yeartable table (myyear int)

insert into @yeartable
(myyear)
values
(@todaysyear = @todaysyear - 1)

Tim_Field
Yak Posting Veteran

87 Posts

Posted - 2005-10-11 : 08:26:26

Oh yeah, forgot to mention, I can obviously use a "for" loop but I'd rather not go this route as I'll be selecting from another table in my final insert.

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2005-10-11 : 08:29:25

declare @todaysyear int, @x as int
select @todaysyear = 2005, @x=0
declare @yeartable table (myyear int)

while @x < 6
BEGIN
insert into @yeartable (myyear)
values (@todaysyear-@x)
set @x = @x + 1
END

select * from @yeartable

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

Tim_Field
Yak Posting Veteran

87 Posts

Posted - 2005-10-11 : 08:33:36

yep that's what I meant I don't want to use a for \ while loop etc.
Maybe I'm thinking this through too much!

I suspect that creating an identity column that starts at 2005 in a temporary table may work (somehow!).

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2005-10-11 : 08:36:18

declare @todaysyear int, @y as int
select @todaysyear = 2005, @y=0
declare @yeartable table (myyear int)
declare @xtable table (x int)

while @y < 10
begin
insert into @xtable values (@y)
set @y=@y+1
end

insert into @yeartable (myyear)
select top 3 @todaysyear - x from @xtable

select * from @yeartable

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

Tim_Field
Yak Posting Veteran

87 Posts

Posted - 2005-10-11 : 08:55:44

Thanks jhocutt... I have a few ideas now. Appreciate the help.

hmmm annoyingly I can't do this!.

declare @yearstable table (myyear_id int identity(@acad_year_to_use, 1), myyear int, myyear_minus_1 int)

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2005-10-11 : 13:11:27

Tim, check out the technique that Graz writes about in his article Creating a Sequential Record Number Field. It sounds like just what you're trying to accomplish. Using an INSERT ... SELECT, and a - instead of + and you should be good to go.

---------------------------
EmeraldCityDomains.com

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2005-10-11 : 13:51:04

Very cool AjarnMark, Thanks for the tip.

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

rfrancisco
Yak Posting Veteran

95 Posts

Posted - 2005-10-11 : 16:01:25

An alternative is to use a table-valued user-defined function that will return a table containing the years you want. Here's a link to such a function that you can use:

http://www.sql-server-helper.com/functions/integer-table.aspx

In that link, there are 2 versions of the same function. You can use the one where you can specify the starting value and the ending value. Then you can use this function just like an ordinary table.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-10-12 : 08:08:11

This is kinda dumb, but it meets your requirement for not using a loop:

declare @yeartable table (myyear int)
declare @lastyear int
select @lastyear = 2006

insert into @yeartable select 2005 from (select top 6 1 a from sysobjects) a

update @yeartable
set @lastyear = myyear = @lastyear - 1

select * from @yeartable

Jay White

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources