Sql

Search

SQL: find gap in sequence

WITH CTE_CheckRegister (AccountNumber, CheckNumber, NextCheckNumber)
AS
(
   SELECT AccountNumber,
          CheckNumber,
          Lead(CheckNumber, 1) OVER (PARTITION BY AccountNumber ORDER BY CheckNumber) NextCheckNumber
   FROM @CheckRegister
)
SELECT   AccountNumber,
         CASE
            WHEN NextCheckNumber is NULL THEN CheckNumber
            WHEN (CheckNumber = NextCheckNumber - 1) THEN CheckNumber
            ELSE CheckNumber + '***'
         END as CheckNumber
FROM     CTE_CheckRegister
ORDER BY AccountNumber, CheckNumber

Comment

PREVIOUS	NEXT

Code Example
Sql :: clickhouse greatest non-aggregate
Sql :: delete duplicates based on 2 columns postgres
Sql :: ejecutar script sql
Sql :: in sql developer where equal queres
Sql :: create table in dbms cmds
Sql :: mysqldump error --no-beep
Sql :: ring SQLite create a SQLite database, add new records then display the data
Sql :: postgres insert knex
Sql :: sql count and addition by day
Sql :: nosql databases
Sql :: sql truncate number
Sql :: sql compiler
Sql :: postgresql createdb
Sql :: how to delete data from database in php
Sql :: sql first day quarter
Sql :: where sqlalchemy
Sql :: how to format tables in sqlplus
Sql :: sqlalchemy one column of two has to be not null
Csharp :: asp.net data annotations email
Csharp :: hello world program in c#
Csharp :: unity check if space pressed
Csharp :: how to get ip address in c#
Csharp :: unity get layer of gameobject
Csharp :: unity play particle system
Csharp :: unity foreach dictionary
Csharp :: c# random color
Csharp :: Linq - Random Elements
Csharp :: read file c#
Csharp :: unity 2d detect click on sprite
Csharp :: how to get rigidbody speed in unity

Search

SQL

SQL: find gap in sequence

ADD CONTENT