SQL Query Help

Question

Hi

I would like to display records if the same UID has Warning status for 3 consecutive business days (TDate).

DECLARE @Data TABLE 
(TDate DATE,
 UID varchar(10),
 Note varchar(10),
 EmailSent varchar(10))

 INSERT INTO @Data values ('02/08/2021','ABC',NULL,NULL)
 INSERT INTO @Data values ('02/08/2021','PQR',NULL,NULL)
 INSERT INTO @Data values ('02/09/2021','ABC','Warning','Yes')
 INSERT INTO @Data values ('02/09/2021','XYZ',NULL,NULL)
 INSERT INTO @Data values ('02/10/2021','ABC','Warning',NULL)
 INSERT INTO @Data values ('02/10/2021','TOM',NULL,NULL)
 INSERT INTO @Data values ('02/10/2021','ABC','Warning',NULL)
 INSERT INTO @Data values ('02/10/2021','CDQ',NULL,NULL)
 INSERT INTO @Data values ('02/11/2021','ABC','Warning',NULL)
 INSERT INTO @Data values ('02/12/2021','ABC','Warning',NULL)

 select * from @Data
 order by TDate

In this case, it should display UID - ABC record.

Thanks!

Answer 1

Please try the following solution.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (TDate DATE, UID varchar(10), Note varchar(10), EmailSent varchar(10));
INSERT INTO @tbl values 
('02/08/2021','ABC',NULL,NULL)
,('02/08/2021','PQR',NULL,NULL)
,('02/09/2021','ABC','Warning','Yes')
,('02/09/2021','XYZ',NULL,NULL)
,('02/10/2021','ABC','Warning',NULL)
,('02/10/2021','TOM',NULL,NULL)
,('02/10/2021','ABC','Warning',NULL)
,('02/10/2021','CDQ',NULL,NULL)
,('02/11/2021','ABC','Warning',NULL)
,('02/12/2021','ABC','Warning',NULL);
-- DDL and sample data population, end

WITH q AS
(
   SELECT  *,
         series = ROW_NUMBER() OVER (PARTITION BY UID ORDER BY TDate) -
                  ROW_NUMBER() OVER (PARTITION BY UID, Note ORDER BY TDate)
   FROM    @tbl WHERE Note = 'Warning'
)
, cte AS
(
 SELECT  MIN(TDate) AS Date_Start, MAX(TDate) AS Date_End, UID AS UID, Note
 FROM    q
 GROUP BY UID, Note, series
)
SELECT * 
FROM cte
WHERE DATEDIFF(DAY, Date_Start, Date_End) >= 3
ORDER BY Date_Start ASC;

Output

+------------+------------+-----+---------+
| Date_Start |  Date_End  | UID |  Note   |
+------------+------------+-----+---------+
| 2021-02-09 | 2021-02-12 | ABC | Warning |
+------------+------------+-----+---------+