Родительский счет, основанный на спаривании нескольких детей

Ответ 1

Я думаю, что это даст необходимый результат.

Создал функцию, которая получит инвентарь.

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<[email protected])c 
where ranking = 1
)

Затем запрос:

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

Это даст все напитки, которые можно сделать из инвентаря. Надеюсь, он решает вашу проблему.

Это всего лишь мои 2 цента. Есть лучшие способы сделать это, и я надеюсь, что больше людей прочитают это и предложит лучше.

Ответ 2

не думайте, что это именно то, что вы ищете... может быть, это поможет.

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

Ответ 3

Я создал несколько дополнительных таблиц, чтобы помочь с написанием запроса, но они могут быть сгенерированы из таблицы #drinks, если вы хотите:

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');

Затем запрос будет выглядеть следующим образом:

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

Самый внутренний SELECT создает таблицу псевдо-инвентаря (components_inventory), состоящую из местоположения, ингредиента, даты и количества доступных. Когда ингредиент недоступен в определенном месте, то используется ноль.

Следующий запрос SELECT определяет, сколько из каждого рецепта может быть сделано для каждого местоположения/даты (опять же это может быть ноль).

Следующий запрос SELECT - это промежуточная таблица, необходимая для сбора того, сколько из каждого рецепта для каждого местоположения можно было сделать за предыдущий день (в то же время удаление любых напитков, которые невозможно было сделать).

И, наконец, самый внешний запрос SELECT использует данные предыдущего дня, чтобы найти, когда количество каждого конкретного рецепта, который может быть сделано, изменилось.

Этот запрос производит несколько разные цифры для вашей таблицы, но я думаю, что, потому что ваш не так? Например, принимая во Флориде дополнительный воинский посох приходит на 2 июля, поэтому число длинных островов, которые могут быть сделаны, достигает 5. И 2 Кубы Libres могут быть сделаны к 19-му.

Результаты:

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