# How to count BoxID records for the last hour for each 15-second time interval

**URL:** <https://community.grafana.com/t/how-to-count-boxid-records-for-the-last-hour-for-each-15-second-time-interval/140833>\
**Category:** MSSQL\
**Created:** [January 21, 2025, 3:08pm UTC](https://community.grafana.com/t/how-to-count-boxid-records-for-the-last-hour-for-each-15-second-time-interval/140833 "2025-01-21T15:08:20Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![michalbartonek](https://avatars.discourse-cdn.com/v4/letter/m/ec9cab/32.png) [@michalbartonek](https://community.grafana.com/u/michalbartonek)\
**Post date:** [January 21, 2025, 3:08pm UTC](https://community.grafana.com/t/how-to-count-boxid-records-for-the-last-hour-for-each-15-second-time-interval/140833/1 "2025-01-21T15:08:20Z")

</div>

Dear Grafana team/users,

### What I Want to Achieve:

I want to create a Grafana query where:

- **Time is grouped into 15-second intervals**.
- For each of these **15-second intervals** , I want to calculate **the count of `BoxID` records** for the **last hour** preceding each 15-second interval.

In simpler terms, the goal is to:

- **Group data by 15-second intervals**.
- **For each time interval** , calculate how many `BoxID` records were logged in the **previous hour** up to that specific time interval.

### Detailed Explanation of the Query:

1. **Group Data into 15-second Intervals** : We need to take the **timestamp** of each record and group it into intervals of **15 seconds**. This will give us discrete blocks of time on the x-axis of the graph.
2. **Count the `BoxID` Records for Each 15-second Interval** : For each 15-second interval, we need to calculate how many `BoxID` entries were recorded **in the last hour**. This means for every 15-second timestamp, we look back **one hour** and count the total number of `BoxID` records in that time period.

Currently, I have the following query, which counts `BoxID` records for **15-second intervals** :

 ![image](https://us1.discourse-cdn.com/grafana/original/3X/f/5/f5db32d8916a212dbe23d7a5bcd527b9a11034ea.png)  
SELECT  
$\_\_timeGroup(Timestamp, ‘15s’) AS “time”, – Time interval grouped by 15 seconds  
count(BoxID) – Counting BoxID for each 15-second interval  
FROM  
ACTIN\_DP.dbo.PLC – Data source (PLC table)  
WHERE  
BR = ‘BR08’ – Filter for BR08 branch  
AND $\_\_timeFilter(Timestamp) – Time filter applied for the selected time range in Grafana  
GROUP BY  
$\_\_timeGroup(Timestamp, ‘15s’) – Grouping by 15-second intervals  
ORDER BY  
“time” – Ordering the results by time

Thank you very much for your help! I appreciate any suggestions or improvements you can offer to modify this query for better clarity and accuracy!

---

<div class="post-metadata">

**Author:** ![sowdenraymond](https://sea2.discourse-cdn.com/grafana/user_avatar/community.grafana.com/sowdenraymond/32/38541_2.png) [@sowdenraymond](https://community.grafana.com/u/sowdenraymond)\
**Post date:** [January 21, 2025, 3:58pm UTC](https://community.grafana.com/t/how-to-count-boxid-records-for-the-last-hour-for-each-15-second-time-interval/140833/2 "2025-01-21T15:58:06Z")

</div>

I don’t know what your data looks like, see if this works:

WITH TimeGrouped AS (  
SELECT  
$\_\_timeGroup(Timestamp, ‘15s’) AS “time\_group”, – Grouped into 15-second intervals  
Timestamp,  
BoxID  
FROM  
ACTIN\_DP.dbo.PLC  
WHERE  
BR = ‘BR08’ – Filter for BR08 branch  
AND $\_\_timeFilter(Timestamp) – Time filter for Grafana range  
)  
SELECT  
tg.time\_group AS “time”,  
COUNT(t1.BoxID) AS “count\_boxid”  
FROM  
TimeGrouped tg  
JOIN  
TimeGrouped t1  
ON t1.Timestamp BETWEEN DATEADD(SECOND, -3600, tg.time\_group) AND tg.time\_group – Previous hour to the current time group  
GROUP BY  
tg.time\_group  
ORDER BY  
tg.time\_group;

---

<div class="post-metadata">

**Author:** ![michalbartonek](https://avatars.discourse-cdn.com/v4/letter/m/ec9cab/32.png) [@michalbartonek](https://community.grafana.com/u/michalbartonek)\
**Post date:** [January 28, 2025, 1:56pm UTC](https://community.grafana.com/t/how-to-count-boxid-records-for-the-last-hour-for-each-15-second-time-interval/140833/3 "2025-01-28T13:56:46Z")

</div>

Thank you so much for your suggestion and guidance! I really appreciate your time and effort in helping me refine my query. Based on your input and a bit of additional experimentation, I was able to achieve exactly what I wanted. Here’s what I did:

`WITH MinuteData AS (`  
` SELECT`  
` $__timeGroup(Timestamp, '15s') AS "time", -- Group timestamps into 15-second intervals`  
` COUNT(BoxID) AS "boxCount" -- Count BoxID for each interval`  
` FROM`  
` ACTIN_DP.dbo.PLC`  
` WHERE`  
` BR = 'BR08' -- Filter for branch BR08`  
` AND Timestamp >= DATEADD(HOUR, -1, $__timeFrom()) -- Load data starting 1 hour before`  
` the Grafana time range`  
` AND Timestamp <= $__timeTo() -- Limit data to the Grafana time range`  
` GROUP BY`  
` $__timeGroup(Timestamp, '15s') -- Group data by 15-second intervals`  
`)`  
`SELECT`  
` "time", -- Each 15-second time interval`  
` SUM(boxCount) OVER (`  
` ORDER BY "time"`  
` ROWS BETWEEN 239 PRECEDING AND CURRENT ROW -- Rolling sum for the last 240 intervals`  
` (15s * 240 = 1 hour)`  
` ) AS "boxCountLastHour" -- Calculate the rolling count of BoxID records for the last hour`  
`FROM`  
` MinuteData`  
`ORDER BY`  
` "time"; -- Sort results by time`

### Key Adjustments I Made:

1. **Expanded Time Range for Accuracy:**

- To calculate the rolling count for the last hour, I adjusted the `WHERE` clause to load data from 1 hour before the selected Grafana range:

```auto
AND Timestamp >= DATEADD(HOUR, -1, $__timeFrom())

```

- This ensures that even if the Grafana range is limited (e.g., 5 minutes), we still have enough data for accurate rolling calculations.

1. **Used a Common Table Expression (CTE):**

- I grouped data by 15-second intervals in the CTE (`MinuteData`), which simplifies further calculations.

1. **Rolling Sum with `OVER` Clause:**

- Using the `SUM` window function, I calculated the rolling count for the last 240 intervals (15 seconds \* 240 = 1 hour).

---

<div class="post-metadata">

**Author:** ![yosiasz](https://sea2.discourse-cdn.com/grafana/user_avatar/community.grafana.com/yosiasz/32/45847_2.png) [@yosiasz](https://community.grafana.com/u/yosiasz)\
**Post date:** [January 28, 2025, 5:22pm UTC](https://community.grafana.com/t/how-to-count-boxid-records-for-the-last-hour-for-each-15-second-time-interval/140833/4 "2025-01-28T17:22:24Z")

</div>

Very nice, now take the generated query from query inspector and run it in SQL management and see query plan and performance of it