create table dtt (date_tt date, ID_tt int,ID_tov int,qty int)
insert into dtt values
('2018-01-31' ,1, 1, 2),
('2018-01-31' ,1, 2, 50),
('2018-01-31' ,2, 1, 300),
('2018-01-31' ,3, 2, 1),
('2020-05-30' ,1, 3, 100),
('2020-05-30' ,1, 1, 500),
('2020-05-28' ,2, 1, 30),
('2023-11-17' ,3, 3, 10),
('2023-11-02' ,1, 1, 2),
('2023-11-01' ,1, 4, 50),
('2023-12-01' ,2, 5, 300),
('2023-11-04' ,3, 4, 1)
select distinct p.id_tov
from
(
select a.*, lag(a.sum_qty,1,0) over (partition by a.id_tov order by a.date_tt) as prec_sum_qty
from
(select date_tt,id_tov, sum(qty) as sum_qty
from dtt
group by date_tt,id_tov) a
)p
where p.prec_sum_qty=0 and p.sum_qty<>0 and p.date_tt between dateadd(day,-30,getdate()) and getdate()
/*select a.clientid,a.segmentid,
string_agg(
concat(
format(date,'dd.MM.yyyy'),
'-',
case when next_date is null
then 'Нет даты окончания'
else format(next_date,'dd.MM.yyyy')
end
),
', ') within group (order by date asc) as period
from (
select *,
lead(date) over (partition by clientid order by date) as next_date
from #segment
)a
group by a.clientid, a.segmentid
order by a.clientid, a.segmentid*/
Telegram: DB developers channel