# Table frequency metric Count (minimum 2 hits every 5 seconds using a datestamp within 24 hours)

I need to find out how many times a table has an insert within a 24 hour period per 5 seconds, if the table is inserted at least twice (2x) or more for each 5 seconds of the day, it should be counted. So for every 5 seconds (using a datestamp), how many times was the table inserted at least 2x within a 24 hour period per 5 seconds. The max the count can be 17280, and that's only if there was 2 or inserts for each 5 seconds.

• What do you mean `hit`? Select/update/insert/delete? Do you want it only once or on the regular base? Is it OK to do a measure for only one hour? Jun 30, 2016 at 2:39
• For each 5 second interval within an 24 hour period, how many times were at least 2 lines added (inserted) to the table within each 5 seconds (using the datestamp). Jun 30, 2016 at 6:18
• You have to better specify your requirements. What if in the first 5 sec you have 1 insert and then in other 5 sec you have 3 inserts - is it count? Do you expect 34560 inserts within 24 hours? What if there are only 34559, what do you do? Jun 30, 2016 at 16:21
• This is being counted per 5 seconds of 24 hours. So if the first 5 seconds of the days has no inserts or just 1 insert, its not counted. if the second 5 seconds of the day has 2 or more inserts, then that's counted. this is incrementally counted for 24hours or 86400 seconds. the highest count that I can obtain in 24 hours is 17280, and that's only if there were at least 2 inserts for every 5 seconds of the day. Jun 30, 2016 at 18:08

Since your day is divided into 5-second buckets, you'll need to do the same to your timestamp in the table. Since you're counting the 5-second buckets and not the actual number of updates, you'll need to use either a CTE or derived table. I'll use the CTE only because I think it's easier to read.

``````WITH UpdateIntervals AS (
SELECT
DATEADD(MINUTE, DATEDIFF(MINUTE, 0, UpdateTimestamp), 0)) AS Interval,
COUNT(*) AS UpdateCount
FROM dbo.YourTable
WHERE UpdateTimestamp BETWEEN @ReportStartTime AND @ReportStopTime
GROUP BY DATEADD(SECOND, ROUND(DATEPART(SECOND, UpdateTimestamp)/5, 0)*5,
)
SELECT COUNT(*)
FROM UpdateIntervals
WHERE UpdateCount >= 2;
``````

You could just as easily use `HAVING COUNT(*) > 1` inside the CTE and then a simple `COUNT(*)` in the query with no where condition, but to me, this makes it clear what I intended to do when I come back and read this 6 months later. I'm counting the number of updates per interval, then counting intervals with at least 2 updates.

The nasty-looking DATEADD formula isn't as bad as it looks. First, it truncates your update timestamp down to the minutes level by calculating the number of minutes between 0-time and your timestamp, then adding them back to 0-time. The seconds part of your timestamp is then rounded down to the previous multiple of 5, and the rounded value added back to the timestamp to give you 5-second buckets.

• Nice! And you don't even need the `Interval` in the `SELECT` list (although SQL Server's optimizer is probably clever enough to remove it anyway) Jun 30, 2016 at 19:21
• That's true, ypercube. Since we're not using it in the outer query, we could just leave the formula in the GROUP BY and remove it from the select. Jun 30, 2016 at 19:24
• ROUND is redundant. SQL Server does integral division when both operands of `/` are integers (the result being rounded down, which seems fair enough to me). Jul 1, 2016 at 8:49
• Good point, Andriy. I'm used to dealing with decimal data types and didn't even think about it. Jul 1, 2016 at 13:34