Help to identify which has wrong sequence and gap

Question

Hi, I want to identify those rows of SEQT and Seqdate which is not in sequence or have gap. See below criteria for SeqT and Seqdate.

SeqT: The number should be in sequence and should not be any gap. Should not be gap more than 1

Seqdate: The next date should be greater than previous date.

My SQL Version is 2012.

My table Temp:

Create table Temp (SeqT number, Seqdate datetime, Pdate datetime)

Insert into Temp values (1, '2023-01-01','2023-01-02')

Insert into Temp values (2, '2023-02-01','2023-02-02')

Insert into Temp values (3, '2023-03-01','2023-03-02')

Insert into Temp values (4, '2023-04-01',' 2023-04-02')

Insert into Temp values (5, '2022-05-01','2022-05-02')

Insert into Temp values (6, '2023-06-01','2023-06-02')

Insert into Temp values (8, '2023-07-01','2023-07-02')

Insert into Temp values (7, '2023-08-01','2023-08-02')

Insert into Temp values (9, '2023-09-01','2023-09-02')

Select * from Temp

SeqT Seqdate Pdate

1 2023-01-01 2023-01-02

2 2023-02-01 2023-02-02

3 2023-03-01 2023-03-02

4 2023-04-01 2023-04-02

5 2022-05-01 2022-05-02

6 2023-06-01 2023-06-02

8 2023-07-01 2023-07-02

7 2023-08-01 2023-08-02

9 2023-09-01 2023-09-02

Expected Ressult:

SeqT Seqdate Pdate

5 2022-05-01 --The date (Seqdate) is not greater than previous date. The Seqdate should be> from prev Seqdate.

8 2023-07-01 -- The SEQT is wrong. It should be > from prev: No. and should not gap more than 1.

7 2023-08-01 -- The SEQT is wrong. It should be > from prev: No. and should not gap more than 1.

Answer 1

Hi @Martin Kevin

Suppose the Seqt column is the sequence column in your sample. And let us add gap Seqt value Insert into Temp values (11, '2023-10-01','2023-10-02') in the query.

Then check this query:

;WITH CTE AS
(
SELECT *,LAG(Seqdate)OVER(ORDER BY SeqT) AS Previous_Seqdate
        ,LAG(SeqT)OVER(ORDER BY SeqT) AS Previous_SeqT
FROM Temp
)
SELECT SeqT,Seqdate,Pdate
FROM CTE
WHERE Seqdate<Previous_Seqdate OR SeqT-Previous_SeqT>1

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Your question is not very clear, but maybe this can get you started:

;WITH CTE AS (
   SELECT *, Prev_date = LAG(Seqdate) OVER(ORDER BY SeqT) 
   FROM   Temp
)
SELECT *
FROM   CTE
WHERE  datediff(MONTH, Prev_date, Seqdate)<> 1