create table TableGroup
(
Number int
,ReportDate int
,Team_Id int
,state varchar(10)
);
insert into TableGroup
values
(9603, 20230110, 9001, 'closed')
,(9603, 20221230, 9001, 'inprogress')
,(9603, 20221229, 9509, 'accepting')
,(9603, 20221228, 9509, 'inprogress')
,(9603, 20221223, 9001, 'confirmed')
,(9603, 20221222, 9001, 'confirmed');
CREATE FUNCTION [dbo].[foo](@start_date DATE, @end_date DATE)
RETURNS TABLE
RETURN (
WITH date_range AS (
SELECT @start_date AS ret
UNION ALL
SELECT DATEADD(DAY, 1, ret)
FROM date_range
WHERE ret < DATEADD(DAY, -1, @end_date)
)
SELECT ret
FROM date_range
)
;
SELECT
number, dates.ret
FROM (
SELECT
Number,
MIN(CASE state WHEN 'closed' THEN '9999-12-31' ELSE CAST(CONVERT(CHAR(8), ReportDate) AS date) END) AS min_date,
MAX(CASE state WHEN 'closed' THEN CAST(CONVERT(CHAR(8), ReportDate) AS date) ELSE '1970-01-01' END) AS max_date
FROM TableGroup
WHERE Team_Id = 9001
GROUP BY Number
) tmp
CROSS APPLY dbo.foo(tmp.min_date, tmp.max_date) AS dates;
Telegram: DB developers channel