cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
PapaTuck
Engaged Sweeper III

I need help with a report that shows sleeptime  for systems. Something like this:

SELECT

Name AS ‘Computer Name’,

LastWakeTime,

LastSleepTime,

DATEDIFF(MINUTE, LastWakeTime,

LastSleepTime) AS ‘Minutes Asleep’

FROM

Computers

WHERE

LastSleepTime IS NOT NULL AND

LastWakeTime IS NOT NULL

ORDER BY

‘Minutes Asleep’ DESC;

 

Suggestions are much appreciated.

1 ACCEPTED SOLUTION
Mister_Nobody
Honored Sweeper

Try this:

Select Top 1000000 tblassets.AssetID,
  tblassets.AssetName,
  Max(Case
    When tblUptime.EventType = 3 Then tblUptime.EventTime
    Else Null
  End) As start_sleep,
  Max(Case
    When tblUptime.EventType = 4 Then tblUptime.EventTime
    Else Null
  End) As end_sleep,
  DateDiff(MINUTE, Max(Case
    When tblUptime.EventType = 3 Then tblUptime.EventTime
    Else Null
  End), Max(Case
    When tblUptime.EventType = 4 Then tblUptime.EventTime
    Else Null
  End)) As [Minutes Asleep]
From tblassets
  Inner Join tblUptime On tblassets.AssetID = tblUptime.AssetId
Where tblUptime.EventType In (3, 4)
Group By tblassets.AssetID,
  tblassets.AssetName

 

View solution in original post

3 REPLIES 3
Mister_Nobody
Honored Sweeper

Try this:

Select Top 1000000 tblassets.AssetID,
  tblassets.AssetName,
  Max(Case
    When tblUptime.EventType = 3 Then tblUptime.EventTime
    Else Null
  End) As start_sleep,
  Max(Case
    When tblUptime.EventType = 4 Then tblUptime.EventTime
    Else Null
  End) As end_sleep,
  DateDiff(MINUTE, Max(Case
    When tblUptime.EventType = 3 Then tblUptime.EventTime
    Else Null
  End), Max(Case
    When tblUptime.EventType = 4 Then tblUptime.EventTime
    Else Null
  End)) As [Minutes Asleep]
From tblassets
  Inner Join tblUptime On tblassets.AssetID = tblUptime.AssetId
Where tblUptime.EventType In (3, 4)
Group By tblassets.AssetID,
  tblassets.AssetName

 

Mister_Nobody
Honored Sweeper

I have read DB documentation:

Mister_Nobody_0-1708916581609.png

You have a chance to solve your task.