Solved

Calculate the total down time

Posted on 2011-09-14
13
464 Views
Last Modified: 2012-05-12
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
Comment
Question by:karinos57
  • 6
  • 2
  • 2
  • +1
13 Comments
 
LVL 30

Expert Comment

by:hnasr
ID: 36537978
What is the required result, and how is it calculated?
0
 
LVL 21

Accepted Solution

by:
JestersGrind earned 250 total points
ID: 36538005
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
 

Author Comment

by:karinos57
ID: 36538038
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 Comment

by:karinos57
ID: 36538226


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 Comment

by:karinos57
ID: 36540177
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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 13

Assisted Solution

by:PCIIain
PCIIain earned 250 total points
ID: 36541303
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
 
LVL 21

Expert Comment

by:JestersGrind
ID: 36542616
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
 

Author Comment

by:karinos57
ID: 36542806
after i run the modified query, i get this error:
"Type DATE is not a defined system type"
0
 

Author Comment

by:karinos57
ID: 36542866
PCIIain:
Yes, I rather show the total downtime for every hour of each day
0
 
LVL 13

Expert Comment

by:PCIIain
ID: 36543022
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 Closing Comment

by:karinos57
ID: 36567341
thnx
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now