Facebook Sign in | Join
Pushing database changes needn't be hard work with SQL Compare
Getting Started with Adobe After Effects - Part 6: Motion Blur
First Time? You can support us by signing up. It takes only 5 seconds. Click here to sign up. If you already have an account, click here to login.

TSQL Challenge 23 - Calculate the uptime and downtime of a system by reading the data from an audit log

This challenge is related to calculate the uptime and downtime of a system by reading the data from an audit log.


http://beyondrelational.com/puzzles/challenges/27/calculate-the-uptime-and-downtime-of-a-system-by-reading-the-data-from-an-audit-log.aspx

Share

Comments

# re: TSQL Challenge 23 - Calculate the uptime and downtime of a system by reading the data from an audit log

Monday, February 08, 2010 3:36 AM by Ramireddy

Nice challenge... reminds famous problem.... eager to post an quick solution... but no time... need to wait until office is over.....


# re: TSQL Challenge 23 - Calculate the uptime and downtime of a system by reading the data from an audit log

Monday, February 08, 2010 1:25 PM by reganwick

I have a recommendation that new challenges be posted only after a challenge eval is complete.

For example, once challenge 16 results are complete, then post challenge 23


# re: TSQL Challenge 23 - Calculate the uptime and downtime of a system by reading the data from an audit log

Monday, February 08, 2010 8:52 PM by Jacob Sebastian

@reganwick,

your recommendation is valid and we understand it is a bit frustrating to bear the delay in seeing the results.

As I have mentioned a few times in the recent posts, we are speeding up the evaluation process to catch up. You will see the improvement very shortly (in 2-3 weeks) and we will be able to reduce the delay between the closure of a challenge and the results to 7 to 14 days.

It is just a matter of another few weeks and we will catch up with all the previous challenges and you will see the results. So please bear with us.


# re: TSQL Challenge 23 - Calculate the uptime and downtime of a system by reading the data from an audit log

Monday, February 08, 2010 11:22 PM by reganwick

Yes. I have read those posts from you. My recommendation was within that context. I just thought it would make more sense for the project not to backlog too much.


# re: TSQL Challenge 23 - Calculate the uptime and downtime of a system by reading the data from an audit log

Tuesday, February 09, 2010 3:56 AM by Mike Lewis

I have noticed in the examples given that you are rounding down to the nearest second, when SQL Server naturally rounds up.  Does it matter either way, or do we need to always round down?

Incidentally, I would post this in the forum but I don't seem to have permission to do so.

Thanks


# re: TSQL Challenge 23 - Calculate the uptime and downtime of a system by reading the data from an audit log

Tuesday, February 09, 2010 7:38 AM by Jacob Sebastian

@Michael, You should round down to the nearest second.

You need to be a member of the group beyondrelational.com/.../default.aspx to be able to post in the forums.


# re: TSQL Challenge 23 - Calculate the uptime and downtime of a system by reading the data from an audit log

Tuesday, February 09, 2010 7:48 AM by karinloos

Can we assume that the audit table is already in the correct sort order (ie date asc )


# re: TSQL Challenge 23 - Calculate the uptime and downtime of a system by reading the data from an audit log

Tuesday, February 09, 2010 8:05 AM by Jacob Sebastian

@karinloos,

I would suggest such an assumption may be incorrect, because a SQL Server table does not guarantee the order of the rows it returns, unless you specify a sort order. So let us not assume any physical order of rows.


# re: TSQL Challenge 23 - Calculate the uptime and downtime of a system by reading the data from an audit log

Tuesday, February 09, 2010 11:02 AM by Eric

I like this challenge.  It's a good one.


# re: TSQL Challenge 23 - Calculate the uptime and downtime of a system by reading the data from an audit log

Thursday, February 11, 2010 5:38 AM by dishdy

The rules say that "the first and last periods start and end on the hour preceding and following." If the first period in the data is exactly, say, 17:00:00 then the rule implies that this should be shown as 16:00:00 - even though someone might claim this is unreasonable. Same for the last period.

Can you please clear this up.

Thnks.


# re: TSQL Challenge 23 - Calculate the uptime and downtime of a system by reading the data from an audit log

Thursday, February 11, 2010 5:54 AM by karinloos

I interpret it differently again, I thought that the object was to round down to the nearest hour on the first period and round up to the last one


# re: TSQL Challenge 23 - Calculate the uptime and downtime of a system by reading the data from an audit log

Thursday, February 11, 2010 6:18 AM by dishdy

The rules do NOT use the terminology 'rounding down' or 'rounding up'. That's why I'm asking that this be make clear.


# re: TSQL Challenge 23 - Calculate the uptime and downtime of a system by reading the data from an audit log

Thursday, February 11, 2010 7:39 AM by Jacob Sebastian

@Dishdy,

you are right. if the first reading is done at 17:00, your code should assume the period of the first reading as 16:00 to 17:00 (which implies that the previous reading was done at 16:00).


# re: TSQL Challenge 23 - Calculate the uptime and downtime of a system by reading the data from an audit log

Thursday, February 11, 2010 12:09 PM by jmc_bkk

Jacob your last post isn't clear. The way I read the instructions was that the first periods start time should be "rounded" down to that time's whole hour (e.g., AuditTime = 10:11:23 becomes 10:00:00 in the final output). For the last AuditTime in the set, we round "up" to the next hour (e.g., 10:42:23 becomes 11:00:00). Now what if the data (the first and last records) starts or ends on a whole hour? My assumption is no rounding "up" or "down" is needed, the first/last time period starts/ends on a whole hour already so leave as is (and your code should plan for this). Please verify (or correct).


# re: TSQL Challenge 23 - Calculate the uptime and downtime of a system by reading the data from an audit log

Thursday, February 11, 2010 3:15 PM by dishdy

As I said earlier, the term "rounded" was never used (except when calculating the midpoint between two time stamps). Instead the term "preceding" was used for the first time stamp and "following" for the last time stamp. The hour "preceeding" 17:00:00, as far as I understand this term, is 16:00:00.

In my opinion, we shouldn't let this technicality distract us. Maybe the simplest way around this issue is to simply state that the first and last time stamp will never be an exact hour. In that case, both terms will produce the same result.


# re: TSQL Challenge 23 - Calculate the uptime and downtime of a system by reading the data from an audit log

Friday, February 12, 2010 2:14 AM by Mark Cowne

Can we assume that the audit times are unique and non NULL?


# re: TSQL Challenge 23 - Calculate the uptime and downtime of a system by reading the data from an audit log

Friday, February 12, 2010 4:15 AM by Rui Carvalho

@dishdy,

As Jacob said, you are right in your reading. This is just to have a starting and ending point and to have a common logic even if the first ping falls exactly on an hour. But as you said, don't be distracted with that ;-)

@Mark,

I can't said exactly now if we'll test that point but in all the cases it doesn't fit any logic.

In all the cases, your need to write your query to work with the provided sample data, after that it's up to you to take in consideration the facts you judge important


# re: TSQL Challenge 23 - Calculate the uptime and downtime of a system by reading the data from an audit log

Monday, February 15, 2010 9:56 PM by Niladri Biswas

Could any one please confirm whether

2010-01-01 10:37:55 is correct?

I guess it will be  2010-01-01 10:37:25

i.e. the second's field will be 25 instead of 55.

Please confirm.


# re: TSQL Challenge 23 - Calculate the uptime and downtime of a system by reading the data from an audit log

Tuesday, February 16, 2010 5:32 AM by rob_farley

Niladri,

10:36:11 to 10:37:25 is 1m 14s.  

10:37:25 to 10:39:40 is 2m 15s.    

10:36:11 to 10:37:55 is 1m 44s.  

10:37:55 to 10:39:40 is 1m 45s.

Therefore, the correct time is 10:37:55

Rob    


# re: TSQL Challenge 23 - Calculate the uptime and downtime of a system by reading the data from an audit log

Tuesday, June 08, 2010 9:43 AM by Sergejack

Seen in (il)logic testing :

SELECT '2010-01-01T10:00:00', 1

=> SELECT '2010-01-01 09:00:00','2010-01-01 10:00:00','Yes'

Are you being serious?

I read the rule "The first and last periods start and end on the hour preceding and following." but we're playing on words here.

Making 2010-01-01T10 be computed as 09:00:00-10:00:00 doesn't make any real-world sense.

It's a real pain to the logical mind to abide to such an out of the blue rule.


# re: TSQL Challenge 23 - Calculate the uptime and downtime of a system by reading the data from an audit log

Wednesday, June 09, 2010 6:57 AM by Jacob Sebastian

@Sergejack,

Most of these challenges are not 100% real-world. To increase or decrease the complexity to a certain level, additional rules are always added  to the problems being discussed in each of these challenges.


Copyright © Rivera Informatic Private Ltd.