Forum Discussion

Martin_Kevin's avatar
Martin_Kevin
Copper Contributor
Aug 07, 2024

Help to find in sequence issue in groups records

Hi, Every group_cost has different sequence of PP_Seq but it should not be any gap in the sequence of any group_cost rcords. It should always be increment by 1. I want to see any record/row of any group_cost if it has any issue in pp_seq and not increment by 1. I want to see when the issue of sequence is started then it will be corrected with other next rows. 

 

Create table #Cost (group_cost char(10), PP_Seq int, date1 datetime, ST char(2))

insert into #cost ('T1',5,'2023-01-01','A')

insert into #cost ('T1',6,'2023-02-01','A')

insert into #cost ('T1',7,'2023-03-01','A')

 

insert into #cost ('T2',12,'2023-01-01','A')

insert into #cost ('T2',14,'2023-02-01','B')  -- This pp_seq is not increment by 1. Should be in output 

insert into #cost ('T2',15,'2023-03-01','A')

 

insert into #cost ('T3',25,'2023-01-02','A')

insert into #cost ('T3',26,'2023-02-01','A')

insert into #cost ('T3',27,'2023-03-05','A')

 

insert into #cost ('T5',65,'2023-01-01','A')

insert into #cost ('T5',66,'2023-02-06','A')

insert into #cost ('T5',67,'2023-03-04','W')

insert into #cost ('T5',69,'2023-04-01','A') -- This pp_seq is not increment by 1. Should be in output 

 

--Expected result

group_cost    PP_Seq      date1                ST        Comments

T2                  14             2023-02-01       B           PP_Seq is not in seq. Also check next rows to correct. 

T5                   69            2023-04-01       A          PP_Seq is not in seq. Also check next rows to correct. 

  • rodgerkong's avatar
    rodgerkong
    Aug 08, 2024

    1. Add a continued sequence with ROW_NUMBER, named PP_seq_correct;

    2. Calculate differences of PP_seq and PP_seq_correct, get the difference of first element in each group_cost set as stand value.

    3. Filter the rows that the differences not equal the stand value, they are all incorrect.

    4. Get the first row from the incorrect rows grouping by group_cost.

     

    WITH T (group_cost, PP_Seq, Seq_no, diff) AS
(
  SELECT c.group_cost, c.PP_seq, a.PP_seq_correct, c.PP_seq-a.PP_seq_correct AS diff 
  FROM #Cost c
  INNER JOIN
  (
    SELECT group_cost, PP_Seq, ROW_NUMBER() OVER(PARTITION BY group_cost ORDER BY PP_seq) AS PP_seq_correct FROM #Cost
  ) a
  ON c.group_cost=a.group_cost AND c.PP_seq=a.PP_seq
)
SELECT Co.*, 'Comments...' Comments FROM #cost Co INNER JOIN
(
  SELECT T.group_cost, Min(PP_Seq) PP_Seq FROM T LEFT JOIN
  (
    SELECT T.group_cost, T.diff FROM T WHERE T.seq_no=1
  ) D
  ON T.group_cost=D.group_cost AND T.diff=D.Diff
  WHERE D.diff IS NULL
  GROUP BY T.group_cost
)R
ON Co.group_cost=R.group_cost AND Co.PP_Seq=R.PP_Seq

     

     

  • rodgerkong's avatar
    rodgerkong
    Iron Contributor
    Aug 08, 2024

    1. Add a continued sequence with ROW_NUMBER, named PP_seq_correct;

    2. Calculate differences of PP_seq and PP_seq_correct, get the difference of first element in each group_cost set as stand value.

    3. Filter the rows that the differences not equal the stand value, they are all incorrect.

    4. Get the first row from the incorrect rows grouping by group_cost.

     

    WITH T (group_cost, PP_Seq, Seq_no, diff) AS
(
  SELECT c.group_cost, c.PP_seq, a.PP_seq_correct, c.PP_seq-a.PP_seq_correct AS diff 
  FROM #Cost c
  INNER JOIN
  (
    SELECT group_cost, PP_Seq, ROW_NUMBER() OVER(PARTITION BY group_cost ORDER BY PP_seq) AS PP_seq_correct FROM #Cost
  ) a
  ON c.group_cost=a.group_cost AND c.PP_seq=a.PP_seq
)
SELECT Co.*, 'Comments...' Comments FROM #cost Co INNER JOIN
(
  SELECT T.group_cost, Min(PP_Seq) PP_Seq FROM T LEFT JOIN
  (
    SELECT T.group_cost, T.diff FROM T WHERE T.seq_no=1
  ) D
  ON T.group_cost=D.group_cost AND T.diff=D.Diff
  WHERE D.diff IS NULL
  GROUP BY T.group_cost
)R
ON Co.group_cost=R.group_cost AND Co.PP_Seq=R.PP_Seq

     

     

Resources