SQLize Online / PHPize Online  /  SQLtest Online

A A A
Share      Blog   Popular
Copy Format Clear
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'); Select *, last_value(ReportDate) over (partition by Team_id order by Team_id, ReportDate desc) from TableGroup; 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;
Stuck with a problem? Got Error? Ask ChatGPT!
Copy Clear