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
Use
lead()
. The following query gets all such days, ordered by date:You can add
fetch first 1 row only
or the equivalent for your database to get only one row.