1

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.

4
  • 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).
    – whereisike
    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.
    – whereisike
    Jun 30, 2016 at 18:08

1 Answer 1

1

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(SECOND, ROUND(DATEPART(SECOND, UpdateTimestamp)/5, 0)*5, 
        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, 
           DATEADD(MINUTE, DATEDIFF(MINUTE, 0, UpdateTimestamp), 0))
)
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.

4
  • 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).
    – Andriy M
    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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.