CODEWITHSUNDEEP
sqlsql-server
taotechnocom
taotechnocom

Reputation: 228

Split quantity to multiple rows based on Quantity and Target dimension data

I have one table "Master data", I would like to split quantity to multiple rows based on Quantity and Target dimension data in SQL Server as sample image below.

enter image description here

Sample: data

CREATE TABLE MasterData
(
    JobNo  VARCHAR(4),
    Batch  VARCHAR(4),
    Tag  VARCHAR(4),
    UnitQtyInTag INT,
    UnitQtyInSheet INT,
    SheetQtyInTag INT
)


INSERT INTO MasterData
SELECT 'AAAA','B001','1',384,48,8

INSERT INTO MasterData
SELECT 'BBBB','B002','2',300,48,7

Upvotes: 0

Views: 841

Answers (3)

Jim Macaulay
Jim Macaulay

Reputation: 5165

You can achieve it through cross join,

select m1.* from MasterData m1
cross join MasterData m2;

Upvotes: 0

Nick
Nick

Reputation: 147246

You can implement this functionality using a recursive CTE which assigns the UnitQtyInTag to successive sheets until there is no remaining amount:

WITH sheets AS (
  SELECT JobNo,
         Batch,
         Tag,
         UnitQtyInTag,
         UnitQtyInSheet,
         SheetQtyInTag,
         1 AS SheetNumber,
         CASE WHEN UnitQtyInSheet > UnitQtyInTag THEN UnitQtyInTag
              ELSE UnitQtyInSheet 
         END AS pcsinSheet,
         CASE WHEN UnitQtyInSheet > UnitQtyInTag THEN 0
              ELSE UnitQtyInTag - UnitQtyInSheet 
         END AS balance
  FROM MasterData
  UNION ALL
  SELECT JobNo,
         Batch,
         Tag,
         UnitQtyInTag,
         UnitQtyInSheet,
         SheetQtyInTag,
         SheetNumber + 1,
         CASE WHEN UnitQtyInSheet > balance THEN balance
              ELSE UnitQtyInSheet 
         END,
         CASE WHEN UnitQtyInSheet > balance THEN 0
              ELSE balance - UnitQtyInSheet 
         END AS balance
  FROM sheets
  WHERE balance > 0
)
SELECT JobNo, Batch, Tag, UnitQtyInTag, UnitQtyInSheet, SheetQtyInTag, SheetNumber, pcsinSheet
FROM sheets
ORDER BY JobNo, Batch, Tag, SheetNumber

Output:

JobNo   Batch   Tag     UnitQtyInTag    UnitQtyInSheet  SheetQtyInTag   SheetNumber     pcsinSheet
AAAA    B001    1       384             48              8               1               48
AAAA    B001    1       384             48              8               2               48
AAAA    B001    1       384             48              8               3               48
AAAA    B001    1       384             48              8               4               48
AAAA    B001    1       384             48              8               5               48
AAAA    B001    1       384             48              8               6               48
AAAA    B001    1       384             48              8               7               48
AAAA    B001    1       384             48              8               8               48
BBBB    B002    2       300             48              7               1               48
BBBB    B002    2       300             48              7               2               48
BBBB    B002    2       300             48              7               3               48
BBBB    B002    2       300             48              7               4               48
BBBB    B002    2       300             48              7               5               48
BBBB    B002    2       300             48              7               6               48
BBBB    B002    2       300             48              7               7               12

Demo on dbfiddle

Upvotes: 3

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522741

We can solve this using a calendar table approach, where we join a sequence table to your table using the sheet quantity in tag:

SELECT
    t2.JobNo,
    t2.Batch,
    t2.Tag,
    t2.UnitQtyInTag,
    t2.UnitQtyInSheet,
    t2.SheetQtyInTag,
    t1.num AS SheetNumber
FROM
(
    SELECT 1 AS num UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
    UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8
) t1
INNER JOIN yourTable t2
    ON t1.num <= t2.SheetQtyInTag
ORDER BY
    t2.JobNo,
    t2.Batch,
    t2.Tag,
    t1.num;

Note that in practice you may wish to replace the inline subquery aliased as t1 above with a more scalable way to generate a sequence in SQL Server (which has been well covered in other questions here on SO).

Demo

Upvotes: 0

Related Questions