How to update status with message wrong sequence for all numbers where there are numbers remaining or down on sequence?

I work on SQL server2012 I face issue I can't stop insert data on group table when Numbers not complete sequence or have numbers not exist or down .

as example

seqManual are 1,2,3,4 per only groupseq B so that Insert data success to table groups .

but if sequence are 1,2,4,5,7 so that not inserted because it have some numbers down or not exist on sequence

so i will update status by message wrong sequence

How to Update status per all Numbers where it have some numbers down or not exist on sequence ?
and not insert it .

 create table #groupsseq
 (
 groupseq nvarchar(20),
 SeqManual int,
 status nvarchar(100)
 )
 insert into #groupsseq(groupseq,SeqManual)
 values
 ('A',1),
 ('A',2),
 ('A',4),
 ('A',5)
    
 create table #Groups
 (
 groupName nvarchar(20),
 SeqManual int
 )

Expected Result

 groupseq    SeqManual    Status
 A    1    Wrong Sequence
 A    2    Wrong Sequence
 A    4    Wrong Sequence
 A    5    Wrong Sequence

but if 1,2,3,4 and groupseq B
so result will be

groupseq SeqManual Status
B 1 success
B 2 success
B 3 success
B 4 success

So How to do that please ?

Hi Ahmed

There are several ways to do this ..

Here is one

; with cte as 
(
	select 
		groupseq , case when abs(seqmanual - lead(seqmanual) over( partition by groupseq order by groupseq , seqmanual)) > 1 then 'Wrong Sequence' end as Wrong  
	from 
	    #groupsseq
) , cte_ok as
(
	select * from cte where Wrong is not null 
)
select 
	a.groupseq, a.SeqManual, isnull(b.Wrong,'Right Sequence') 
From 
	#groupsseq a  left join cte_ok b on a.groupseq = b.groupseq

image

hi

i had to add DISTINCT as there may be more than 1 wrong sequence in Group

; with cte as 
(
	select 
		groupseq , case when abs(seqmanual - lead(seqmanual) over( partition by groupseq order by groupseq , seqmanual)) > 1 then 'Wrong Sequence' end as Wrong  
	from 
	    #groupsseq
) , cte_ok as
(
	select distinct * from cte where Wrong is not null 
)
select 
	a.groupseq, a.SeqManual, isnull(b.Wrong,'Right Sequence') 
From 
	#groupsseq a  left join cte_ok b on a.groupseq = b.groupseq

Ahmed

No response AS Usual

:laughing:

thank you for reply
sorry for late

I guess I don't understand why you think you need to identify each row with a message.

First, if you insisted on updating each row, it should be (in this case) in a bit (yech!) or tinyint column labeled something like "HasBadSeq" with possible values of 0 or 1. It just doesn't make sense to waste all the bytes for a duplicated message.

Second, if you're going to use it as a control for transferring only those GroupSeq's that have good sequences, do that at run time instead of wasting and entire column along with an update of that column. It's super easy to identify sequences as either having a bad sequence or not and simply joining to a temp table with that information in it. The table is comparatively tiny compared to all the bloody messages you want to store. For example...

 SELECT  GroupSeq
        ,IsBadSeq = ABS(SIGN((POWER(CONVERT(BIGINT,COUNT(*)),2)+COUNT(*))/2 - SUM(SeqManual)))
   INTO #BadSeqCheck
   FROM #GroupsSeq
  GROUP BY GroupSeq
;

This formula calculates what the sum of a sequence (starting at 1) should be based only on the number of entries in the sequence and is included above. It's the "Triangular Join" formula.

ABS(SIGN((POWER(CONVERT(BIGINT,COUNT(*)),2)+COUNT(*))/2