T-SQL 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 2000 Forums

Transact-SQL (2000)

T-SQL help

Author

Topic

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2005-11-23 : 19:31:28

create table #stock
(
[id] int,
stockcode varchar(10),
startrange int,
endrange int,
nextavailablenumber int
)

INSERT INTO #stock values (1,'A',1,20,1)
INSERT INTO #stock values (2,'A',201,250,201)
INSERT INTO #stock values (3,'A',401,500,401)

--suppose i picked stock 10 stock
Update #stock SET nextavailablenumber=11 where [id]=1

DROP TABLE #stock

now i need to get 80 stock items
what will be query so that it gives me
range 11-20 from id=1 and set next nextavailablenumber=21
range 201-251 from id=2 and set next nextavailablenumber=251
range 401-420 from id=3 and set next nextavailablenumber=421

thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-11-23 : 20:02:59

is this what you want ?

update #stock set nextavailablenumber = endrange + 1

-----------------
[KH]

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2005-11-23 : 20:22:03

nope!

mk_garg

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2005-11-23 : 23:14:37

basicaaly i should get foloowing results
id,startrange,endrange,nextavailablenumber
1,11,20,21
2,201,251,251
3,401,420,421

Thanks

mk_garg

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-11-23 : 23:28:49

quote:
basicaaly i should get foloowing results
id,startrange,endrange,nextavailablenumber
1,11,20,21
2,201,251,251
3,401,420,421

How do you get endrange of 251 and 420 from the data ?

INSERT INTO #stock values (1,'A',1,20,11)
INSERT INTO #stock values (2,'A',201,250,201)
INSERT INTO #stock values (3,'A',401,500,401)

Sorry i don't quite understand your logic here. You want to update the table or query from the table ?

-----------------
[KH]

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2005-11-24 : 00:00:32

Allright i updated data in original post.

For id 1:-
Intially i had start:1 end :20 & nextavailablenumber:1
Then i took 10 itmes for id 1 and now my nextavailablenumber for id 1 is 11.

Here is my requirement:-
Now i need 80 items.
1. id 1 has 10 items available(11-20) i need to pick. now i am short of 70 items and i got to
2. id 2 which has 50 itmes from (201-250). i need to pick that now i am short of 20 items. so i got to
3. id 3 which has enough items so i pick 20 item.

I hope now u understand what i am trying to achieve here.

Thanks

mk_garg

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-11-24 : 00:09:14

you can use cursor to achieve this. Unless you want to avoid using cursor

-----------------
[KH]

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2005-11-24 : 00:30:25

I am looking for alternative to cursor.

mk_garg

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2005-11-24 : 01:10:38

This should get you started...


declare @Vol int
set @vol =80

Update s SET 
		nextavailablenumber = case when  endrange-startrange < @vol then endrange +1 
						when  endrange-startrange > @vol then endrange-@vol
							else  0 end
, @Vol =   @vol-(endrange-startrange) + endrange-startrange
from  #stock s
select * from #stock

DavidM

Intelligent Design is NOT science.

A front-end is something that tries to violate a back-end.

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2005-11-24 : 23:13:13

quote:
Originally posted by byrmol

This should get you started...

declare @Vol int
set @vol =80

Update s SET
nextavailablenumber = case when endrange-startrange < @vol then endrange +1
when endrange-startrange > @vol then endrange-@vol
else 0 end
, @Vol = @vol-(endrange-startrange) + endrange-startrange
from #stock s
select * from #stock

DavidM

Intelligent Design is NOT science.

A front-end is something that tries to violate a back-end.

if i change @vol to 30 then this code should update id 1 & 2 not 3 as it is doing now.

Thanks

mk_garg

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources