What is the correct query?

user_14669569 0
Viewed 81

What is the correct query for this requirement?

Assume there is an object which has to go under a checking procedure every few month to be validated. The outgoing information of this procedure is stored in postgres. When the object is checked again, it is validated again. An object may be revalidated within its own (near ending) validation date interval.

Requirement: Select all records which have not been revalidated.

My work so fare: Select all objects which are not valid. But i cannot (with my knowledge) exclude the objects which have been revalidated.

The outcome of this dbfiddle query is an record which has been revalidated an should therefore not show. Thank you for help.

https://www.db-fiddle.com/f/ocHQNXQJKUPKYGN35LBeFK/3

Important columns are here:

  1. device_serial_no: identifacation of the object
  2. validated: the date on which the object has been validated
  3. validated_for_month: the interval in month which has to pass, respective the validated column date, for becoming not valid. So its valid for validated + concat(valitated_for_month, ' month')::interval

Query 1: Data set

Query 2: Wrong results, object has been revalidated.

Expected: No object shows up.

Schema (PostgreSQL v9.5)

CREATE TABLE public.einrichtungen (
    id serial,
    messplatz varchar(255) NOT NULL,
    gegenstand varchar(255) NOT NULL,
    validated timestamp NOT NULL,
    valitated_for_month int8 NOT NULL,
    active bool NOT NULL,
    device_serial_no varchar(255) NOT NULL
);

insert into einrichtungen(messplatz, gegenstand, validated, valitated_for_month, active, device_serial_no) values ('HV1', 'Messuhr', '2020-04-08 10:37:59', 24, true, 'SDHFEJ');

insert into einrichtungen(messplatz, gegenstand, validated, valitated_for_month, active, device_serial_no) values ('HV1', 'Messuhr', '2020-03-01 10:37:59', 12, true, 'XX1');

insert into einrichtungen(messplatz, gegenstand, validated, valitated_for_month, active, device_serial_no) values ('HV1', 'Messuhr', '2021-02-15 11:12:19', 12, true, 'XX1');

insert into einrichtungen(messplatz, gegenstand, validated, valitated_for_month, active, device_serial_no) values ('HV1', 'Messuhr', '2020-04-08 10:37:59', 24, true, 'JSDFIE');

Query #1

select * from einrichtungen;
id messplatz gegenstand validated valitated_for_month active device_serial_no
1 HV1 Messuhr 2020-04-08T10:37:59.000Z 24 true SDHFEJ
2 HV1 Messuhr 2020-03-01T10:37:59.000Z 12 true XX1
3 HV1 Messuhr 2021-02-15T11:12:19.000Z 12 true XX1
4 HV1 Messuhr 2020-04-08T10:37:59.000Z 24 true JSDFIE

Query #2

select * from einrichtungen where validated + concat(valitated_for_month, ' month')::interval < now();
id messplatz gegenstand validated valitated_for_month active device_serial_no
2 HV1 Messuhr 2020-03-01T10:37:59.000Z 12 true XX1

View on DB Fiddle

postgresql sql
1 Answers

This is not perfect, because I don't completely understand the question, but it will get you started. I also omitted the irrelevant columns.

CREATE TABLE einrichtungen (
    id serial PRIMARY KEY
    , messplatz varchar(255) NOT NULL
    , gegenstand varchar(255) NOT NULL
    , validated timestamp NOT NULL
    , validated_for_month int8 NOT NULL
    , active bool NOT NULL
    , device_serial_no varchar(255) NOT NULL
        );

insert into einrichtungen(messplatz, gegenstand, validated, validated_for_month, active, device_serial_no) values
('HV1', 'Messuhr', '2020-04-08 10:37:59', 24, true, 'SDHFEJ')
, ('HV1', 'Messuhr', '2010-03-01 10:37:59', 12, true, 'XX1') -- extra
, ('HV1', 'Messuhr', '2020-03-01 10:37:59', 12, true, 'XX1')
, ('HV1', 'Messuhr', '2021-02-15 11:12:19', 12, true, 'XX1')
, ('HV1', 'Messuhr', '2020-04-08 10:37:59', 24, true, 'JSDFIE')
        ;

SELECT *
FROM einrichtungen e
WHERE 1=1
AND e.validated + e.validated_for_month * '1 mon'::interval < now()
AND e.active
AND NOT EXISTS (
        SELECT * FROM einrichtungen nx
        WHERE nx.device_serial_no = e.device_serial_no
        AND nx.validated > e.validated
        AND nx.validated <= e.validated + e.validated_for_month * '1 mon'::interval
        );
user_9059020
Related