How to get the row after the lastest zero

DanYeung W 41 Reputation points
2022-10-28T21:31:36.96+00:00

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  
  
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,584 questions
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 104.2K Reputation points MVP
    2022-10-28T22:19:01.547+00:00

    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  
    
    0 comments No comments

3 additional answers

Sort by: Most helpful
  1. Jingyang Li 5,891 Reputation points
    2022-10-29T03:12:59.76+00:00
    ;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.

    0 comments No comments

  2. NikoXu-msft 1,911 Reputation points
    2022-10-31T02:30:48.227+00:00

    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".

    0 comments No comments

  3. DanYeung W 41 Reputation points
    2022-10-31T14:48:02.28+00:00

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