CODEWITHSUNDEEP
sqlsql-serversql-server-2005
Susanna Floora
Susanna Floora

Reputation: 75

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

I want to split a customer order depending on Lot qty.

Here is Customer Order table.

Product           Size    Purch. Qty    Lot Qty 
-----------------------------------------------
Addidas Shoe       8         30         25  
Addidas Shoe       9         15         25
Addidas Shoe      10         50         25
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

Please help me to get this. I am using Sql server 2005.

Upvotes: 1

Views: 1925

Answers (1)

roman
roman

Reputation: 117420

I'm sure this will not give me many upvotes because of it's complexity, but still this is interesting task. I think it could be done by cursor approach, but it's also possible with recursive CTE. I'll try to make it as readable as possible, precalculating values with outer apply surely helps here.

I've modified you schema a bit. I think you have to store Lot Qty in a different table, something like:

create table Products ([Product] varchar(12), [Lot Qty] int);
insert into Products
values
('Addidas Shoe', 25),
('Puma Shoe', 30);

You could still store Lot Qty in your Order table, but it's not normalized. As for the question, I think recursive CTE can help you here:

with cte1 as (
   select
       row_number() over(partition by c.[Product] order by c.size) as row_num,
       c.*,
       P.[Lot Qty]
   from CustOrder as c
       inner join Products as P on P.[Product] = c.Product
), cte2 as (
    select
        c.Product, c.Size,
        0 as [Lot No],
        1 as [Sl. No],
        c.[Purch Qty] - c.[Lot Qty] as [Rem Qty],
        case when c.[Purch Qty] > c.[Lot Qty] then c.[Lot Qty] else c.[Purch Qty] end as Qty,
        case when c.[Purch Qty] > c.[Lot Qty] then c.row_num else c.row_num + 1 end as row_num2,
        c.[Lot Qty]
    from cte1 as c
    where c.row_num = 1

    union all

    select
        c.Product, c.Size,
        CALC.[Lot No],
        CALC.[Sl. No],
        CALC.[Purch Qty] - CALC.[Lot Qty] as [Rem Qty],
        case when CALC.[Purch Qty] > CALC.[Lot Qty] then CALC.[Lot Qty] else CALC.[Purch Qty] end as Qty,
        case when CALC.[Purch Qty] > CALC.[Lot Qty] then c.row_num else c.row_num + 1 end as row_num2,
        c.[Lot Qty]
    from cte1 as c
        inner join cte2 as c2 on c2.row_num2 = c.row_num and c2.Product = c.product
        outer apply (
             select
                 case when c2.[Rem Qty] < 0 then c2.[Lot No] else c2.[Lot No] + 1 end as [Lot No],
                 case when c2.[Rem Qty] < 0 then c2.[Sl. No] + 1 else 1 end as [Sl. No],
                 case when c2.[Rem Qty] < 0 and abs(c2.[Rem Qty]) < c.[Lot Qty] then abs(c2.[Rem Qty]) else c.[Lot Qty] end as [Lot Qty],
                 case when c2.[Rem Qty] > 0 and c2.[Rem Qty] < c.[Purch Qty] then c2.[Rem Qty] else c.[Purch Qty] end as [Purch Qty]
        ) as CALC

)
select
    999 + dense_rank() over(order by c.Product, c.[Lot No]) as [Lot No],
    c.[Sl. No],
    c.Product, c.Size, c.Qty
from cte2 as c
order by product, [Lot No]

sql fiddle demo

cursor-based solution:

declare @Lot table (ItemSize int, Product varchar(20), Qty int)

declare @RemQty int = 0, @curQty int, @curLotQty int
declare @ItemSize int, @Product varchar(20), @Qty int, @lotQty int, @oldProduct varchar(20)

declare table_cursor cursor local fast_forward for    
    select
       ItemSize, Product, Qty, lotQty
    from Custorders
    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 or @oldProduct is null
        begin
            select @oldProduct = @Product
            select @RemQty = 0
        end
    end

    if @RemQty < 0 and abs(@RemQty) < @lotQty
        select @curLotQty = abs(@RemQty)
    else
        select @curLotQty = @lotQty

    if @RemQty > 0 and @RemQty < @Qty
        select @curQty = @RemQty
    else
        select @curQty = @Qty

    select @RemQty = @curQty - @curLotQty

    insert into @Lot 
    select @ItemSize, @Product, case when @curQty > @curLotQty then @curLotQty else @curQty end
end
close table_cursor
deallocate table_cursor

select * from @Lot order by Product, Itemsize

sql fiddle demo

Upvotes: 2

Related Questions