SQL Server Find consecutive null values started date on date column and bit datatype column

7hiiyaii 于 2023-04-04 发布在其他

关注(0)|答案(2)|浏览(93)

create table history(response_date date, r_value bit);

insert into values('2023-03-18',1),('2023-03-19',NULL),
('2023-03-20',NULL),('2023-03-21',1),('2023-03-22',NULL),
('2023-03-23',0),('2023-03-24',0),('2023-03-25',NULL),
('2023-03-26',NULL),('2023-03-27',NULL),('2023-03-28',NULL);

I need only the starting record date of consecutive null values till the date from the table.

Expected result record date is:

2023-03-25

sql-server

来源：https://stackoverflow.com/questions/75926390/find-consecutive-null-values-started-date-on-date-column-and-bit-datatype-column

2条答案

按热度按时间

4zcjmb1e1#

Find consecutive null values started date on date column:

You can do it using ROW_NUMBER() OVER PARTITION BY r_valueas in this demo .

SELECT MIN(response_date) AS start_date
FROM (
  SELECT response_date, r_value, ROW_NUMBER() OVER (ORDER BY response_date) AS row_num,
         ROW_NUMBER() OVER (PARTITION BY r_value ORDER BY response_date) AS group_num
  FROM history
) t
WHERE r_value IS NULL
GROUP BY (row_num - group_num)
HAVING COUNT(*) >= 2;

Output :

start_date
2022-03-25

赞(0）回复(0）举报 2023-04-04

t3irkdon2#

with cte as (  
    SELECT 
        response_date, 
        r_value, 
        LEAD(r_value) OVER (ORDER BY response_date) AS lead,
        LAG(r_value) OVER (ORDER BY response_date) AS lag
    FROM history
) 
select * 
from cte
where r_value is null
and lead is null
and lag is not null;

赞(0）回复(0）举报 2023-04-04

我来回答

SQL Server Find consecutive null values started date on date column and bit datatype column

2条答案

相关问题

热门标签

最新问答