Listing missing records

Started by Ertan Küçükoğluabout 9 years ago4 messagesgeneral

ertan.kucukoglu@1nar.com.tr

about 9 years ago

Hello,

Using PostgreSQL 9.6.1 on a Windows 10 64bit, EnterpriseDB binaries.

I have following table:
CREATE TABLE report
(
id uuid NOT NULL,
no smallint NOT NULL,
serial character varying(15) NOT NULL,
branchcode character varying(10) NOT NULL,
date timestamp without time zone NOT NULL,
recordtime timestamp without time zone DEFAULT
date_trunc('second'::text, now()),
CONSTRAINT reportdata_pkey PRIMARY KEY (no, branchcode, serial, date)
)

Normally, I should have one record for each "serial, branchcode" set every
day. Unfortunately, for some reason beyond us, we are not getting these
records inserted. I am asked to report missing records in the table so that
we can provide a list to people who are responsible to enter data.

Some details about data:
- serial and branchcode values represents different devices. They are always
same within themselves.
- there may be more than one record in a day for a given serial, branchcode

What I am looking for is a list of serial, branchcode , date columns just
for the missing days.

Some data from table is as follows:
'76522985-14d9-40cb-a34f-8d57793ba83c',146,'JI20049362','50009','2017-02-04
23:21:00','2017-02-13 13:13:58'
'76522985-14d9-40cb-a34f-8d57793ba83c',145,'JI20049362','50009','2017-02-04
22:50:00','2017-02-13 13:13:58'
'76522985-14d9-40cb-a34f-8d57793ba83c',231,'JH20064415','50010','2017-02-04
23:59:00','2017-02-13 13:13:58'
'76522985-14d9-40cb-a34f-8d57793ba83c',265,'JI20033331','50010','2017-02-04
23:58:00','2017-02-13 13:13:58'
'76522985-14d9-40cb-a34f-8d57793ba83c',232,'JH20065321','50010','2017-02-04
23:50:00','2017-02-13 13:13:58'
'9a07616d-ace5-462e-af59-35f3e0ba23a9',233,'JH20064415','50010','2017-02-06
23:59:00','2017-02-13 13:13:58'
'9a07616d-ace5-462e-af59-35f3e0ba23a9',267,'JI20033331','50010','2017-02-06
23:58:00','2017-02-13 13:13:58'
'9a07616d-ace5-462e-af59-35f3e0ba23a9',234,'JH20065321','50010','2017-02-06
23:50:00','2017-02-13 13:13:58'
'909e90f1-177f-4a8a-9108-a1b7697e660f',148,'JI20049362','50009','2017-02-06
23:58:00','2017-02-13 13:13:58'

When looked in detail you can see that there is no record for date
'2017-02-05' above. As a query result I am looking for something like below:
JH20065321, 50010, 2017-02-05, Sunday
JI20033331, 50010, 2017-02-05, Sunday
JH20064415, 50010, 2017-02-05, Sunday
JI20049362, 50009, 2017-02-05, Sunday
JI20049362, 50009, 2017-02-05, Sunday

Thanks & regards,
Ertan Küçükoğlu

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

John R Pierce

pierce@hogranch.com

about 9 years ago

In reply to: Ertan Küçükoğlu (#1)

Re: Listing missing records

On 2/19/2017 2:26 AM, Ertan Küçükoğlu wrote:

What I am looking for is a list of serial, branchcode , date columns just
for the missing days.

so are there other tables with all the valid serial, branchcode values ?

finding missing values generally revolves around doing an anti-join
('NOT IN') with generate_series.

--
john r pierce, recycling bits in santa cruz

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Charles Clavadetscher

clavadetscher@swisspug.org

about 9 years ago

In reply to: Ertan Küçükoğlu (#1)

Re: Listing missing records

As suggested

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Ertan Küçükoglu
Sent: Sonntag, 19. Februar 2017 11:27
To: pgsql-general@postgresql.org
Subject: [GENERAL] Listing missing records

Hello,

Using PostgreSQL 9.6.1 on a Windows 10 64bit, EnterpriseDB binaries.

I have following table:
CREATE TABLE report
(
id uuid NOT NULL,
no smallint NOT NULL,
serial character varying(15) NOT NULL,
branchcode character varying(10) NOT NULL,
date timestamp without time zone NOT NULL,
recordtime timestamp without time zone DEFAULT date_trunc('second'::text, now()),
CONSTRAINT reportdata_pkey PRIMARY KEY (no, branchcode, serial, date)
)

Normally, I should have one record for each "serial, branchcode" set every day. Unfortunately, for some reason
beyond us, we are not getting these records inserted. I am asked to report missing records in the table so that we
can provide a list to people who are responsible to enter data.

Some details about data:
- serial and branchcode values represents different devices. They are always same within themselves.
- there may be more than one record in a day for a given serial, branchcode

What I am looking for is a list of serial, branchcode , date columns just for the missing days.

Some data from table is as follows:
'76522985-14d9-40cb-a34f-8d57793ba83c',146,'JI20049362','50009','2017-02-04
23:21:00','2017-02-13 13:13:58'
'76522985-14d9-40cb-a34f-8d57793ba83c',145,'JI20049362','50009','2017-02-04
22:50:00','2017-02-13 13:13:58'
'76522985-14d9-40cb-a34f-8d57793ba83c',231,'JH20064415','50010','2017-02-04
23:59:00','2017-02-13 13:13:58'
'76522985-14d9-40cb-a34f-8d57793ba83c',265,'JI20033331','50010','2017-02-04
23:58:00','2017-02-13 13:13:58'
'76522985-14d9-40cb-a34f-8d57793ba83c',232,'JH20065321','50010','2017-02-04
23:50:00','2017-02-13 13:13:58'
'9a07616d-ace5-462e-af59-35f3e0ba23a9',233,'JH20064415','50010','2017-02-06
23:59:00','2017-02-13 13:13:58'
'9a07616d-ace5-462e-af59-35f3e0ba23a9',267,'JI20033331','50010','2017-02-06
23:58:00','2017-02-13 13:13:58'
'9a07616d-ace5-462e-af59-35f3e0ba23a9',234,'JH20065321','50010','2017-02-06
23:50:00','2017-02-13 13:13:58'
'909e90f1-177f-4a8a-9108-a1b7697e660f',148,'JI20049362','50009','2017-02-06
23:58:00','2017-02-13 13:13:58'

When looked in detail you can see that there is no record for date '2017-02-05' above. As a query result I am
looking for something like below:
JH20065321, 50010, 2017-02-05, Sunday
JI20033331, 50010, 2017-02-05, Sunday
JH20064415, 50010, 2017-02-05, Sunday
JI20049362, 50009, 2017-02-05, Sunday
JI20049362, 50009, 2017-02-05, Sunday

CREATE TABLE report
(
id uuid NOT NULL,
no smallint NOT NULL,
serial character varying(15) NOT NULL,
branchcode character varying(10) NOT NULL,
date timestamp without time zone NOT NULL,
recordtime timestamp without time zone DEFAULT date_trunc('second'::text, now()),
CONSTRAINT reportdata_pkey PRIMARY KEY (no, branchcode, serial, date)
);

INSERT INTO report VALUES
('76522985-14d9-40cb-a34f-8d57793ba83c',146,'JI20049362','50009','2017-02-04 23:21:00','2017-02-13 13:13:58'),
('76522985-14d9-40cb-a34f-8d57793ba83c',145,'JI20049362','50009','2017-02-04 22:50:00','2017-02-13 13:13:58'),
('76522985-14d9-40cb-a34f-8d57793ba83c',231,'JH20064415','50010','2017-02-04 23:59:00','2017-02-13 13:13:58'),
('76522985-14d9-40cb-a34f-8d57793ba83c',265,'JI20033331','50010','2017-02-04 23:58:00','2017-02-13 13:13:58'),
('76522985-14d9-40cb-a34f-8d57793ba83c',232,'JH20065321','50010','2017-02-04 23:50:00','2017-02-13 13:13:58'),
('9a07616d-ace5-462e-af59-35f3e0ba23a9',233,'JH20064415','50010','2017-02-06 23:59:00','2017-02-13 13:13:58'),
('9a07616d-ace5-462e-af59-35f3e0ba23a9',267,'JI20033331','50010','2017-02-06 23:58:00','2017-02-13 13:13:58'),
('9a07616d-ace5-462e-af59-35f3e0ba23a9',234,'JH20065321','50010','2017-02-06 23:50:00','2017-02-13 13:13:58'),
('909e90f1-177f-4a8a-9108-a1b7697e660f',148,'JI20049362','50009','2017-02-06 23:58:00','2017-02-13 13:13:58');

SELECT * FROM (SELECT serial,branchcode,
generate_series(min(date),max(date),'1 day'::INTERVAL)::DATE AS date
FROM report
GROUP BY serial,branchcode) x
WHERE (x.serial,x.branchcode,x.date) NOT IN
(SELECT report.serial,report.branchcode,report.date::date
FROM report
GROUP BY report.serial,report.branchcode,report.date::date);

serial | branchcode | date
------------+------------+------------
JH20065321 | 50010 | 2017-02-05
JI20049362 | 50009 | 2017-02-05
JI20033331 | 50010 | 2017-02-05
JH20064415 | 50010 | 2017-02-05
(4 rows)

Regards
Charles

Thanks & regards,
Ertan Küçükoğlu

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Ertan Küçükoğlu

ertan.kucukoglu@1nar.com.tr

about 9 years ago

In reply to: Charles Clavadetscher (#3)

Re: Listing missing records

Hi Charles,

Your example worked just fine.

Thank you.

-----Original Message-----
From: Charles Clavadetscher [mailto:clavadetscher@swisspug.org]
Sent: Sunday, February 19, 2017 2:04 PM
To: 'Ertan Küçükoğlu' <ertan.kucukoglu@1nar.com.tr>;
pgsql-general@postgresql.org
Subject: RE: [GENERAL] Listing missing records

As suggested

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Ertan
Küçükoglu
Sent: Sonntag, 19. Februar 2017 11:27
To: pgsql-general@postgresql.org
Subject: [GENERAL] Listing missing records

Hello,

Using PostgreSQL 9.6.1 on a Windows 10 64bit, EnterpriseDB binaries.

I have following table:
CREATE TABLE report
(
id uuid NOT NULL,
no smallint NOT NULL,
serial character varying(15) NOT NULL,
branchcode character varying(10) NOT NULL,
date timestamp without time zone NOT NULL,
recordtime timestamp without time zone DEFAULT

date_trunc('second'::text, now()),

CONSTRAINT reportdata_pkey PRIMARY KEY (no, branchcode, serial,
date)
)

Normally, I should have one record for each "serial, branchcode" set
every day. Unfortunately, for some reason beyond us, we are not
getting these records inserted. I am asked to report missing records in

the table so that we can provide a list to people who are responsible to
enter data.

Some details about data:
- serial and branchcode values represents different devices. They are

always same within themselves.

- there may be more than one record in a day for a given serial,
branchcode

What I am looking for is a list of serial, branchcode , date columns just

for the missing days.

Some data from table is as follows:
'76522985-14d9-40cb-a34f-8d57793ba83c',146,'JI20049362','50009','2017-
02-04
23:21:00','2017-02-13 13:13:58'
'76522985-14d9-40cb-a34f-8d57793ba83c',145,'JI20049362','50009','2017-
02-04
22:50:00','2017-02-13 13:13:58'
'76522985-14d9-40cb-a34f-8d57793ba83c',231,'JH20064415','50010','2017-
02-04
23:59:00','2017-02-13 13:13:58'
'76522985-14d9-40cb-a34f-8d57793ba83c',265,'JI20033331','50010','2017-
02-04
23:58:00','2017-02-13 13:13:58'
'76522985-14d9-40cb-a34f-8d57793ba83c',232,'JH20065321','50010','2017-
02-04
23:50:00','2017-02-13 13:13:58'
'9a07616d-ace5-462e-af59-35f3e0ba23a9',233,'JH20064415','50010','2017-
02-06
23:59:00','2017-02-13 13:13:58'
'9a07616d-ace5-462e-af59-35f3e0ba23a9',267,'JI20033331','50010','2017-
02-06
23:58:00','2017-02-13 13:13:58'
'9a07616d-ace5-462e-af59-35f3e0ba23a9',234,'JH20065321','50010','2017-
02-06
23:50:00','2017-02-13 13:13:58'
'909e90f1-177f-4a8a-9108-a1b7697e660f',148,'JI20049362','50009','2017-
02-06
23:58:00','2017-02-13 13:13:58'

When looked in detail you can see that there is no record for date
'2017-02-05' above. As a query result I am looking for something like

below:

JH20065321, 50010, 2017-02-05, Sunday
JI20033331, 50010, 2017-02-05, Sunday
JH20064415, 50010, 2017-02-05, Sunday
JI20049362, 50009, 2017-02-05, Sunday
JI20049362, 50009, 2017-02-05, Sunday

CREATE TABLE report
(
id uuid NOT NULL,
no smallint NOT NULL,
serial character varying(15) NOT NULL,
branchcode character varying(10) NOT NULL,
date timestamp without time zone NOT NULL,
recordtime timestamp without time zone DEFAULT
date_trunc('second'::text, now()),
CONSTRAINT reportdata_pkey PRIMARY KEY (no, branchcode, serial, date) );

INSERT INTO report VALUES
('76522985-14d9-40cb-a34f-8d57793ba83c',146,'JI20049362','50009','2017-02-04
23:21:00','2017-02-13 13:13:58'),
('76522985-14d9-40cb-a34f-8d57793ba83c',145,'JI20049362','50009','2017-02-04
22:50:00','2017-02-13 13:13:58'),
('76522985-14d9-40cb-a34f-8d57793ba83c',231,'JH20064415','50010','2017-02-04
23:59:00','2017-02-13 13:13:58'),
('76522985-14d9-40cb-a34f-8d57793ba83c',265,'JI20033331','50010','2017-02-04
23:58:00','2017-02-13 13:13:58'),
('76522985-14d9-40cb-a34f-8d57793ba83c',232,'JH20065321','50010','2017-02-04
23:50:00','2017-02-13 13:13:58'),
('9a07616d-ace5-462e-af59-35f3e0ba23a9',233,'JH20064415','50010','2017-02-06
23:59:00','2017-02-13 13:13:58'),
('9a07616d-ace5-462e-af59-35f3e0ba23a9',267,'JI20033331','50010','2017-02-06
23:58:00','2017-02-13 13:13:58'),
('9a07616d-ace5-462e-af59-35f3e0ba23a9',234,'JH20065321','50010','2017-02-06
23:50:00','2017-02-13 13:13:58'),
('909e90f1-177f-4a8a-9108-a1b7697e660f',148,'JI20049362','50009','2017-02-06
23:58:00','2017-02-13 13:13:58');

SELECT * FROM (SELECT serial,branchcode,
generate_series(min(date),max(date),'1
day'::INTERVAL)::DATE AS date
FROM report
GROUP BY serial,branchcode) x WHERE
(x.serial,x.branchcode,x.date) NOT IN
(SELECT report.serial,report.branchcode,report.date::date
FROM report
GROUP BY report.serial,report.branchcode,report.date::date);

Regards
Charles

Thanks & regards,
Ertan Küçükoğlu

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make

changes to your subscription:

http://www.postgresql.org/mailpref/pgsql-general

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general