15,937,779 members
See more:
I want to split a customer order depending on Lot qty.

Here is Customer Order table.
```Product           Size    Purch. Qty    Lot Qty
-----------------------------------------------
Puma Shoe          7         40         30
Puma Shoe          8         60         30
```

I have 2 products Addidas shoe to be splitted in to multiple rows depending on its Lot qty 25 and Puma shoe to be splitted in to multiple rows depending on its Lot qty 30 as shown below.
```Lot No	Sl. No	Product	Size	Qty
1000	1	Addidas Shoe	8	25
1001	1	Addidas Shoe	8	5
1001	2	Addidas Shoe	9	15
1001	3	Addidas Shoe	10	5
1002	1	Addidas Shoe	10	25
1003	1	Addidas Shoe	10	20
1004	1	Puma Shoe	7	30
1005	1	Puma Shoe	7	10
1005	2	Puma Shoe	8	20
1006	1	Puma Shoe	8	30
1007	1	Puma Shoe	8	10```

Posted
Updated 26-Aug-13 21:39pm
v2
gvprabu 27-Aug-13 8:06am
Wht is Lot No and Sl.No column values? any logic already u tried for this..?

## Solution 1

I got the result
DECLARE @CustOrder TABLE(
ItemSize int,
Product varchar(20),
Qty INT, lotQty int)
INSERT @CustOrder
VALUES
(
)
INSERT @CustOrder
VALUES
(
)
INSERT @CustOrder
VALUES
(
)
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