Sql How To Split Quantity Into Multiple Rows Based On Quantity On Target Source

I have two table where i want to join and show quantity with details. table are join with ITM,DIA , and total Qty is equal in both table on ITM/DIA combination I want to split tabl

Solution 1:

You simply need to explode quantities in units both for table1 and table2 and then couple them side by side. Pay attention to FN_NUMBERS(n), it is a function that returns only one column with numbers from 1 to n, you need it in you database, there are many ways to do it, just google for "tally tables" or look here. I use the following:

CREATEFUNCTIONFN_NUMBERS(
     @MAX INT
)
RETURNS @NTABLE (N INT NOT NULL PRIMARY KEY)  
BEGINWITHPass0as (select '1' as C union all select '1'),       --2rowsPass1as (select '1' as C from Pass0 as A, Pass0 as B),--4rowsPass2as (select '1' as C from Pass1 as A, Pass1 as B),--16rowsPass3as (select '1' as C from Pass2 as A, Pass2 as B),--256rowsPass4as (select TOP (@MAX) '1' as C from Pass3 as A, Pass3 as B)    --65536rows
       ,Tallyas (select TOP (@MAX) '1' as C from Pass4 as A, Pass2 as B, Pass1 as C)  --4194304rows--,Tallyas (select TOP (@MAX) '1' as C from Pass4 as A, Pass3 as B)               --16777216rows--,Tallyas (select TOP (@MAX) '1' as C from Pass4 as A, Pass4 as B)               --4294836225rowsINSERTINTO @NSELECTTOP (@MAX) ROW_NUMBER() OVER(ORDER BY C) ASNFROMTallyRETURNEND

Back to the sql..

;with
t1 as (
    select *, ROW_NUMBER() over (partition by itm,dia orderby loc,id) rn      
    from table1 t1
    join FN_NUMBERS(500) on n<=t1.qty
),
t2 as (
    select *, ROW_NUMBER() over (partition by itm,dia orderby nta) rn      
    from table2 t2
    join FN_NUMBERS(500) on n<=t2.qty
),
t3 as (
    select t1.itm, t1.dia, t1.loc, t1.id, t1.qty, t2.nta, count(t1.n) NewQTY
    from  t1
    join  t2 on t1.itm=t2.itm and t1.dia = t2.dia and t1.rn=t2.rn
    groupby t1.itm, t1.dia, t1.loc, t1.id, t1.qty, t2.nta
)
select * 
from t3
orderby1,2,3,4,5,6