blob: 9b724cce202d9886bff8cbf81d9100a3f85e9446 (
plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
|
CREATE VIEW memorin.zoned_devices_logs AS
SELECT
device_serial,
version_id,
zone_id,
latitude AS lat,
longitude AS long,
temperature AS temp,
battery_percentage AS battery,
signal_strength
FROM
memorin.device_logs AS logs
WHERE
device_serial IN (
SELECT serial_number
FROM memorin.devices
WHERE deactivated_at IS NULL
)
AND version_id IN (
SELECT id
FROM memorin.device_versions
WHERE id = logs.version_id
)
AND temperature BETWEEN 34 AND 45
AND signal_strength BETWEEN 0 AND 5
AND battery_percentage BETWEEN 0 AND 100
AND longitude BETWEEN (
SELECT min_longitude
FROM memorin.geographic_zones
WHERE id = logs.zone_id
)
AND (
SELECT max_longitude
FROM memorin.geographic_zones
WHERE id = logs.zone_id
)
AND latitude BETWEEN (
SELECT min_latitude
FROM memorin.geographic_zones
WHERE id = logs.zone_id
)
AND (
SELECT max_latitude
FROM memorin.geographic_zones
WHERE id = logs.zone_id
)
WITH CHECK OPTION;
|
