title: Parent count based on pairing of multiple children
body: <p>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.</p>
  
  <p>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).</p>
  
  <p>Sample of data table</p>
  
  <p><a href="https://i.stack.imgur.com/uzJA4.png" rel="noreferrer"><img src="https://i.stack.imgur.com/uzJA4.png" alt="Sample chart of inventory by location group by drink"></a></p>
  
  <p>Please use the below code to enter the relevant data above:</p>
  
  <pre><code>    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');
  </code></pre>
  
  <p>The expected results are as follows:</p>
  
  <p><a href="https://i.stack.imgur.com/sxtI4.png" rel="noreferrer"><img src="https://i.stack.imgur.com/sxtI4.png" alt="![Expected Results Desired"></a></p>
  
  <p>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.</p>
  
  <p>Also not the update to the DC region for Cuba Libre's on both 06/30 and 06/31.</p>
  
  <p>Please take into consideration that parts are interchangeable and also that each time a new item arrives it makes available any item previously now.</p>
  
  <p>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!</p>