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

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

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.

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

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

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

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.

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

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

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).

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.

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

@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

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.

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    

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.