SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE test ( id integer, date text, title text, stock text ); INSERT INTO public.test (id, date, title, stock) VALUES (1, '01.01.2022', 'Title 1', 'N'); INSERT INTO public.test (id, date, title, stock) VALUES (2, '02.01.2022', 'Title 1', 'N'); INSERT INTO public.test (id, date, title, stock) VALUES (3, '03.01.2022', 'Title 1', 'N'); INSERT INTO public.test (id, date, title, stock) VALUES (4, '04.01.2022', 'Title 1', 'Y'); INSERT INTO public.test (id, date, title, stock) VALUES (5, '05.01.2022', 'Title 1', 'Y'); INSERT INTO public.test (id, date, title, stock) VALUES (6, '06.01.2022', 'Title 1', 'N'); INSERT INTO public.test (id, date, title, stock) VALUES (7, '08.01.2022', 'Title 1', 'N'); INSERT INTO public.test (id, date, title, stock) VALUES (8, '09.01.2022', 'Title 1', 'N'); INSERT INTO public.test (id, date, title, stock) VALUES (9, '15.01.2022', 'Title 1', 'N'); SELECT *, CASE WHEN stock = 'N' THEN to_date(date, 'DD.MM.YYYY') - to_date(first_value(date) OVER (PARTITION BY title,batch ORDER BY date), 'DD.MM.YYYY') ELSE 0 END AS "doos" FROM ( SELECT *, COALESCE(CASE WHEN stock = 'Y' THEN id ELSE lag(id, rn::int) OVER (PARTITION BY title ORDER BY date) END) AS batch FROM ( SELECT *, CASE WHEN stock = 'N' THEN row_number() OVER (PARTITION BY title,stock,s1 - s2 ORDER BY date) ELSE NULL END AS "rn" FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY title ORDER BY date) AS "s1", ROW_NUMBER() OVER (PARTITION BY title,stock ORDER BY date) AS "s2" FROM test) AS "s" ) AS r ) AS q ORDER BY id