IF OBJECT_ID('tempdb..#meat') IS NOT NULL 
DROP TABLE #meat

IF OBJECT_ID('tempdb..#product') IS NOT NULL 
DROP TABLE #product

-- сырье
SELECT TOP 1000 
	MeatId = ROW_NUMBER() OVER (ORDER BY 1/0), 
	WeightKg = ((RN + 1129) * 127 % 10 + 1) * 10, 
	DT = DATEADD(day, (RN - 1) / 5, '2019-01-01') 
INTO #meat
FROM 
(
	SELECT RN = ROW_NUMBER() OVER (ORDER BY 1/0) FROM master..spt_values a1, master..spt_values a2
) A
ORDER BY RN

-- отгрузка со склада
SELECT TOP 10000 
	StockId = ROW_NUMBER() OVER (ORDER BY 1/0), 
	ProductId = RN % 3, 
	Product = CASE RN % 3 WHEN 0 THEN N'Котлеты' WHEN 1 THEN N'Стейк' WHEN 2 THEN N'Шашлык' END, 
	WeightKg = (RN % 10 + 1), 
	DT = DATEADD(day, (RN - 1) / 5, '2019-01-01') 
INTO #product 
FROM 
(
	SELECT RN = ROW_NUMBER() OVER (ORDER BY 1/0) FROM master..spt_values a1, master..spt_values a2
) A
ORDER BY RN


SELECT SUM(WeightKg) FROM #meat
SELECT SUM(WeightKg) FROM #product

SELECT * FROM #meat ORDER BY 1
SELECT * FROM #product ORDER BY 1

-- пример соответствия 
SELECT MeatId, ProductId, StockId, WeightKg FROM 
(
	VALUES 
	(1,1,1,2),
	(1,2,2,3),
	(1,3,0,4),
	(1,4,1,1),
	(2,4,1,4),
	(2,5,2,6),
	(2,6,0,7)

) A (MeatId, ProductId, StockId, WeightKg)

create clustered index IX_#m__m__start on #m (m__start, m__end);
create clustered index IX_#p__p__start on #p (p__start, p__end);

create clustered index IX_#m__m__start on #m (m__start);
create clustered index IX_#p__p__start on #p (p__start);

IF OBJECT_ID('tempdb..#meat') IS NOT NULL 
DROP TABLE #meat

IF OBJECT_ID('tempdb..#product') IS NOT NULL 
DROP TABLE #product

IF OBJECT_ID('tempdb..#m') IS NOT NULL 
DROP TABLE #m

IF OBJECT_ID('tempdb..#p') IS NOT NULL 
DROP TABLE #p

IF OBJECT_ID('tempdb..#result') IS NOT NULL 
DROP TABLE #result



-- сырье
SELECT TOP 100000 
	MeatId = ROW_NUMBER() OVER (ORDER BY 1/0), 
	WeightKg = ((RN + 1129) * 127 % 10 + 1) * 10, 
	DT = DATEADD(day, (RN - 1) / 5, '2019-01-01') 
INTO #meat
FROM 
(
	SELECT RN = ROW_NUMBER() OVER (ORDER BY 1/0) FROM master..spt_values a1, master..spt_values a2
) A
ORDER BY RN

-- отгрузка со склада
SELECT TOP 1000000 
	StockId = ROW_NUMBER() OVER (ORDER BY 1/0), 
	ProductId = RN % 3, 
	Product = CASE RN % 3 WHEN 0 THEN N'Котлеты' WHEN 1 THEN N'Стейк' WHEN 2 THEN N'Шашлык' END, 
	WeightKg = (RN % 10 + 1), 
	DT = DATEADD(day, (RN - 1) / 5, '2019-01-01') 
INTO #product 
FROM 
(
	SELECT RN = ROW_NUMBER() OVER (ORDER BY 1/0) FROM master..spt_values a1, master..spt_values a2
) A
ORDER BY RN


SELECT SUM(WeightKg) FROM #meat
SELECT SUM(WeightKg) FROM #product

--SELECT * FROM #meat ORDER BY 1
--SELECT * FROM #product ORDER BY 1

-- пример соотвествия 
SELECT MeatId, ProductId, StockId, WeightKg FROM 
(
	VALUES 
	(1,1,1,2),
	(1,2,2,3),
	(1,3,0,4),
	(1,4,1,1),
	(2,4,1,4),
	(2,5,2,6),
	(2,6,0,7)

) A (MeatId, ProductId, StockId, WeightKg)


select
 MeatId, WeightKg, DT, sum(WeightKg) over (order by dt, MeatId) - WeightKg + 1 as m__start, sum(WeightKg) over (order by dt, MeatId) as m__end
into
 #m
from
 #meat
order by
 DT;

select
 StockId, ProductId, Product, WeightKg, DT, sum(WeightKg) over (order by dt, StockId) - WeightKg + 1 as p__start, sum(WeightKg) over (order by dt, StockId) as p__end
into
 #p
from
 #product
order by
 DT;

create clustered index IX_#m__m__start on #m (m__start, m__end);
create clustered index IX_#p__p__start on #p (p__start, p__end);

select
 m.MeatId, p.ProductId, p.StockId,
 WeightKg = case when p.p__end > m.m__end then m.m__end else p.p__end end - case when m.m__start > p.p__start then m.m__start else p.p__start end + 1
INTO #result
from
 #m m join
 #p p on p.p__start <= m.m__end and p.p__end >= m.m__start
order by
 m.m__start;

DROP TABLE IF EXISTS meat;
DROP TABLE IF EXISTS product;
DROP TABLE IF EXISTS m;
DROP TABLE IF EXISTS p;


-- Сырье
CREATE LOCAL TEMPORARY TABLE meat (MeatId int, WeightKg int, DT TIMESTAMP) ON COMMIT PRESERVE ROWS; 
INSERT INTO meat SELECT
	n,
	((n + 1129) * 127 % 10 + 1) * 10,
	'2019-01-01'::date + (n - 1) / 5
FROM (
    SELECT ROW_NUMBER() OVER() AS n 
    FROM (
        SELECT 1 
        FROM (
            SELECT date(0) + INTERVAL '1 second' AS i 
            UNION ALL
            SELECT date(0) + INTERVAL '100000 seconds' AS i 
        ) _
        TIMESERIES tm AS '1 second' OVER(ORDER BY i)
    ) _
) _
ORDER BY n;


-- Отгрузка со склада
CREATE LOCAL TEMPORARY TABLE product (StockId int, ProductId int, Product VARCHAR, WeightKg int, DT TIMESTAMP) ON COMMIT PRESERVE ROWS; 
INSERT INTO product SELECT
	n,
	n % 3,
	CASE n % 3 WHEN 0 THEN 'Котлеты' WHEN 1 THEN 'Стейк' WHEN 2 THEN 'Шашлык' END,
	(n % 10 + 1),
	'2019-01-01'::date + (n - 1) / 5
FROM (
    SELECT ROW_NUMBER() OVER() AS n 
    FROM (
        SELECT 1 
        FROM (
            SELECT date(0) + INTERVAL '1 second' AS i 
            UNION ALL
            SELECT date(0) + INTERVAL '1000000 seconds' AS i 
        ) _
        TIMESERIES tm AS '1 second' OVER(ORDER BY i)
    ) _
) _
ORDER BY n;


SELECT
	MeatId,
	WeightKg,
	DT,
	SUM(WeightKg) OVER (ORDER BY DT, MeatId) - WeightKg + 1 AS m__start,
	SUM(WeightKg) OVER (ORDER BY DT, MeatId) AS m__end INTO TABLE m FROM meat ORDER BY DT;


SELECT
	StockId,
	ProductId,
	Product,
	WeightKg,
	DT,
	SUM(WeightKg) OVER (ORDER BY DT, StockId) - WeightKg + 1 AS p__start,
	SUM(WeightKg) OVER (ORDER BY DT, StockId) AS p__end INTO TABLE p FROM product ORDER BY DT;

DROP TABLE IF EXISTS result;

SELECT
	_m.MeatId,
	_p.ProductId,
	_p.StockId,
 	CASE
		WHEN _p.p__end > _m.m__end THEN _m.m__end
		ELSE _p.p__end
	END - 
	CASE
		WHEN _m.m__start > _p.p__start THEN _m.m__start
		ELSE _p.p__start
	END + 1 WeightKg
INTO TABLE result
FROM m _m
JOIN p _p on _p.p__start <= _m.m__end and _p.p__end >= _m.m__start ORDER BY _m.m__start;

SELECT COUNT(*) FROM result;