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
  • Last Modified:

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		17.2442893981934	
2011-09-08 22:49:26.721		15.0537271499634	
2011-09-08 22:38:04.728		0
2011-09-08 22:42:09.720		18.25	
2011-09-09 14:34:26.703		16.7527503967285	
2011-09-09 14:39:04.467		0		                            
2011-09-09 14:45:26.597		15.9180603027344	
2011-09-10 16:08:04.793		19.2465705871582	
2011-09-10 16:12:27.003		15.5914258956909	
2011-09-10 16:33:04.230		0	
2011-09-10 16:49:26.643		16.3721446990967

appreciate your help
0
karinos57
Asked:
karinos57
  • 6
  • 2
  • 2
  • +1
2 Solutions
 
hnasrCommented:
What is the required result, and how is it calculated?
0
 
JestersGrindCommented:
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

Open in new window

0
 
karinos57Author 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
karinos57Author 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
 
karinos57Author 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
 
PCIIainCommented:
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
 
JestersGrindCommented:
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)

Open in new window

0
 
karinos57Author Commented:
after i run the modified query, i get this error:
"Type DATE is not a defined system type"
0
 
karinos57Author Commented:
PCIIain:
Yes, I rather show the total downtime for every hour of each day
0
 
PCIIainCommented:
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
 
karinos57Author Commented:
thnx
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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