To cursor or not to cursor - 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 2008 Forums

Transact-SQL (2008)

To cursor or not to cursor

Author

Topic

oasis1
Starting Member

35 Posts

Posted - 2010-09-24 : 00:33:42

I am having a dilema and not sure how to approach this.

I have two tables one is

ID Total1
a 46
b 56
c 50
etc
The other

ID total2
1 5
2 3
3 2
etc

I have a total limit on table one of 100. I want to assign the total2 in the second table to the lowest total1 column until it meets or exceeds the next lowest total1 number. In this case above it would show

A 1 51
C 2 53
C 3 55

B would not be touched because it is 56 and C only got to 55. Any ideas? mahalo

sql-programmers
Posting Yak Master

190 Posts

Posted - 2010-09-24 : 07:07:26

Check this script,

Create table A1(ID char(2),Total1 int)
insert into A1 values('a', 46)
insert into A1 values('b', 56)
insert into A1 values('c', 50)

Create table A2(ID char(2),Total2 int)
insert into A2 values('1', 5)
insert into A2 values('2', 3)
insert into A2 values('3', 2)

select *,row_number() over (order by total1) as RowId into #A1 from A1
select * into #temp from A2
Create table #Final(ID char(2),ID1 char(2),Total int)
DECLARE @ID1 char(2), @Total1 int ,@Row int
DECLARE @ID2 char(2), @Total2 int ,@Flag int,@Max1 int,@Max2 int
set @Flag=0
declare @C as int

DECLARE MY_CURSOR Cursor FOR
Select ID, Total1,RowID From #A1 order by Total1
Open My_Cursor
Fetch NEXT FROM MY_Cursor INTO @ID1, @Total1, @Row
WHILE @@FETCH_STATUS = 0
BEGIN

set @C=(select count(*) from #temp)
if @C<>0
begin
set @Max1=coalesce((select MAX(Total1) from #A1 where Total1>@Total1 and RowID= @Row+1),0)

set @ID2=(select top 1 ID from #temp order by Total2 desc)
set @Total2=(select top 1 Total2 from #temp order by Total2 desc)
insert into #Final values(@ID1,@ID2,@Total1+@Total2)
delete from #temp where ID=@ID2

set @Max2=coalesce((select Total from #Final where id=@ID1),0)

if @Max1<>0 and @Max1>@Max2
begin
WHILE @Flag = 0
BEGIN
set @C=(select count(*) from #temp)
if @C=0 or @Max1<=@Max2
begin
set @Flag = 1
end
else
begin
set @ID2=(select top 1 ID from #temp order by Total2 desc)
set @Total2=(select top 1 Total2 from #temp order by Total2 desc)
set @Total1=(select top 1 Total from #Final where Id=@ID1 order by Total desc)
insert into #Final values(@ID1,@ID2,@Total1+@Total2)
delete from #temp where ID=@ID2

set @Max2=coalesce((select max(Total) from #Final where id=@ID1),0)

end
END
end
end
FETCH NEXT FROM MY_CURSOR INTO @ID1, @Total1, @Row
END
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR

select * from #Final

drop table #temp
drop table #Final
drop table #A1

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources