FreeHostia Forums

Query Almost Works!?
Page 1 of 1

Author:  serjio [ Thu May 07, 2020 6:26 am ]
Post subject:  Query Almost Works!?

Hi Everyone, I have a table containing the status of many devices in the form of an event log, as events are logged the status of the device is logged, status 1 means the device is available, > 1 means the device is unavailable with different numbers meaning different reasons.

I am attempting to create a query that will calculate the duration of the device unavailability by using TIMEDIFF between the timestamp from them the status went != 1 and when it went back = 1 - this almost works - the issue i have is that if the device is unavailable and then the unavailable reason changes before it goes available again i get 2 rows or more and some of the time is counted twice - what i need is for the query to not return the middle status changes and to only return from the first != 1 to the next = 1, and then repeat for every time the device availability changes from 1 to != 1

Additionally i must say that i fully understand why its doing this, i just dont know what to do about

SELECT, x.EventDateTime, x.StatusId,, y.EventDateTime, y.StatusId, TIMEDIFF(min(y.EventDateTime),x.EventDateTime) "Unavailable Duration"
FROM ShiftLog x
JOIN ShiftLog y
ON y.EventDateTime > x.EventDateTime
WHERE x.DeviceId = '733' AND y.DeviceId = '733'
AND x.StatusId != '1' AND y.StatusId = '1'

Author:  Miles [ Fri May 08, 2020 3:37 pm ]
Post subject:  Re: Query Almost Works!?


We do not offer such development services or advice and can not assist with your tasks. You need to contact some qualified developer to assist you on that.
Thank you for your understanding on this matter.

Page 1 of 1 All times are UTC
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group