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!

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         |
+------------+-------------+-----+-----------+