I have a table named "MachineHealth". Here is the sample data:

Date MachineId IsBad
2020-01-05 1 1
2020-01-04 1 1
2020-01-03 1 1
2020-01-02 1 0
2020-01-01 1 1
2020-01-05 2 1
2020-01-04 2 1
2020-01-03 2 0
2020-01-02 2 0
2020-01-01 2 1

I want to find continuous 1 count in field IsBad group by MachineId, that means the number of days that a machine is continuously bad for the most recent time. For example, the query on above data should return:

MachineId ContinuousBadDays
1 3
2 2

How to write this sql?

New contributor
Leo Xu is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
  • You have 2 rows for MachineId 1 with a date of 2020-01-05. Is that valid in your scenario? – Scott Hodgin Jan 13 at 12:11
  • @ScottHodgin Thanks for reminding. I have edited the question. It should be only one record for each machine in each day. – Leo Xu Jan 14 at 1:47

Assuming your duplicate Dates for MachineId = 1 was an accident (as Scott pointed out) something like this should be what you're after:

WITH CTE_GoodDates_Sorted AS
    SELECT MachineId, [Date], ROW_NUMBER() OVER (PARTITION BY MachineId ORDER BY [Date] DESC) AS SortId -- Generates a unique ID for each row within each MachineId, then resets on the next MachineId
    FROM MachineHealth
    WHERE IsBad = 0 -- Only want good Dates so we can get the last good Date per MachineId later

SELECT MH.MachineId, SUM(MH.IsBad) AS ContinuousBadDays
FROM MachineHealth AS MH
    ON MH.MachineId = GD.MachineId
    AND MH.[Date] > GD.[Date] -- Ensures we only get rows that came after the good Date
WHERE GD.SortId = 1 -- Using the auto-generate unique ID (within each MachineId), ensures we only have the latest good Date for each MachineId to compare to in the previous INNER JOIN

This uses the ROW_NUMBER() window function to get the latest Date no accidents occurred on per each MachineId in a CTE then uses that CTE to join to filter down the results to only the most recent continuous bad Dates. The final query then does a GROUP BY on MachineId and sums the IsBad field to get the total days (though you probably can even substitute that for a COUNT(1) instead if your IsBad field at most has a value of 1).

  • 1
    Thanks so much. This works for me. The solution looks simple that I just need to find the latest good date and count the dates from that day. – Leo Xu Jan 14 at 4:44
  • 1
    In case of some machines are bad in all days, we cannot find the latest good date. So we can union these machines: UNION SELECT MachineId, COUNT(1) AS ContinuousBadDays FROM MachineHealth GROUP BY MachineId HAVING COUNT(1) == SUM(IsBad) – Leo Xu Jan 14 at 10:16
  • @LeoXu Yes good point, and a smart solution. That's probably similar to how I would add those Machines with all bad days back in as well. :) – J.D. Jan 14 at 12:49

Your Answer

Leo Xu is a new contributor. Be nice, and check out our Code of Conduct.

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.