I need some assistance with calculating downtime. I currently have a table being populated every 15 minutes with results from SiteScope.
- Columns are: ID, Domain, URL, navTime, AddedAt, LastUpdated
- navTime = 0 or navTime >= 65 should be considered downtime
- downtime should be calculated per URL since there are multiple entries for each URL
- I would imagine that calculating down to the minute should be fine
My idea is to look at each rows LastUpdated entry for a specific URL, and DATEDIFF it with the LastUpdated entry of the previous row for that specific URL, and to calculate this ongoing. Then, for each specific URL, those minutes need to be added up in another column. From there, with a start date and end date, I can come up with downtime.
Problem is, I don't know sql query very well and this seems complex to me. I've been able to come up with some queries that work, but putting it all together is epic fail.