Comments by susanna.floora (Top 3 by date)

pls help me to achieve the result.

i tried using sql cursor it works for single lot qty. i dont know how to do for multiple Lot qty.I'll post my query
declare @Custorders table
( ItemSize int,
Product varchar(20),
Qty INT, lotQty int)
insert into @Custorders
select '8','Addidas Shoe',30,25
union all
select '9','Addidas Shoe',15,25
union all
select '10','Addidas Shoe',50,25
union all
select '7','Puma Shoe',40,30
union all
select '8','Puma Shoe',60,30
declare @Lot table
(
LotNo varchar(20),
Seq int,
ItemSize int,
Product varchar(20),
Qty int)
declare @Item varchar(20)
,@Product varchar(20)
,@Qty INT
declare @lno int
set @lno = 0
declare @seq int
set @seq = 1
-- Declare and set the cursor
declare qtycursor cursor for
select ItemSize
,Product
,Qty
from @Custorders
declare @olq INT
SET @olq=25
declare @qty_left int
set @qty_left = @olq
open qtycursor
fetch next from qtycursor into @Item, @Product, @Qty
while @@fetch_status = 0
begin
while @Qty <> 0
begin
if @Qty < @qty_left
begin
insert into @Lot
select '100'+CAST(@lno as varchar), @seq, @Item, @Product, @Qty
set @seq = @seq + 1
set @qty_left = @qty_left - @Qty
set @Qty = 0
end
else
begin
insert into @Lot
select '100'+CAST(@lno as varchar), @seq, @Item, @Product, @qty_left

set @Qty = @Qty - @qty_left
if @Qty > 0
begin
set @seq = 1
end
set @lno = @lno + 1
set @qty_left = @olq
end
end
fetch next from qtycursor into @Item, @Product, @Qty
end
close qtycursor
deallocate qtycursor

SELECT LotNo [Lot No.], Seq [Sl. No.], Product, ItemSize [Size], Qty from @Lot