Find if two different events occur with the same hour

[gavinmarks]

Grafana version 9.4.3

Microsoft SQL Server

The image shows the table structure, I am trying to find the number of times two specific alarms reset with in the same hour. The query I have so far is below, but still not getting the correct data back.

SELECT
  CAST(TimeDate as Date) as Day,
  DATEPART(hour, TimeDate) as Hour,
  AGV,
  Fault
FROM AGV.dbo.AGVfault
WHERE Fault = 'wifi reset'
  OR Fault = 'lowbattery reset'
GROUP BY 
  CAST(TimeDate as Date),
  DATEPART(hour, TimeDate),
  AGV,
  Fault
ORDER BY
  CAST(TimeDate as Date),
    DATEPART(hour, TimeDate)

Can someone let me know if I am on the right track, or if there is a better way to extract this data?
The end goal is to display when a specific event takes place on a time series chart, filtered by several variables. the event itself is not recorded, but can be observed in the data by a few different alarms occurring within a few minutes of each other.

Thanks for the help as always.

[gavinmarks]

Hi

I am still stuck with this one, if anyone can give me some advice?
This is my latest effort. the data looks a bit better, but the return values are still wrong and I have duplicate ‘Programs’ shown.

I am trying to show the number of events over a filtered time frame. the event is defined by the occurrence of two different events happening to the same ‘AGV’ within the same hour.

Thanks

SELECT
  Program,
  COUNT(*) AS 'Battery Change'
FROM AGV.dbo.AGVfault
WHERE Fault IN ('wifi reset', 'lowbattery reset') AND $__timeFilter(TimeDate) AND Program <> 0
GROUP BY 
  CAST(TimeDate AS Date),
  DATEPART(hour, TimeDate),
  Program
HAVING COUNT(DISTINCT Fault) = 2
ORDER BY 'Battery Change' DESC;