0

Wondering if anyone can help me out. I have a simple table with the following fields.

ID (int), TimeStamp (DateTime), Status (NvarChar)

I need to produce a table with a Count of all Status' for the last 2 hours in 10 minute slots like the example provided. The idea is to produce a Google Line Chart in a dashboard where it will refresh every 10 minutes.

Example: Table

any help would be appreciated.

José

José Oliveira
  • 53
  • 5
  • Does it matter if a status is the same or not? Or just count every status for the past 2hours and 10 minutes? – Ryan Wilson Feb 20 '18 at 16:46
  • For every 10 minute period during the past 2 hours i need a total for all items which will have 2 possible statuses; "OK" and "NOK". https://i.stack.imgur.com/Tnfev.png shows what I need. Thanks for the help! – José Oliveira Feb 20 '18 at 17:05
  • This post shows a much easier solution than the one below: (https://stackoverflow.com/questions/5002661/how-to-group-time-by-hour-or-by-10-minutes) – Ryan Wilson Feb 20 '18 at 17:09
  • @RyanWilson the dateadd to round the time to nearest 10 minutes is easier, but you dont get all of the time slots for the last 2 hours if there's not a status update every 10 minutes, without the cte – JamieD77 Feb 20 '18 at 18:34

1 Answers1

1

you could create your time slots with a recursive cte and join to that.

with cte as (
    select  DATETIMEFROMPARTS(datepart(year,getdate()), datepart(month,getdate()), datepart(day,getdate()), datepart(hour, getdate()), floor((datepart(minute, getdate()) - 9) / 10) * 10, 0, 0) as startDT,
            DATETIMEFROMPARTS(datepart(year,getdate()), datepart(month,getdate()), datepart(day,getdate()), datepart(hour, getdate()), floor((datepart(minute, getdate()) + 9) / 10) * 10, 0, 0) as endDT
    union all
    select  DATEADD(minute, -10, startDT),
            DATEADD(minute, -10, endDt)
    from    cte 
    where   DATEADD(minute, -130, getdate()) < DATEADD(minute, -10, startDT)
)
select      endDt as [Period],
            count(case when [Status] = 'OK' then 1 end) as Status_OK,
            count(case when [Status] <> 'OK' then 1 end) as Status_NOK
from        cte
left join   myTable on [TimeStamp] >= startDT and [TimeStamp] < endDT
group by    endDT

if you prefer to use dateadd then

;with cte as (
    select  dateadd(minute, datediff(minute, 0, getdate()) / 10 * 10, 0) as startDT,
            dateadd(minute, datediff(minute, 0, getdate()) / 10 * 10 + 10, 0)  as endDT
    union all
    select  dateadd(minute, -10, startDT),
            dateadd(minute, -10, endDt)
    from    cte 
    where   dateadd(minute, -130, getdate()) < dateadd(minute, -10, startDT)
)
select      endDt as [Period],
            count(case when [Status] = 'OK' then 1 end) as Status_OK,
            count(case when [Status] <> 'OK' then 1 end) as Status_NOK
from        cte
left join   myTable on [TimeStamp] >= startDT and [TimeStamp] < endDT
group by    endDT
JamieD77
  • 13,796
  • 1
  • 17
  • 27