Find out of sequence records

25 views
Skip to first unread message

Ertan Küçükoglu

unread,
Apr 2, 2022, 6:12:34 PMApr 2
to firebird...@googlegroups.com
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

unread,
Apr 2, 2022, 6:16:59 PMApr 2
to firebird...@googlegroups.com
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

unread,
Apr 2, 2022, 6:47:26 PMApr 2
to firebird...@googlegroups.com
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

unread,
Apr 4, 2022, 2:12:26 AMApr 4
to firebird...@googlegroups.com
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

unread,
Jul 21, 2022, 5:10:17 PMJul 21
to firebird...@googlegroups.com
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.

Mark Rotteveel

unread,
Jul 22, 2022, 6:02:09 AMJul 22
to firebird...@googlegroups.com
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
Reply all
Reply to author
Forward
0 new messages