Click here to Skip to main content
14,667,121 members
Rate this:
Please Sign up or sign in to vote.
See more:
I have following quantity table 

<pre>Item	QuantityRequired	MaxQuantity
 Item1	      200	               50
 Item2	      100	               30


Based on MaxQuantity column value, table rows has to get split like below,

Item	QuantityRequired
Item1	  50
Item1	  50
Item1	  50
Item1	  50
Item2	  30
Item2	  30
Item2	  30
Item2	  10


What I have tried:

I treid with union its not working.
Posted
Updated 24-Apr-20 6:15am
Comments
CHill60 24-Apr-20 11:18am
   
Show the code you tried

Rate this:
Please Sign up or sign in to vote.

Solution 2

I chose to use a recursive CTE (Common Table Expression) to do this, combined with another CTE
;with cte1 as
(
	select item, quantityrequired, maxquantity, 
	cast(QuantityRequired / maxquantity as integer) as wholes, QuantityRequired % MaxQuantity as partials
	from #test
)
,cte2 as 
(
    SELECT item, maxquantity, wholes, 'base ' as w from cte1
    UNION ALL
    SELECT item, maxquantity, wholes - 1, 'recur' as w
    FROM cte2 WHERE wholes > 1
)
SELECT item, MaxQuantity as QuantityRequired 
from cte2
union all
SELECT item, partials as QuantityRequired 
from cte1 where partials > 0
order by Item, QuantityRequired desc


Edit - meant to explain this before I submitted it!

The first CTE is determining how many rows I need, wholes is the number of rows where I can use the maximum quantity allowed (required / max), partialsis what is left over (required mod max)

The 2nd CTE generates all of the 'whole' rows needed - I included that column w to help you see what was happening (if you want to include it in the select) - a full explanation of recursive CTEs can be found at Recursive CTEs Explained - Essential SQL[^]

The final select takes all of those "whole" rows and unions with the left-over "partial" value for each item - if there is one. Note the use of Union all so the duplicate "whole" rows are not removed
   
v4
Comments
Member 14636607 25-Apr-20 3:50am
   
THANK YOU SIR
Rate this:
Please Sign up or sign in to vote.

Solution 3

Here's a solution with a single recursive CTE[^]:
WITH cte As
(
    SELECT
        Item,
        CASE
            WHEN QuantityRequired > MaxQuantity THEN MaxQuantity
            ELSE QuantityRequired
        END As QuantityRequired,
        QuantityRequired - MaxQuantity As RemainingQuantity,
        MaxQuantity
    FROM
        YourTable
    
    UNION ALL
    
    SELECT
        Item,
        CASE
            WHEN RemainingQuantity > MaxQuantity THEN MaxQuantity
            ELSE RemainingQuantity
        END As QuantityRequired,
        RemainingQuantity - MaxQuantity As RemainingQuantity,
        MaxQuantity
    FROM
        cte
    WHERE
        RemainingQuantity > 0
)
SELECT
    Item,
    QuantityRequired
FROM
    cte
ORDER BY
    Item
;
Output:
Item    QuantityRequired
------------------------
Item1	50
Item1	50
Item1	50
Item1	50

Item2	30
Item2	30
Item2	30
Item2	10
   
Comments
Member 14636607 25-Apr-20 3:50am
   
THANK YOU SIR

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100