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

Question

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

As per the question guide, please do not post images of code, data, error messages, etc. - copy or type the text into the question. Please reserve the use of images for diagrams or demonstrating rendering bugs, things that are impossible to describe accurately via text. — Dale K, Apr 04 '23 at 07:02
we need find out the starting position of the continuous NULL records till the current date. for example: in the 10 records, for 6th onwards we have continuous nulls , till last records . we need to find that 6th row id or date, no need consider when 1 or more rows might be nulls and very next had not null. — S Nagendra, Apr 04 '23 at 07:22
@SNagendra; I have posted a solution and it shall work for any such multiple ranges too as I tested; Can you verify it — Tushar, Apr 04 '23 at 07:23

score 0 · Answer 1 · answered Apr 04 '23 at 07:17

0

Find consecutive null values started date on date column:

You can do it using ROW_NUMBER() OVER PARTITION BY r_value as 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

answered Apr 04 '23 at 07:17

Tushar

3,527
9
27
49

what else if the records have more than 2 nulls values before the consecutive nulls, – S Nagendra Apr 04 '23 at 07:37
The same query shall written you the `start_date` = `2022-03-22` as in this demo : https://dbfiddle.uk/tjOCWsK1 – Tushar Apr 04 '23 at 08:42
we need find out the starting position of the continuous NULL records till the current date. for example: in the 10 records, for 6th onwards we have continuous nulls , till last records . we need to find that 6th row id or date, no need consider when 1 or more rows might be nulls and very next had not null. just i have given a demo , i need query to work as dynamic , please understand above explanation. – S Nagendra Apr 05 '23 at 06:25

score 0 · Answer 2 · answered Apr 04 '23 at 07:27

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;

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

2 Answers2