DROP TABLE IF EXISTS dbo.Sales;
CREATE TABLE dbo.Sales (
Shop VARCHAR(255),
Product VARCHAR(255),
Date DATE,
Seller VARCHAR(255),
Count INT,
NewDate DATE
);
INSERT INTO dbo.Sales (Shop, Product, Date, Seller, Count, NewDate)
VALUES
('M1', 'T1', '2010-01-01', 'P1', 2, '2010-01-01'),
('M1', 'T1', '2010-01-05', 'P2', 1, '2010-01-01'),
('M1', 'T1', '2010-01-29', 'P1', 3, '2010-01-01'),
('M2', 'T1', '2010-01-15', 'P2', 1, '2010-01-15'),
('M1', 'T1', '2010-02-28', 'P1', 3, '2010-02-28'),
('M1', 'T1', '2010-03-10', 'P2', 2, '2010-02-28'),
('M2', 'T1', '2010-04-15', 'P1', 1, '2010-04-15');
select Shop, Product, Seller, Count, Date, NewDate, min(Date) over (partition by Shop, Product, gr) as myDate
from (
select *
, sum(dd) over(partition by Shop, Product order by date) as sum0
, sum(dd) over(partition by Shop, Product order by date) / 30 as gr
from (
SELECT s.Shop
, s.Product
, s.Seller
, s.Count
, s.Date
, s.NewDate
, datediff(dd, lag(s.Date, 1, s.Date) over(partition by s.Shop, s.Product order by s.date), s.Date) as dd
FROM dbo.Sales s
) t
) t
Telegram: DB developers channel