Categories
Database Development

CASE Statement returning unpredictable results

First time using Postgresql and building a query with a CASE statement. I am trying to end up with a collection of data whereby one of the columns in the data (status) qualifies the row based on conditions.

I have 3 tables (in a Posqtgresql DB) as follows:

Table devices:

id serial_number retired last_reported_utc_at other_data
2 1234 FALSE 2020-12-01 15:34:23
5 4567 FALSE 2020-12-01 15:34:23
6 NULL FALSE NULL

Table device_measurements

id device_id reading read_utc_at
1 5 1.36 2020-12-01 15:34:23
2 2 2.45 2020-12-12 18:21:12
3 6 1.87 2020-12-03 20:29:12

Table devices_meters
Note: This table links the device to a meter location. the activated and deactivated dates will sequence the history of the device to meters.

id device_id meter_id reading activated_utc_at deactivated_utc_at
1 5 23 1.36 2018-10-01 15:34:23 NULL
2 2 17 2.45 2019-06-12 18:21:12 NULL
3 6 17 2.45 2019-06-12 18:21:12 NULL

Each device could have the following status:

active, online, offline, inactive, retired 

I have been trying, via a CASE statement in the SELECT to generate a column (status) where I flag the row as one of the above. The query runs, but the result is unpredictable.

My CASE statement is:

   CASE
        // Device has reported back last 24hrs and has non zero readings for data in this period
        WHEN
            devices.retired = false
            AND MAX(device_measurements.reported_utc_at) BETWEEN :yesterdayFilter AND :nowFilter
            AND SUM(device_measurements.flow) > 0
            THEN 'active'

        // Device has reported back last 24hrs and has all zero readings for data in this period
        WHEN
            devices.retired = false
            AND MAX(device_measurements.reported_utc_at) BETWEEN :yesterdayFilter AND :nowFilter
            AND SUM(device_measurements.flow) = 0
            THEN 'online'

        // Device has not reported back last 24 hrs
        WHEN
            devices.retired = false
            AND MAX(device_measurements.reported_utc_at) < :yesterdayFilter
            THEN 'offline'

        // Device with null serial number
        WHEN devices.retired = false
            AND devices.serial_number IS NULL
            THEN 'inactive'

        // Device retired boolean column flag set to true
        WHEN devices.retired = true
            THEN 'retired'
    END AS status,

:nowFilter contains a date of now() in UTC

:yesterdayFilter contains a date of now()->subHours(24)

The above does not fail but returns unpredictable results, such an offline device which has had a data report less than 24 hrs ago.

Any help is really appreciated.

The whole query is below:

select
    "devices".*,
    CASE
        WHEN devices.retired = false
        AND MAX(device_measurements.reported_utc_at) BETWEEN :yesterdayFilter
        AND :nowFilter
        AND SUM(device_measurements.flow) > 0 THEN 'active'
        WHEN devices.retired = false
        AND MAX(device_measurements.reported_utc_at) BETWEEN :yesterdayFilter
        AND :nowFilter
        AND SUM(device_measurements.flow) = 0 THEN 'online'
        WHEN devices.retired = false
        AND MAX(device_measurements.reported_utc_at) < :yesterdayFilter THEN 'offline'
        WHEN devices.retired = false
        AND devices.serial_number IS NULL THEN 'inactive'
        WHEN devices.retired = true THEN 'retired'
    END AS status,
    MAX(device_measurements.reported_utc_at) AS last_reported_utc_at
from
    "devices"
    left join "devices_meters" on "devices_meters"."device_id" = "devices"."id"
    left join "device_measurements" on "device_measurements"."device_id" = "devices"."id"
group by
    "devices"."id"
having
    (
        CASE
            WHEN devices.retired = false
            AND MAX(device_measurements.reported_utc_at) BETWEEN :yesterdayFilter
            AND :nowFilter
            AND SUM(device_measurements.flow) > 0 THEN 'active'
            WHEN devices.retired = false
            AND MAX(device_measurements.reported_utc_at) BETWEEN :yesterdayFilter
            AND :nowFilter
            AND SUM(device_measurements.flow) = 0 THEN 'online'
            WHEN devices.retired = false
            AND MAX(device_measurements.reported_utc_at) < :yesterdayFilter THEN 'offline'
            WHEN devices.retired = false
            AND devices.serial_number IS NULL THEN 'inactive'
            WHEN devices.retired = true THEN 'retired'
        END
    ) = :status
order by
    "devices"."installed_at" desc
limit
    10 offset 0

Leave a Reply

Your email address will not be published. Required fields are marked *