Denis February 2016

How would l write SQL to label quantities until they run out?

I would like to label quantities (in the quantity table) using the labels assigned (see label assignment table) until the quantity goes to 0. Then I know that I am done labeling that particular ID.

label assignment table is as follows:

ID  |  Label | Quantity
 1      aaa      10
 1      bbb      20
 2      ccc      20

And my quantity table:

ID  |  Total Quantity
 1       60
 2       20

And I would like to get the following result:

ID  |   Label    | Quantity 
 1       aaa         10       (read from reference table, remaining 50)
 1       bbb         20       (read from reference table, remaining 30)
 1       [NULL]      30       (no label in reference table, remaining 0)
 2       ccc         20       (read from reference table, remaining 0)

Answers


Giorgos Betsos February 2016

You can do it with a simple JOIN and UNION operation so as to include 'not covered' quantities:

SELECT la.ID, la.Label, la.Quantity
FROM label_assignment AS la
INNER JOIN quantity AS q ON la.ID = q.ID

UNION 

SELECT q.ID, NULL AS Label, q.TotalQuantity - la.TotalQuantity  
FROM quantity AS q
INNER JOIN (
   SELECT ID, SUM(Quantity) AS TotalQuantity
   FROM label_assignment
   GROUP BY ID
) AS la ON q.ID = la.ID AND q.TotalQuantity > la.TotalQuantity 

Demo here


Derek Elkins February 2016

DECLARE @PerLabelQuantity TABLE(Id int, Label varchar(10), Quantity int);
INSERT INTO @PerLabelQuantity 
VALUES (1, 'aaa', 10), (1, 'bbb', 20), (2, 'ccc', 20);

DECLARE @QuantityRequired TABLE(Id int, TotalQuantity int);
INSERT INTO @QuantityRequired
VALUES (1, 60), (2, 20);

SELECT t.Id,
       CASE WHEN o.Overflowed = 1 THEN NULL ELSE t.Label END AS Label, 
       CASE WHEN o.Overflowed = 1 THEN t.QuantityStillNeeded 
            WHEN t.QuantityStillNeeded < 0 THEN t.Quantity + t.QuantityStillNeeded 
            ELSE t.Quantity END AS Quantity
FROM (
    SELECT p.Id, p.Label, p.Quantity,
        MAX(p.Label) OVER (PARTITION BY p.Id) AS LastLabel, 
        r.TotalQuantity - SUM(p.Quantity) 
            OVER (PARTITION BY p.Id 
                  ORDER BY Label 
                  ROWS UNBOUNDED PRECEDING) AS QuantityStillNeeded
    FROM @PerLabelQuantity p
    INNER JOIN @QuantityRequired r ON p.Id = r.Id) t
INNER JOIN (VALUES (0), (1)) o(Overflowed) 
    ON t.LastLabel = t.Label AND t.QuantityStillNeeded > 0 OR Overflowed = 0
WHERE t.QuantityStillNeeded > -t.Quantity; -- Remove this if you want labels with 
                                           -- 0 quantity used, but you'll need to tweak 
                                           -- the CASE expression for Quantity

The subquery calculates a set of used up labels and how many items remain afterward. If there is any quantity remaining after the last label, then we need to insert a row in the result set. To do this, I join on a two-element table but the join condition is only true when we are at the last label and there is quantity remaining. This is probably a confusing way to do this, and we could combine the UNION from George's answer with the subquery from mine to avoid this Overflow table.

Here's the changed (and probably preferable) query:

SELECT Id, 
       Label, 
       CASE WHEN QuantityStillNeeded < 0 THEN Quantity + QuantityStillNeed 


Denis February 2016

Simplest answer I think, after getting ideas from the other answers: Just create a "FAKE" label for the missing amount:

DECLARE @PerLabelQuantity TABLE(Id int, Label varchar(10), Quantity int);
INSERT INTO @PerLabelQuantity 
VALUES (1, 'aaa', 10), (1, 'bbb', 20), (2, 'ccc', 20);

SELECT *
FROM @PerLabelQuantity

DECLARE @QuantityRequired TABLE(Id int, TotalQuantity int);
INSERT INTO @QuantityRequired
VALUES (1, 60), (2, 20);

SELECT *
FROM @QuantityRequired

-- MAKE A FAKE LABEL LET'S CALL IT [NULL] WITH THE AMOUNT THAT IS NOT LABELED
-- i.e. WITH THE REMAINING AMOUNT
-- Probably should be done by copying the original data and the following
-- into a temp table but this is just for proof of concept
INSERT INTO @PerLabelQuantity( Id, Label, Quantity )
SELECT q.ID, 
       NULL, 
       ISNULL(q.TotalQuantity - p.TotalQuantityLabeled, q.TotalQuantity)
FROM @QuantityRequired q
     LEFT JOIN (SELECT p.ID, SUM(Quantity) AS TotalQuantityLabeled 
                 FROM @PerLabelQuantity p 
                 GROUP BY p.Id) p ON 
          p.ID = q.ID
          AND q.TotalQuantity - p.TotalQuantityLabeled > 0

SELECT *
FROM @PerLabelQuantity p

Post Status

Asked in February 2016
Viewed 3,478 times
Voted 13
Answered 3 times

Search




Leave an answer