Help in Counter and reverse query

Question

Hi, I don't want any gap in Pcounter column's data and should increment by 1 from top
and also don't want any gap in Preverse column's data and it should increment by -1 from top.
If Pcounter data is not increment by 1 then should mention in the output with comment Similarly
If Preverse data is not increment by -1 then should mention in the output with comment.

Create table #DT (PN number varchar(4), Pcounter int, Pdate datetime, preverse int)
Insert into #DT values ('111',20, '01-01-2021',20)
Insert into #DT values ('111',21, '02-01-2021',19)
Insert into #DT values ('111',22, '03-01-2021',18)
Insert into #DT values ('111',22, '04-01-2021',17)
Insert into #DT values ('111',23, '05-01-2021',15)

Required Output --
PN Pcounter Pdate Preverse Comments
111 20 01-01-2021 20 OK
111 21 02-01-2021 19 OK
111 22 03-01-2021 18 OK
111 22 04-01-2021 17 Pcounter and Preverse is wrong
111 23 05-01-2021 15 Pcounter and Preverse is wrong

Answer 1

Hi,

(1) You INSERT string into DATETIME column without informing the server what is the format of the string!

In one server the string '04-01-2021' will be implicitly converted to Jan 4th 2021 and in another server the same string will be converted into Apr 1th 2021

You should never INSERT data like this! If you are using string to represent date then you must explicitly CONVERT it to DATETIME using the style of the string format. For example instead of '04-01-2021' you should use CONVERT(DATETIME,'04-01-2021',105) if the format is "dd-mm-yyyy" or use for example CONVERT(DATETIME,'04-01-2021', 110) if the format is "mm-dd-yyyy"

Check the different in the forulowing query:

SELECT CONVERT(DATETIME,'04-01-2021',105), CONVERT(DATETIME,'04-01-2021',111)  
GO  

At this time I have not idea what are the values in your demo

(2) You INSERT data into the table without informing the server to which columns you insert the data, which is a very VERY bad coding and can lead to multiple issues!

You should not use something like "Insert into DT values " but "Insert into DT (list of columns in the write order according to the values you insert!) values "

(3) Not clear to me why you get the following row 111 22 04-01-2021 17 Pcounter and Preverse is wrong since it seems like the Preverse fits. It is 17 and above it we have 18-19-20 so it fits. Maybe you meant to use "Pcounter OR Preverse is wrong"

----------

According to your description and my understanding using some assumptions/guess Please check if one of the following solve your need:

;WITH MyCTE AS (  
 SELECT PN,Pcounter,Pdate,Preverse   
 ,L_Pcounter = MIN(Pcounter) OVER (PARTITION BY PN ORDER BY PDate) + ROW_NUMBER() OVER (PARTITION BY PN ORDER BY PDate) - 1  
 ,L_Preverse = MAX(Preverse) OVER (PARTITION BY PN ORDER BY PDate) - ROW_NUMBER() OVER (PARTITION BY PN ORDER BY PDate) + 1  
 FROM DT  
)  
SELECT  PN,Pcounter,Pdate,Preverse,   
 Comment = CASE  
 WHEN NOT Pcounter = L_Pcounter or NOT preverse = L_Preverse THEN 'Pcounter and Preverse is wrong'  
 ELSE 'OK'  
 END  
FROM MyCTE  
GO  

Or this one:

SELECT PN,Pcounter,Pdate,Preverse   
 ,COMMENT = CASE  
 WHEN   
 (Pcounter - ISNULL(LAG(Pcounter) OVER (PARTITION BY PN ORDER BY PDate), Pcounter-1)) = 1   
 AND (ISNULL(LAG(Preverse) OVER (PARTITION BY PN ORDER BY PDate), Preverse+1)) - Preverse = 1  
 THEN 'OK'  
 ELSE 'Pcounter and Preverse is wrong'  
 END  
FROM DT  

Both returns the same result which means nothing when we have only 5 rows which does not cover all the options. These queries use different logic and if non of these fit you then please provide several more rows and explain exactly how you get the expected result from the sample data - row by row