Find out of sequence records

[Ertan Küçükoglu]

Hello,

I am using FirebirdSQL 4.0.1 32Bit on Windows.

I have a table where it receives data from a web service automatically by another software. That data is a POS system data and it must follow an increasing sequence.

Minimized table structure is as follows:

CREATE TABLE ZREPORT

(

  CCODE                        INTEGER NOT NULL,

  ZNO                          INTEGER NOT NULL,

  SERIAL                       VARCHAR(50) NOT NULL,

  ZTIME                        TIMESTAMP NOT NULL,

  ZYEAR                        INTEGER,

  ZMONTH                       INTEGER,

  CONSTRAINT PK_ZREPORT PRIMARY KEY (CCODE, ZNO, SERIAL, ZTIME)

);

I am using following SQL to list all records in a given month:

select

  *

from

  zreport

where

  ccode=:ccode and

  serial=:serial and

  zyear=:zyear and

  zmonth=:zmonth

Minimized list looks like as following:

ZNO ZTIME

1643 28.02.2022 01:30

1642 27.02.2022 01:30

1641 26.02.2022 01:30

1640 25.02.2022 01:30

1639 24.02.2022 01:30

1638 23.02.2022 02:19

1637 22.02.2022 01:30

1636 21.02.2022 01:30

Frequently, I need to check and confirm there is no missing data (all ZNO is in sequence).

If there is missing data I need to list missing ZNO numbers. It can be a separate list or It can be to have ZTIME column with NULL values for missing ZNO numbers and I can easily find them in the list.

I could not find a solution using SQL. Actually, I am not good at window functions, which may be a solution.

I would appreciate any help.

Thanks & Regards,

Ertan

[Dimitry Sibiryakov]

Ertan Küçükoglu wrote 03.04.2022 0:12:
> Frequently, I need to check and confirm there is no missing data (all ZNO is in
> sequence).

If all you need is to check then simple "select zno-1 from zreport z1 where
not exists(select * from zreport z2 where z2.zno=z1.zno-1)" should suit your needs.

--
WBR, SD.

[Ertan Küçükoglu]

Running the SQL returned 3 records

SUBTRACT

1582

196

1226

Only the first row is correct.

196 is listed because it is the very first record in the table. So, there is no earlier.

1226 is listed because it is the very first record of another SERIAL.

CCODE ZNO SERIAL

2 465 2C17457814

2 1227 2A20131202

1582 is really missing

CCODE ZNO SERIAL

1 1583 JH20038639

1 1584 JH20038639

However, I think I can use the result. I can test all listed rows accordingly by building myself a reporting application.

Thanks & Regards,

Ertan

Dimitry Sibiryakov <s...@ibphoenix.com>, 3 Nis 2022 Paz, 01:16 tarihinde şunu yazdı:

--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/276290d6-a015-9fd1-cbb7-419c44fb267a%40ibphoenix.com.

[Virgo Pärna]

On 03.04.2022 01:47, Ertan Küçükoglu wrote:
> 1226 is listed because it is the very first record of another SERIAL.

This is something, that was not mentioned originally. if ZNO is unique
only in specific SERIAL, then the query should be.

select zno-1 from zreport z1 where not exists(select * from zreport z2

where z2.zno=z1.zno-1 and z2.serial = z1.serial)




--
Virgo Pärna
Gaiasoft OÜ
vi...@gaiasoft.ee

[Ertan Küçükoglu]

I didn't understand your post at that time. Wrong filtering just hit me today and I now understood additional filtering on serial column.

Just would like to thank you.

Virgo Pärna <vi...@gaiasoft.ee>, 4 Nis 2022 Pzt, 09:12 tarihinde şunu yazdı:

--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/2db8fde0-2637-fb4f-1ab9-27834eb449f2%40gaiasoft.ee.

[Mark Rotteveel]

On 03-04-2022 00:12, Ertan Küçükoglu wrote:
> Frequently, I need to check and confirm there is no missing data (all
> ZNO is in sequence).
> If there is missing data I need to list missing ZNO numbers. It can be a
> separate list or It can be to have ZTIME column with NULL values for
> missing ZNO numbers and I can easily find them in the list.
>
> I could not find a solution using SQL. Actually, I am not good at
> window functions, which may be a solution.

You can use LEAD or LAG to select a value from the next (or previous)
row in the window.

Something like (haven't tested it):

select ccode, serial, zno
from (
select ccode, serial, zno,
lag(zno, 1, 0) over (partition by ccode, serial order by zno)
previous_zno
from zreport
) a
where zno - previous_zno > 1

Mark
--
Mark Rotteveel