It is currently Sun Jul 21, 2024 4:28 pm

All times are UTC

Forum rules

Please click here to view the forum rules

Post new topic Reply to topic  [ 2 posts ] 
Author Message
 Post subject: Query Almost Works!?
PostPosted: Thu May 07, 2020 6:26 am 

Joined: Thu May 07, 2020 6:19 am
Posts: 1
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'

Last edited by serjio on Sun May 10, 2020 1:18 am, edited 1 time in total.

 Post subject: Re: Query Almost Works!?
PostPosted: Fri May 08, 2020 3:37 pm 
Site Admin

Joined: Sat May 31, 2008 1:56 am
Posts: 497

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.

Best Regards,

Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 2 posts ]  Moderators: Moderators, Support Team

All times are UTC

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
Jump to:  
Hosting | Domains | Servers | Extras | Order | Support | Contacts | FreeHostia © 2011