It is currently Sun Dec 05, 2021 1:34 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 
Newbie
Newbie

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. https://snaptube.cam/ https://9apps.cam/

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.id, x.EventDateTime, x.StatusId, y.id, 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'
GROUP BY x.Id


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

Top
 Profile  
 
 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: 470
Hello,

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,
Miles
ModeratorsTeam
Freehostia.com


Top
 Profile  
 
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:  
cron
Hosting | Domains | Servers | Extras | Order | Support | Contacts | FreeHostia © 2011