SQL Script to split data rows into multiple rows based on a condition

I wish to split an order to generate multiple work orders depending on the Optimum Lot开发者_如何学运维 Quantity (OLQ) meaning the sum the quantity in a work order would not exceed the OLQ.

Here is my order Table sample data:

Order   Item    Product Qty
OR-01   I-001   PRD-01  70
OR-01   I-001   PRD-02  15
OR-01   I-001   PRD-03  55

The OLQ for this order is 30 and therefore the sum of the qty of work order should not exceed 30. But the work order can has multiple products of the same item (item is the parent for product).

Here is how I wish to split this order to create work orders on the basis of OLQ:

Order   WorkOrd Seq Item    Product Qty
OR-01   WO-0001 001 I-001   PRD-01  30
OR-01   WO-0002 001 I-001   PRD-01  30
OR-01   WO-0003 001 I-001   PRD-01  10
OR-01   WO-0003 002 I-001   PRD-02  15
OR-01   WO-0003 003 I-001   PRD-03  5
OR-01   WO-0004 001 I-001   PRD-03  30
OR-01   WO-0005 001 I-001   PRD-03  20

Note that WO-0003 has three products with 10, 15 and 5 amounting to 30. Also note that the last Work order WO-0005 has only 20 qty (which is the remaining).

In the attachment I have highlighted the work orders in different colors for easy understanding.

Kindly help me to achieve this.

Thanks in advance.

Since item made no sense here, i removed it from the sql.

declare @t table(order1 varchar(5), product varchar(6), seqcheck int)

insert @t values('OR-01','PRD-01',70) 
insert @t values('OR-01','PRD-02',15) 
insert @t values('OR-01','PRD-03',55) 

;with 
b as
(
select order1, product, seqcheck - 30 seqcheck, case when seqcheck > 30 then 30 else seqcheck end quantity, 1 seq, 1 workorder
from @t
where product = 'PRD-01'
union all
select order1, product, seqcheck - 30, case when seqcheck > 30 then 30 else seqcheck end quantity, 1 seq, workorder + 1
from b
where seqcheck > 0
union all
select t.order1, t.product, t.seqcheck + b.seqcheck, case when t.seqcheck + b.seqcheck >= 0 then -b.seqcheck else t.seqcheck end quantity, seq + 1, workorder
from b join @t t on 
cast(stuff(b.product, 1,4, '') as int) = cast(stuff(t.product, 1,4, '') as int) - 1
where b.seqcheck <= 0
)
select order1 [order], 'WO-' + left('000'+ cast(workorder as varchar(4)), 4) workord, right('000'+ cast(seq as varchar(3)), 3) seq, product, quantity from b
option(MAXRECURSION 0)

Result:

order workord seq  product quantity
----- ------- ---- ------- -----------
OR-01 WO-0001 001  PRD-01  30
OR-01 WO-0002 001  PRD-01  30
OR-01 WO-0003 001  PRD-01  10
OR-01 WO-0003 002  PRD-02  15
OR-01 WO-0003 003  PRD-03  5
OR-01 WO-0004 001  PRD-03  30
OR-01 WO-0005 001  PRD-03  20

I dont like working with cursors, but sometimes they are handy.

Here you go:

-- Test data
declare @orders table 
    (Order_a varchar(20),
     Item varchar(20),
     Product varchar(20),
     Qty int)

insert into @orders
select 'OR-01',   'I-001',   'PRD-01',  70
union all
select 'OR-01',   'I-001',   'PRD-02',  15
union all
select 'OR-01',   'I-001',   'PRD-03',  55

-- End test data

declare @workorders table 
    (Order_a varchar(20),
     WorkOrd varchar(20),
     Seq int,
     Item varchar(20),
     Product varchar(20),
     Qty int)


declare @olq int
set @olq = 30

declare @qty_left int
set @qty_left = @olq

declare @wo int
set @wo = 1

declare @seq int
set @seq = 1

declare @Order_a varchar(20)  
  ,@Item varchar(20)
  ,@Product varchar(20)
  ,@Qty int

-- Declare and set the cursor    
declare qtycursor cursor for    
select Order_a  
  ,Item  
  ,Product  
  ,Qty  
from @orders

open qtycursor    
fetch next from qtycursor into @Order_a, @Item, @Product, @Qty    
while @@fetch_status = 0     
begin    

    while @Qty <> 0
    begin
        if @Qty < @qty_left
        begin
            insert into @workorders
            select @Order_a, 'WO-'+CAST(@wo as varchar), @seq, @Item, @Product, @Qty

            set @seq = @seq + 1 
            set @qty_left = @qty_left - @Qty
            set @Qty = 0
        end
        else
        begin
            insert into @workorders
            select @Order_a, 'WO-'+CAST(@wo as varchar), @seq, @Item, @Product, @qty_left

            set @Qty = @Qty - @qty_left
            if @Qty > 0
            begin
                set @seq = 1    
            end
            set @wo = @wo + 1
            set @qty_left = @olq

        end
    end




fetch next from qtycursor into @Order_a, @Item, @Product, @Qty    
end    
close qtycursor    
deallocate qtycursor    

select * from @workorders