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