Colaboración Cursores

El código para lo que se requiere según la consulta

Se crearon las tablas y se usó cursor con variables tipo tabla para la solución.



Create Database Beers
go

Use Beers
go
— Tabla Beer
Create table BeerSale
(ID int, PubName nvarchar(50), BeerSold int,
constraint BeerSalePK Primary key (ID))
go
Insert into BeerSale values
(1,’U Sviraka’,37),(2,’U Hovada’,15), (3,’U Dive Svine’,43)
go
Create table BoxStock
(ID int, BoxStockName nvarchar(50), Capacity int,
constraint BoxStockPK Primary key (ID))
go
Insert into BoxStock values
(1,’P-001′,20),(2,’P-002′,20),(3,’P-003′,20),
(4,’P-004′,20),(5,’P-005′,20),(6,’P-006′,20),
(7,’P-007′,20),(8,’P-008′,20),(9,’P-009′,20),
(10,’P-010′,20)
go
— Usando un cursor con variable tipo tabla
Declare @Resultado Table (PubName nvarchar(50), BoxStockName nvarchar(50),BeerRemainder int)
Declare @BoxStockNameValorFinal int
Set @BoxStockNameValorFinal = 0
Declare @BoxStockNameActual nchar(5)
Set @BoxStockNameActual = »
Declare cursorBeerSale cursor for select * from BeerSale
Open cursorBeerSale
Declare @ID int, @PubName nvarchar(50), @BeerSold int
Fetch cursorBeerSale into @ID, @PubName, @BeerSold
While (@@FETCH_STATUS = 0)
Begin
— Análisis para insertar en la variable tipo tabla @Resultado
— ============= Para hallar el BeerRemainder ================= ———–
Declare @Remainder int
Set @Remainder = @BeerSold
If @Remainder < 20
Begin
Set @BeerSold = @Remainder
— ============ Para hallar el BoxStockName ============ ———–
Set @BoxStockNameValorFinal = (select COUNT(R.PubName) + 1 from @Resultado As R)
if @BoxStockNameValorFinal<10
Begin
set @BoxStockNameActual = ‘P-‘+’00’+Trim(Str(@BoxStockNameValorFinal))
End
Else
Begin
if @BoxStockNameValorFinal<100
Begin
set @BoxStockNameActual = ‘P-‘+’0’+Trim(Str(@BoxStockNameValorFinal))
End
Else
Begin
set @BoxStockNameActual = ‘P-‘+Trim(Str(@BoxStockNameValorFinal))
End
End
— ============ Final Para hallar el BoxStockName ============ ———–
insert into @Resultado values (@PubName, @BoxStockNameActual, @BeerSold )
Set @BoxStockNameValorFinal += 1
End
Else
Begin
— Calcular cuantas veces se insertará restando 20
Declare @Veces int, @RestanteMayor20 int
Set @Veces = FLOOR(@BeerSold/20)
Declare @Bucle int
Set @Bucle = 1
While (@Bucle <= @Veces)
Begin
— ============ Para hallar el BoxStockName ============ ———–
Set @BoxStockNameValorFinal = (select COUNT(R.PubName) + 1 from @Resultado As R)
if @BoxStockNameValorFinal<10
Begin
set @BoxStockNameActual = ‘P-‘+’00’+Trim(Str(@BoxStockNameValorFinal))
End
Else
Begin
if @BoxStockNameValorFinal<100
Begin
set @BoxStockNameActual = ‘P-‘+’0’+Trim(Str(@BoxStockNameValorFinal))
End
Else
Begin
set @BoxStockNameActual = ‘P-‘+Trim(Str(@BoxStockNameValorFinal))
End
End
— ============ Final Para hallar el BoxStockName ============ ———–
insert into @Resultado values (@PubName, @BoxStockNameActual, 20)
Set @Bucle +=1
End
Set @RestanteMayor20 = @BeerSold – @Veces * 20
— ============ Para hallar el BoxStockName ============ ———–
Set @BoxStockNameValorFinal = (select COUNT(R.PubName) + 1 from @Resultado As R)
if @BoxStockNameValorFinal<10
Begin
set @BoxStockNameActual = ‘P-‘+’00’+Trim(Str(@BoxStockNameValorFinal))
End
Else
Begin
if @BoxStockNameValorFinal<100
Begin
set @BoxStockNameActual = ‘P-‘+’0’+Trim(Str(@BoxStockNameValorFinal))
End
Else
Begin
set @BoxStockNameActual = ‘P-‘+Trim(Str(@BoxStockNameValorFinal))
End
End
— ============ Final Para hallar el BoxStockName ============ ———–
insert into @Resultado values (@PubName, @BoxStockNameActual, @RestanteMayor20)
Set @BoxStockNameValorFinal += 1
End
Fetch cursorBeerSale into @ID, @PubName, @BeerSold
End
Close cursorBeerSale
Deallocate cursorBeerSale
select * from @Resultado
go



Resultado: