Parent count based on pairing of multiple children

Question

In the below example, I'm trying to count the number of drinks I can make based on the availability of ingredients per bar location that I have.

To further clarify, as seen in the below example: based on the figures highlighted in the chart below; I know that I can only make 1 Margarita on 6/30/2018 (in either DC or FL if I ship the supplies to the location).

Sample of data table

Please use the below code to enter the relevant data above:

    CREATE TABLE #drinks 
    (
        a_date      DATE,
        loc         NVARCHAR(2),
        parent      NVARCHAR(20),
        line_num    INT,
        child       NVARCHAR(20),
        avail_amt   INT
    );

INSERT INTO #drinks VALUES ('6/26/2018','CA','Long Island','1','Vodka','7');
INSERT INTO #drinks VALUES ('6/27/2018','CA','Long Island','2','Gin','5');
INSERT INTO #drinks VALUES ('6/28/2018','CA','Long Island','3','Rum','26');
INSERT INTO #drinks VALUES ('6/26/2018','DC','Long Island','1','Vodka','15');
INSERT INTO #drinks VALUES ('6/27/2018','DC','Long Island','2','Gin','18');
INSERT INTO #drinks VALUES ('6/28/2018','DC','Long Island','3','Rum','5');
INSERT INTO #drinks VALUES ('6/26/2018','FL','Long Island','1','Vodka','34');
INSERT INTO #drinks VALUES ('6/27/2018','FL','Long Island','2','Gin','14');
INSERT INTO #drinks VALUES ('6/28/2018','FL','Long Island','3','Rum','4');
INSERT INTO #drinks VALUES ('6/30/2018','DC','Margarita','1','Tequila','6');
INSERT INTO #drinks VALUES ('7/1/2018','DC','Margarita','2','Triple Sec','3');
INSERT INTO #drinks VALUES ('6/29/2018','FL','Margarita','1','Tequila','1');
INSERT INTO #drinks VALUES ('6/30/2018','FL','Margarita','2','Triple Sec','0');
INSERT INTO #drinks VALUES ('7/2/2018','CA','Cuba Libre','1','Rum','1');
INSERT INTO #drinks VALUES ('7/8/2018','CA','Cuba Libre','2','Coke','5');
INSERT INTO #drinks VALUES ('7/13/2018','CA','Cuba Libre','3','Lime','14');
INSERT INTO #drinks VALUES ('7/5/2018','DC','Cuba Libre','1','Rum','0');
INSERT INTO #drinks VALUES ('7/19/2018','DC','Cuba Libre','2','Coke','12');
INSERT INTO #drinks VALUES ('7/31/2018','DC','Cuba Libre','3','Lime','9');
INSERT INTO #drinks VALUES ('7/2/2018','FL','Cuba Libre','1','Rum','1');
INSERT INTO #drinks VALUES ('7/19/2018','FL','Cuba Libre','2','Coke','3');
INSERT INTO #drinks VALUES ('7/17/2018','FL','Cuba Libre','3','Lime','2');
INSERT INTO #drinks VALUES ('6/30/2018','DC','Long Island','3','Rum','4');
INSERT INTO #drinks VALUES ('7/7/2018','FL','Cosmopolitan','5','Triple Sec','7');

The expected results are as follows:

Please note, as seen in the expected results, children are interchangeable. For example, on 7/7/2018 Triple Sec arrived for the drink cosmopolitan; however because the child is also rum, it changes the availability of Margaritas for FL.

Also not the update to the DC region for Cuba Libre's on both 06/30 and 06/31.

Please take into consideration that parts are interchangeable and also that each time a new item arrives it makes available any item previously now.

Lastly - It would be awesome if I could add another column that shows kit availability regardless of location based only on availability of the child. For Ex. If there is a child #3 in DC and none in FL they FL can assume that they have enough inventory to make drink based on inventory in another location!

Brenneman

This sounds like a derivative of the bin packing problem to me. en.wikipedia.org/wiki/Bin_packing_problem – Statism

BTW, excellent job posting data, expected output and details about the logic you need. I wish everyone would take so much care in posting. Well done!! – Statism

Not enough explanation of the logic of the desired result for me. Why on June 30 can you make 9 Long Islands in DC? Why can you only make 4 Long Islands in DC on June 28? – Mixup

It's good that you've posted scripts, but a) the scripted data doesn't seem to match the explanatory text and b) it's not clear what the results are supposed to mean. How many of those drinks can be made there and then? Are we then to subtract quantities used from stock? – Maundy

@SeanLange Thanks – Brenneman

@Maundy Yes you are correct. The results would then be subtracted from the total "avail_amt" and the opposite is also correct. What else is unclear? – Brenneman

@TabAlleman - In the example On June 30th we can make 9 Long Islands because previously on 06/28 there was only enough rum to make 4 drinks. But then on 06/30 and additional shipment of rum was received. ....*I just realized I copied the wrong spread sheet and text. There was supposed to be another entry on 06/30 for DC for 4 packages of rum. And the original entry with 23 packages of rum on 06/28 was mean to be only 5 packages. Sorry for the confusion!!!! – Brenneman

@TabAlleman Thanks for the catch. I have updated all the information accordingly if you wanted to check it out. – Brenneman

@Maundy I have revised the scripts. Sorry for the confusion – Brenneman

Please note that using all caps is considered shouting. I have edited your question, but please keep this in mind for the future. – Arand

@MarkRotteveel Thanks Mark, Will do! My apologies – Brenneman

Couple of questions: a)How is 28-Jun CA Long Island 7 in the first row of expected result? b) What's the role of child? – Letrice

@AjayGupta CA can make 7 Long Islands on Jun-28, because a long island drink requires Vodka, Gin, and Rum as ingredients ---- Before the 28th, CA had no rum; but as of the 28th there was only enough Vodka to make 7 drinks. ---- The children in this sense are ingredient representing, as a whole, 1 complete drink. No drink can be made without at least one item from each child. – Brenneman

And how do I know the children of a particular drink and how are they interchangeable? – Letrice

@AjayGupta The children "child" columns is tied to the parent "parent" column. They are interchangeable as seen in the example. The "triple sec" that arrived in FL for the parent drink "cosmopolitan" can be used to make Margaritas as it is the same child, just belonging to a different parent. – Brenneman

Well, I wanted to know like there's just one entry of cosmopolitan in the table but the line_num is 5. Are there other 4 children of cosmopolitan? – Letrice

Let us continue this discussion in chat. – Letrice

Still looks problematic.. how can you make 7 Long Islands in CA on 28-Jun if you only have 5 Gin there on that date? – Mixup

@TabAlleman Thanks. Any ideas on how to write the sql for this? I was thinking nested loops, but I can't account for the make in the child column. – Brenneman

@Brenneman you're missing a definitive ingredient list. Nowhere can we tell from the data what a Cosmopolitan requires. As I've mentioned in the chat above, you should make 3 separate tables for: location inventory (by date), location menu, and drink formulation – Bachelor

@Brenneman I still don't understand the logic in the question well enough to help you with an answer, sorry. – Mixup

The most confusing thing about this question is how do you make a long island without coke, tequila, and triple sec? – Latrena

Answer 1

I've created a couple of extra tables to help with writing the query, but these could be generated from the #drinks table if you wanted:

CREATE TABLE #recipes 
(
    parent      NVARCHAR(20),
    child       NVARCHAR(20)
);

INSERT INTO #recipes VALUES ('Long Island', 'Vodka');
INSERT INTO #recipes VALUES ('Long Island', 'Gin');
INSERT INTO #recipes VALUES ('Long Island', 'Rum');
INSERT INTO #recipes VALUES ('Maragrita', 'Tequila');
INSERT INTO #recipes VALUES ('Maragrita', 'Triple Sec');
INSERT INTO #recipes VALUES ('Cuba Libre', 'Coke');
INSERT INTO #recipes VALUES ('Cuba Libre', 'Rum');
INSERT INTO #recipes VALUES ('Cuba Libre', 'Lime');
INSERT INTO #recipes VALUES ('Cosmopolitan', 'Cranberry Juice');
INSERT INTO #recipes VALUES ('Cosmopolitan', 'Triple Sec');

CREATE TABLE #locations 
(
    loc      NVARCHAR(20)
);

INSERT INTO #locations VALUES ('CA');
INSERT INTO #locations VALUES ('FL');
INSERT INTO #locations VALUES ('DC');

The query then becomes:

DECLARE @StartDateTime DATETIME
DECLARE @EndDateTime DATETIME

SET @StartDateTime = '2018-06-26'
SET @EndDateTime = '2018-07-31';

--First, build a range of dates that the report has to run for
WITH DateRange(a_date) AS 
(
    SELECT @StartDateTime AS DATE
    UNION ALL
    SELECT DATEADD(d, 1, a_date)
    FROM   DateRange 
    WHERE  a_date < @EndDateTime
)
SELECT a_date, parent, loc, avail_amt
FROM   (--available_recipes_inventory
        SELECT a_date, parent, loc, avail_amt,
               LAG(avail_amt, 1, 0) OVER (PARTITION BY loc, parent ORDER BY a_date) AS previous_avail_amt
        FROM   (--recipes_inventory
                SELECT a_date, parent, loc, 
                       --The least amount of the ingredients for a recipe is the most 
                       --amount of drinks we can make for it
                       MIN(avail_amt) as avail_amt
                FROM   (--ingredients_inventory
                        SELECT dr.a_date, r.parent, r.child, l.loc, 
                               --Default ingredients we don't have with a zero amount
                               ISNULL(d.avail_amt, 0) as avail_amt
                        FROM   DateRange dr CROSS JOIN
                               #recipes r CROSS JOIN
                               #locations l OUTER APPLY
                               (
                                --Find the total amount available for each 
                                --ingredient at each location for each date
                                SELECT SUM(d1.avail_amt) as avail_amt
                                FROM   #drinks d1
                                WHERE  d1.a_date <= dr.a_date
                                AND    d1.loc = l.loc
                                AND    d1.child = r.child
                               ) d
                        ) AS ingredients_inventory
                GROUP BY a_date, parent, loc
               ) AS recipes_inventory
        --Remove all recipes that we don't have enough ingredients for
        WHERE  avail_amt > 0 
       ) AS available_recipes_inventory
--Selects the first time a recipe has enough ingredients to be made
WHERE  previous_avail_amt = 0 
--Selects when the amount of ingredients has changed
OR     previous_avail_amt != avail_amt 
ORDER BY a_date
--MAXRECURSION needed to generate the date range
OPTION (MAXRECURSION 0)
GO

The innermost SELECT creates a pseudo inventory table (ingredients_inventory) consisting of location, ingredient, date and amount available. When an ingredient is not available at a location for a particular date, then a zero is used.

The next SELECT query out finds how many of each recipe can be made for each location/date (again this may be zero).

The next SELECT query out is an intermediate table necessary to gather how many of each recipe for each location could be made for the previous day (whilst also removing any drinks that could not be made).

And finally, the outermost SELECT query uses the previous day's data to find when the quantity of each particular recipe that can be made has changed.

This query produces slightly different numbers to your table, but I think that's because yours is wrong? Taking Florida for example, an extra Rum comes in on 2nd July, so the number of Long Islands that can be made goes up to 5. And 2 Cuba Libres can be made by the 19th.

Results:

+------------+-------------+-----+-----------+
| a_date     | parent      | loc | avail_amt |
+------------+-------------+-----+-----------+
| 2018-06-28 | Long Island | DC  | 5         |
| 2018-06-28 | Long Island | CA  | 5         |
| 2018-06-28 | Long Island | FL  | 4         |
| 2018-06-30 | Long Island | DC  | 9         |
| 2018-07-01 | Maragrita   | DC  | 3         |
| 2018-07-02 | Long Island | FL  | 5         |
| 2018-07-07 | Maragrita   | FL  | 1         |
| 2018-07-13 | Cuba Libre  | CA  | 5         |
| 2018-07-19 | Cuba Libre  | FL  | 2         |
| 2018-07-31 | Cuba Libre  | DC  | 9         |
+------------+-------------+-----+-----------+

Mccowan

Answer 2

I think this would give the required result.

Created a function that'll get the inventory.

Create function GetInventoryByDateAndLocation
(@date DATE, @Loc NVARCHAR(2))
RETURNS TABLE
AS
RETURN
(
Select child,avail_amt from 
    (Select a_date, child,avail_amt, 
     ROW_NUMBER() over (partition by child order by a_date desc) as ranking 
     from drinks where loc = @Loc and a_date<=@date)c 
where ranking = 1
)

Then the query:

with parentChild as 
(Select distinct parent, line_num, child from drinks),
ParentChildNo as
(Select parent, max(line_num) as ChildNo from parentChild group by parent)
,Inventory as
(Select a_date,loc,s.* from drinks d cross apply
GetInventoryByDateAndLocation(d.a_date, d.loc)s)
, Available as
(Select a_date,parent,loc,count(*) as childAvailable,min(avail_amt) as quantity 
from Inventory i 
join parentChild c 
on i.child = c.child 
group by parent,loc,a_date)
Select a_date,a.parent,loc,quantity from available a 
join ParentChildNo pc 
on a.parent = pc.parent and a.childAvailable = pc.ChildNo 
where quantity > 0 order by 1

This would give all the drinks which can be made from the inventory. Hope it solves your issue.

These are just my 2 cents. There are better ways of doing this and I hope more people would read this and suggest better.

Letrice

Thanks for the effort and feedback Ajay. I tried this in the actual table in my system. Unfortunately it only returns one record. I don't think you are taking account line_num in your Inventory function. Apart from that, the function your created make over 22 million records, which I know seems wrong. – Brenneman

I just thought line_num is for identifying the children. I'll add the explanation of my code and then maybe you can try making some modifications – Letrice

Answer 3

don't think this is exactly what your looking for... maybe it will help.

SELECT DISTINCT #drinks.loc,#drinks.parent,avail.Avail
FROM #drinks
LEFT OUTER JOIN (
SELECT DISTINCT #drinks.parent, MIN(availnow.maxavailnow / line_num) 
OVER(PARTITION BY parent) as Avail
FROM #drinks
LEFT OUTER JOIN (
            SELECT #drinks.child,SUM(avail_amt) maxavailnow
            FROM #drinks
            LEFT OUTER JOIN (SELECT MAX(a_date) date,loc,child FROM #drinks GROUP BY loc,child) maxx ON #drinks.loc = maxx.loc AND #drinks.child = maxx.child AND maxx.date = #drinks.a_date
            GROUP BY #drinks.child
            ) availnow ON #drinks.child = availnow.child
            ) avail ON avail.parent = #drinks.parent

Hellhound

Answer 4

SELECT ( SELECT MAX(d2.a_date) FROM #drinks AS d2 WHERE d2.parent = d.parent AND d2.loc = d.loc) AS a_date ,d.loc ,d.parent ,SUM(d.avail_amt) AS [avail_amt(SUM)] ,COUNT(d.avail_amt) AS [avail_amt(COUNT)] FROM #drinks AS d GROUP BY d.loc ,d.parent ORDER BY a_date

Claudicant