MS SQL - Query issue - Any Ideas?

Hi All,

I have a table with data simular to the following in MS SQL

SysName DateTime Status
SysA 01/01/2012 01:00 Up
SysB 01/01/2012 01:00 Down
SysC 01/01/2012 01:00 Up
SysA 01/01/2012 01:10 Up
SysB 01/01/2012 01:10 Down
SysC 01/01/2012 01:10 Down
SysA 01/01/2012 01:20 Down
SysB 01/01/2012 01:20 Down
SysC 01/01/2012 01:20 Down
SysA 01/01/2012 01:30 Up
SysB 01/01/2012 01:30 Down
SysC 01/01/2012 01:30 Up
SysA 01/01/2012 01:40 Up
SysB 01/01/2012 01:40 Down
SysC 01/01/2012 01:40 Up
SysA 01/01/2012 01:50 Down
SysB 01/01/2012 01:50 Down
SysC 01/01/2012 01:50 Up
SysA 01/01/2012 02:00 Down
SysB 01/01/2012 02:00 Down
SysC 01/01/2012 02:00 Up
SysA 01/01/2012 02:10 Up
SysB 01/01/2012 02:10 Down
SysC 01/01/2012 02:10 Up

I need to be able to run a query that will list the the down periods for each SysName in order - so the data from the above would look like the following

SysName DownTime UpTime
SysB 01/01/2012 01:00 STILL DOWN
SysC 01/01/2012 01:10 01/01/2012 01:30
SysA 01/01/2012 01:20 01/01/2012 01:30
SysA 01/01/2012 01:50 01/01/2012 02:10

The main issue I am having is allow the same device to be listed multiple times.

Any help would be greatly appreciated, I'm sure there's a trick to making this work...

Thanks!

So you want the most recent down record returned for each server?

Hi,

Thanks for your response.

I need a history of all the down events and the duration of the outage for lets say the last 10 outages, it could be the same node was down twice, this is where the difficulty lies as getting the last outage and duration for each is quite easy by selecting the last down time using max and then finding the max last uptime which is < the max last downtime.

Thanks