Find consecutive days with the same value

Question

My data is in this format

CALENDAR    CLIENTID    TOTAL
20170801    1700          2
20170801    1800    
20170801    1900          2
20170801    1990    
20170801    2000          0
20170801    2090          0
20170802    2090          0
20170803    2090          0

I am trying to get the minimum calendar day when a particular client has 3 consecutive totals of zero. So, in the example above, my output would be 20170801 since client 2090 had a zero total on 20170801, 20170802 and 20170803 days. I have the following query so far, but a and b counts are not accurate.

WITH cte AS (
    SELECT *,COUNT(1) OVER(PARTITION BY clientid) b  FROM
    (
        SELECT tt.*
           ,(SELECT COUNT(CALENDAR) FROM STATS 
                WHERE total = 0 
            ) AS a
        FROM STATS tt
        WHERE total = 0
    ) t1
)
SELECT * FROM cte WHERE b >= 3

Answer 1

Use lead(). The following query gets all such days, ordered by date:

select s.*
from (select s.*,
             lead(total) over (partition by clientid order by calendar) as total_1,
             lead(total, 2) over (partition by clientid order by calendar) as total_2
      from stats s
     ) s
where total = 0 and total_1 = 0 and total_2 = 0
order by date;

You can add fetch first 1 row only or the equivalent for your database to get only one row.

Answer 2

The way is

WITH cte AS (
  Select * From dbo.Consequtive Where TOTAL = 0
  )

SELECT r1.*, r2.*, r3.*
  FROM cte as r1
Left Join cte as r2
  On r1.CLIENTID = r2.CLIENTID and DATEADD(day, 1, r1.CALENDAR)  = r2.CALENDAR
Left Join cte as r3
  On r1.CLIENTID = r3.CLIENTID and DATEADD(day, 2, r1.CALENDAR)= r3.CALENDAR

Where r1.TOTAL = 0 and r2.TOTAL = 0 and r3.TOTAL = 0  

results in

CALENDAR    CLIENTID    TOTAL   CALENDAR    CLIENTID    TOTAL   CALENDAR    CLIENTID    TOTAL
2017-08-01  2090        0   2017-08-02  2090        0   2017-08-03  2090        0