How to get the row after the lastest zero

Question

I have to get the date of 5/23/2022 which is next row from the last zero in the count column. Can I use queries to get it? Or I have to do a loop? Thank you.

ID	   printed_date	 count  
215251	1/23/2021	0  
215251	2/20/2021	0  
215251	3/23/2021	0  
215251	4/22/2021	17  
215251	5/23/2021	0  
215251	6/22/2021	0  
215251	7/23/2021	0  
215251	8/23/2021	0  
215251	9/22/2021	0  
215251	10/25/2021	0  
215251	11/22/2021	0  
215251	12/23/2021	0  
215251	1/24/2022	18  
215251	2/22/2022	46  
215251	3/23/2022	0  
215251	4/22/2022	0  
215251	5/23/2022	18  
215251	6/22/2022	48  
215251	7/25/2022	79  
215251	8/23/2022	110  
215251	9/22/2022	140  
215251	10/24/2022	171  
  

Answer 1

Here is a query (and my test setup):

   CREATE TABLE #ullig (id int NOT NULL,  
                        date date NOT NULL,  
                        cnt int NOT NULL)  
   INSERT #ullig (id, date, cnt)  
   VALUES  
    (215251, '1/23/2021'  ,  0    ),  
    (215251, '2/20/2021'  ,  0    ),  
    (215251, '3/23/2021'  ,  0    ),  
    (215251, '4/22/2021'  ,  17   ),  
    (215251, '5/23/2021'  ,  0    ),  
    (215251, '6/22/2021'  ,  0    ),  
    (215251, '7/23/2021'  ,  0    ),  
    (215251, '8/23/2021'  ,  0    ),  
    (215251, '9/22/2021'  ,  0    ),  
    (215251, '10/25/2021' ,   0   ),  
    (215251, '11/22/2021' ,   0   ),  
    (215251, '12/23/2021' ,   0   ),  
    (215251, '1/24/2022'  ,  18   ),  
    (215251, '2/22/2022'  ,  46   ),  
    (215251, '3/23/2022'  ,  0    ),  
    (215251, '4/22/2022'  ,  0    ),  
    (215251, '5/23/2022'  ,  18   ),  
    (215251, '6/22/2022'  ,  48   ),  
    (215251, '7/25/2022'  ,  79   ),  
    (215251, '8/23/2022'  ,  110  ),  
    (215251, '9/22/2022'  ,  140  ),  
    (215251, '10/24/2022' ,   171 )  
   go  
   SELECT *FROM #ullig  
   go  
   WITH numbering AS (  
      SELECT id, date, cnt,   
             row_number () OVER (PARTITION BY id, cnt ORDER BY date DESC) AS rownobycnt  
      FROM   #ullig  
   ), addlead AS (  
      SELECT *, lead(date) OVER(PARTITION BY id ORDER BY date) AS nextdate  
      FROM  numbering  
   )  
   SELECT nextdate AS thedate  
   FROM   addlead  
   WHERE  rownobycnt = 1  
     AND  cnt = 0  
   go  
   DROP TABLE #ullig  

Answer 2

;WITH cte AS (  
   SELECT ID, printed_date, [count],   
          row_number () OVER (PARTITION BY ID ORDER BY date  desc)  
   -row_number () OVER (PARTITION BY ID, case when [count]=0 then 0 else 1 end ORDER BY date desc ) AS delta  
   FROM   yourtable  
)  
   SELECT ID,min(printed_date) dt  
   FROM  cte  
   where delta=0  
   group by ID, delta  

--Modified query.

Answer 3

Hi @DanYeung W ,

Try this code:

;with cte as  
(  
select *  
,ROW_NUMBER()over(PARTITION BY count ORDER BY printed_date) as [order]  
,lead(printed_date) OVER(PARTITION BY ID ORDER BY printed_date) AS nextdate   
from yourtable  
)select max(nextdate) as [date] from cte where count=0  

Best regards
Niko

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 4

Thanks @Erland Sommarskog , @Jingyang Li , and @NikoXu-msft . All 3 examples work. It only let me check Accept Answer once.