0

i'm stuck creating a complex query... maybe there is some help out there?? :) I've a table with device status. An entry is genereated for each device status change. What i want to have is an output where i can see the lastest per date of all devices - for all dates containing in the table. in this exampel 04.05.2021 - 06.05.2021. i want to use it for a dashboard.

Can anyone help me out?

Here is an example how the table is looking like: Table of Device-Changes:

ID DEVICEID CHANGEDON PREVALUE POSTVALUE
20 40 04.05.2021 13:00 - ON
21 41 04.05.2021 13:00 - ON
22 42 04.05.2021 13:00 - ON
23 43 04.05.2021 13:00 - ON
24 44 04.05.2021 13:00 - ON
25 45 04.05.2021 13:00 - ON
26 46 04.05.2021 13:00 - ON
27 47 04.05.2021 13:00 - ON
28 48 04.05.2021 13:00 - ON
29 40 05.05.2021 15:00 ON OFF
30 40 05.05.2021 16:00 OFF RECOV
31 40 05.05.2021 17:00 RECOV WAIT
32 41 06.05.2021 09:00 ON OFF
33 41 06.05.2021 10:00 OFF WAIT
35 40 06.05.2021 11:00 WAIT ON

The output what i want to achieve is the follwoing:

LATESTSTATPERDATE DEVICEID CHANGEDON PREVALUE POSTVALUE
04.05.2021 40 04.05.2021 13:00 - ON
04.05.2021 41 04.05.2021 13:00 - ON
04.05.2021 42 04.05.2021 13:00 - ON
04.05.2021 43 04.05.2021 13:00 - ON
04.05.2021 44 04.05.2021 13:00 - ON
04.05.2021 45 04.05.2021 13:00 - ON
04.05.2021 46 04.05.2021 13:00 - ON
04.05.2021 47 04.05.2021 13:00 - ON
04.05.2021 48 04.05.2021 13:00 - ON
05.05.2021 40 05.05.2021 17:00 RECOV WAIT
05.05.2021 41 04.05.2021 13:00 - ON
05.05.2021 42 04.05.2021 13:00 - ON
05.05.2021 43 04.05.2021 13:00 - ON
05.05.2021 44 04.05.2021 13:00 - ON
05.05.2021 45 04.05.2021 13:00 - ON
05.05.2021 46 04.05.2021 13:00 - ON
05.05.2021 47 04.05.2021 13:00 - ON
05.05.2021 48 04.05.2021 13:00 - ON
06.05.2021 40 06.05.2021 11:00 WAIT ON
06.05.2021 41 06.05.2021 10:00 OFF WAIT
06.05.2021 42 04.05.2021 13:00 - ON
06.05.2021 43 04.05.2021 13:00 - ON
06.05.2021 44 04.05.2021 13:00 - ON
06.05.2021 45 04.05.2021 13:00 - ON
06.05.2021 46 04.05.2021 13:00 - ON
06.05.2021 47 04.05.2021 13:00 - ON
06.05.2021 48 04.05.2021 13:00 - ON 
WORKING SOLUTION FOR ME:
With
    Query1 As (
        Select
            *
        From
            devices
    )
select 
    first.deviceid as Device,
    first.changedon as Date,
    first.changedon1 as SubDate,
    first.prevalue,
    first.postvalue
from (
Select
    Concat(Date(devices.changedon), " 23:59:59") As changedon,
    Query1.deviceid,
    Query1.changedon As changedon1,
    Query1.prevalue,
    Query1.postvalue
From
    devices Left Join
    Query1 On Query1.changedon <= devices.changedon
Group By
    devices.changedon,
    Query1.deviceid
order by devices.changedon DESC, changedon1 DESC
) as first
group by Date,Device
order by Date ASC
FeLoN
  • 1
  • 1
  • You're looking for `GROUP BY` clause. – pavel May 06 '21 at 17:42
  • hi pavel, i've tried to make this happen with "GROUP BY" but wasn'T really successfull.:( can you give me some hints? the problem was, that i need every device in all dates... the table needs a join on himself... and i don't know how to work this out... – FeLoN May 06 '21 at 17:46
  • What is **precise** MySQL version? – Akina May 06 '21 at 17:46
  • Does `id_1 > id_2` always matches `date_1 >= date_2`? – Akina May 06 '21 at 17:48
  • @FeLoN: after 3mins you really didn't study `GROUP BY`. You need to combine group by for `deviceID` a `Y-m-d` date. Please add your own effort. – pavel May 06 '21 at 17:48
  • the Version of mysq is:l Ver 15.1 Distrib 10.1.47-MariaDB – FeLoN May 06 '21 at 17:49
  • That's bad, CTE and window functions are not available. – Akina May 06 '21 at 17:50
  • @Akina the "id" from the first table is only generated for the entry itself and not in a relationship with the other data. – FeLoN May 06 '21 at 17:52
  • @Akina when i update mariadb to a newer version would it be possible to achieve it? and how must i do it? sorry for those questions, but i'm not familiar with CTE or window functions. – FeLoN May 06 '21 at 17:54
  • Step 1. Get list of devices (`SELECT DISTINCT device_id`) from 1st copy of table. 2. Get list of dates from 2nd copy of table. 3. CROSS JOIN them, obtain all possible pairs (date, device). 4. Join 3rd copy of table by "the same device and date not above". Group. Find according pair value for last row not above by date. 5. Join 4th copy, select complete row by found device and date. – Akina May 06 '21 at 17:54
  • @pavel thank you for your hints :) i'll try to make it with GROUP BY maybe i missed something, but my group by rules were not working out. i've made a query which shows me the correct status for a certain date, but i wasn'T able to combine the table with itself to achieve the results. i wasn't aware, that i can do this with group by – FeLoN May 06 '21 at 17:56
  • *when i update mariadb to a newer version would it be possible to achieve it?* You may do this on current version - but the solution will be **TOO** complex. IF you'll upgrade to at least 10.2.1 the solution will be much easier. – Akina May 06 '21 at 17:57
  • @Akina WOW thanks for helping :) do i need seperate temporary tables for that, or could this be handled in "ONE" query? i just ask in which way i should do more research :) – FeLoN May 06 '21 at 17:58

0 Answers0