Click here to Skip to main content
15,444,353 members

Comments by susanna.floora (Top 3 by date)

susanna.floora 27-Aug-13 9:07am View     CRLF
Deleted
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 sql 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
susanna.floora 27-Aug-13 8:31am View    
Deleted
pls help me to achieve the result.
susanna.floora 27-Aug-13 8:29am View     CRLF
Deleted
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