Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 520

# Calculate the total down time

Hi,
i have a puzzle here and would appreciate if someone can help me here cuz i could not get the correct results.  I have 2 columns: DateStamp and ResponseTime.  If the ResponseTime shows 0 that means it is downtime but if it shows any other value other than zero then it is up time.  So, i want to calculate the Total Down Time(sum of total minutes) for each day where the ResponseTime was Zero.  Here is sample data that i have:

DateStamp                                                      ResponseTime
2011-09-08 22:53:04.627&#9;&#9;17.2442893981934&#9;
2011-09-08 22:49:26.721&#9;&#9;15.0537271499634&#9;
2011-09-08 22:38:04.728&#9;&#9;0
2011-09-08 22:42:09.720&#9;&#9;18.25&#9;
2011-09-09 14:34:26.703&#9;&#9;16.7527503967285&#9;
2011-09-09 14:39:04.467&#9;&#9;0&#9;&#9;
2011-09-09 14:45:26.597&#9;&#9;15.9180603027344&#9;
2011-09-10 16:08:04.793&#9;&#9;19.2465705871582&#9;
2011-09-10 16:12:27.003&#9;&#9;15.5914258956909&#9;
2011-09-10 16:33:04.230&#9;&#9;0&#9;
2011-09-10 16:49:26.643&#9;&#9;16.3721446990967

0
karinos57
• 6
• 2
• 2
• +1
2 Solutions

Commented:
What is the required result, and how is it calculated?
0

Commented:
I'm assuming that you want to compare the date/time before the downtime to the date/time after the downtime.  That is what this query does.

Greg

;WITH CTE
AS
(
SELECT
ROW_NUMBER() OVER(ORDER BY DateStamp) AS RowID, DateStamp, ResponseTime
FROM
YourTable
)
SELECT
SUM(DATEDIFF(mi, [before].DateStamp, [after].DateStamp)) AS TotalDowntime
FROM
CTE [down] INNER JOIN
CTE [before] ON [down].RowID = [before].RowID + 1 INNER JOIN
CTE [after] ON [down].RowID = [after].RowID - 1
WHERE
[down].ResponseTime = 0
0

Author Commented:
thanks for your quick response.  it would be great if we can show in another column where it shows the Total Minutes that it was down (for instance where the ResponseTime is 0) and if you could show the Total Minutes where it was up (for instance where the ResponseTime is NOT  0).  In terms of calucating,  i believe we should check first the date/time it was down like when it shows 0 then look at the next hour/minute it is up (when it is not 0) then the difference should give you the total downtime or uptime.  i hope i explained your question.  thanks again
0

Author Commented:

JestersGrind,
It appears this is the result I want but it is only giving me the result in one cell which is the total downtime.  I have thousands of rows in my server by just showing the result in one cell is not going to make a sense for lot of users so I would like to break it down by day like the following columns:
DateStamp: just to show here the normal datestamp
WeekDay: just to show here the day of the week like Monday, Tuesday
DownTime: based on the corresponding DateStamp and WeekDay -> Ex: 80 min was the downtime
UpTime: based on the corresponding DateStamp and WeekDay -> Ex: 22 Hrs and 40 min was the uptime

I thinking I am asking more complex staff but it may be simpler for you.  Thanks again.
0

Author Commented:
JestersGrind,
how about if you can tweek the query you wrote just to give me 2 columns like: the date/time hour for the down time and the total minutes for the down time for each hour

TimeStamp: this column should show the date/time  for the downtime (when the downtime happened)
TotalDownTime:  this column should show the total minutes for the downtime between the date/time before the downtime to the date/time after the downtime.  i hope i simplified my request now

0

Commented:
What is your expected result if the downtime occurs on one day, and doesn't resolve until the next day?
If it begins at say 23:00 and ends at 04:00 should it show 1 hour in one day and 4 in another?
0

Commented:
karinos57,

Sorry for the delay.  Try the following code.

Greg

;WITH CTE
AS
(
SELECT
ROW_NUMBER() OVER(PARTITION BY CONVERT(DATE, DateStamp) ORDER BY DateStamp) AS RowID, DateStamp, ResponseTime
FROM
YourTable
)
SELECT
CONVERT(DATE, [down].DateStamp) AS [Date],
DATENAME(DW, CONVERT(DATE, [down].DateStamp)) AS [Day],
--SUM(DATEDIFF(mi, [before].DateStamp, [after].DateStamp)) AS TotalDowntime,
CONVERT(VARCHAR, (SUM(DATEDIFF(mi, [before].DateStamp, [after].DateStamp)) / 60) )+ ' hours and ' +
CONVERT(VARCHAR, (SUM(DATEDIFF(mi, [before].DateStamp, [after].DateStamp)) % 60)) + ' minutes' AS TotalDowntime,
CONVERT(VARCHAR, ((1440 - SUM(DATEDIFF(mi, [before].DateStamp, [after].DateStamp)))) / 60) + ' hours and ' +
CONVERT(VARCHAR, ((1440 - SUM(DATEDIFF(mi, [before].DateStamp, [after].DateStamp)))) % 60) + ' minutes' AS TotalUptime
FROM
CTE [down] INNER JOIN
CTE [before] ON [down].RowID = [before].RowID + 1 AND CONVERT(DATE, [down].DateStamp) = CONVERT(DATE, [before].DateStamp) INNER JOIN
CTE [after] ON [down].RowID = [after].RowID - 1 AND CONVERT(DATE, [down].DateStamp) = CONVERT(DATE, [after].DateStamp)
WHERE
[down].ResponseTime = 0
GROUP BY
CONVERT(DATE, [down].DateStamp)
0

Author Commented:
after i run the modified query, i get this error:
"Type DATE is not a defined system type"
0

Author Commented:
PCIIain:
Yes, I rather show the total downtime for every hour of each day
0

Commented:
replace where it says "convert(date" with "convert(datetime"  to fix the issue, (I can see two.).

Further, JestersGrind's query assumes the site goes down immediately after the last positive reading ad doesn't come back 'till the next. Your example data doesn't actually have an 'uptime' record before the 'downtime' record. So it wouldn't work in this example. (I presume obviously there's more data.)

If you record (again, large gaps in tests to make maths easy.)

2011-09-08 12:00:00.000   15.0537271499634
2011-09-08 12:30:00.000   0
2011-09-08 13:00:00.000   18.25

Is the down time 30 mins (from 12:30 to 13:00) or 1 hour (from 12:00 to 13:00)?

detailing the down time for each hour may be tricky for the same reason as detailing it when it changes dates, you're going to need another table with the hours in each day in it.
0

Author Commented:
thnx
0

## Featured Post

• 6
• 2
• 2
• +1
Tackle projects and never again get stuck behind a technical roadblock.