SQL Query Logic Needed-CodePudding

Im trying to map order/item wise qty from 2 data tables.

Table A :

order | item | Qty 
------------------
ABC   | IA1  | 5
ABC   | IA2  | 6
ABC   | IA3  | 1
ABD   | IA4  | 2
ABD   | IA5  | 2
ABE   | IA6  | 1
ABE   | IA7  | 2

Table B :

order | Qty
----------- 
ABC   | 9
ABD   | 3
ABE   | 3

The output should be:

order | item | ordered_Qty 
---------------------------
ABC   | IA1  | 5  ( 5 units from 9 )
ABC   | IA2  | 4  ( remaining 4 )
ABC   | IA3  | 0  ( 0 as 0 is remaining )
ABD   | IA4  | 2  (2 out of 3 )
ABD   | IA5  | 1  (remaining 1 )
ABE   | IA6  | 1  ( 1 out of 3 )
ABE   | IA7  | 2  ( remaining 2 )

CodePudding user response：

SELECT t1.*,
       CASE WHEN t2.Qty < SUM(t1.Qty) OVER (PARTITION BY `order` ORDER BY t1.item)
            THEN GREATEST(t2.Qty   t1.Qty - SUM(t1.Qty) OVER (PARTITION BY `order` ORDER BY t1.item), 0)
            ELSE t1.Qty
            END ordered_Qty 
FROM t1
JOIN t2 USING (`order`)

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=35ef35456c7d1910ea998131ce02d76b

CodePudding user response：

With a version of MySQL that supports window functions (8 ) this should be fairly trivial:

WITH a2 as (
  SELECT *,
    SUM(Qty) OVER(PARTITION BY [order] ORDER BY item) csum
  FROM TableA
)

SELECT *, 
  CASE WHEN b.Qty >= csum THEN a2.Qty                   --can fully
    WHEN b.Qty >= csum - a2.Qty THEN a2.Qty - (csum-b.Qty) --can partially
    ELSE 0 
  END as diff
FROM
  a2 INNER JOIN TableB b ON a2.[order] = b.[order]

The item bothers me a little, as it won't sort correctly if some order contains e.g. IA2 and IA10 - you might have to order by the int conversion of replacing IA with (nothing)