Re: [GENERAL] 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);

   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