I got the result
DECLARE @CustOrder TABLE(
ItemSize int,
Product varchar(20),
Qty INT, lotQty int)
INSERT @CustOrder
VALUES
(
'8','Addidas Shoe',30,25
)
INSERT @CustOrder
VALUES
(
'9','Addidas Shoe',15,25
)
INSERT @CustOrder
VALUES
(
'10','Addidas Shoe',50,25
)
INSERT @CustOrder
VALUES
(
'7','Puma Shoe',40,30
)
INSERT @CustOrder
VALUES
(
'8','Puma Shoe',60,30
)
declare @Lot table (LotNo varchar(20),Seq int,ItemSize int, Product varchar(20), Qty int)
declare @RemQty int , @curQty int, @curLotQty INT
SET @RemQty=0;
declare @ItemSize int, @Product varchar(20), @Qty int, @lotQty int, @oldProduct varchar(20)
declare @lno int
set @lno = 0
declare @seq int
set @seq = 1
declare table_cursor cursor local fast_forward for
select
ItemSize, Product, Qty, lotQty
from @CustOrder
order by Product, Itemsize
open table_cursor
while 1 = 1
begin
if @RemQty <= 0
begin
fetch table_cursor into @ItemSize, @Product, @Qty, @lotQty
if @@fetch_status <> 0 break
if @oldProduct <> @Product AND @oldProduct IS NOT NULL
set @lno = @lno + 1
if @oldProduct <> @Product or @oldProduct is null
begin
select @oldProduct = @Product
select @RemQty = 0 set @seq = 1
END
end
if @RemQty < 0 and abs(@RemQty) < @lotQty
BEGIN
select @curLotQty = abs(@RemQty)
set @seq = @seq + 1
END
ELSE
select @curLotQty = @lotQty
if @RemQty > 0 and @RemQty < @Qty
BEGIN
select @curQty = @RemQty
set @lno = @lno + 1
set @seq = 1
END
ELSE
select @curQty = @Qty
select @RemQty = @curQty - @curLotQty
insert into @Lot
SELECT '100'+CAST(@lno as varchar), @seq, @ItemSize, @Product, case when @curQty > @curLotQty then @curLotQty else @curQty END
end
close table_cursor
deallocate table_cursor
SELECT LotNo [Lot No.], Seq [Sl. No], Product, ItemSize [Size], Qty from @Lot order by Product, Itemsize