CODEWITHSUNDEEP
sqlsql-server
José Oliveira
José Oliveira

Reputation: 53

SQL Query | Summarize TimeSeries

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é

Upvotes: 0

Views: 114

Answers (1)

JamieD77
JamieD77

Reputation: 13949

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

Upvotes: 1

Related Questions