Forum Discussion

Ajaiswal84's avatar
Ajaiswal84
Copper Contributor
Oct 25, 2024

compare cell 2 with cell 1 and show result

Dear All,

 

I need your help.

I have below sample data and want to compare Cell F (column Snap_Id) with previous row value.

e.g. row_num 2 should compare with row_num 1 and if it is matching it should say "No" and if it is not matching like row_num 3 compare with row_num 2 then it should say "Yes".

Can you please help me to write SQL statement to achieve it. I tried self join but then it is returning me 12 records with incorrect data.

The final output I am expecting is as below,

Hope you can help me with this.

Thanks in advance.

 

Regards,

Abhishek

 

  • rodgerkong's avatar
    rodgerkong
    Oct 25, 2024

    Ajaiswal84 

    You can use OVER clause and windows function to resolve it.

    1. Use OVER and ROWS to get one previous row.
    2. Since it has to use aggregate function with OVER clause, and there only one row in window, so it can use either MIN() OR MAX() to get the value of column Snap_Id.
    3. NULL value cannot compare with other value, even NULL it self, so before the comparation, use ISNULL() convert NULL to a constaint value that never be used in bussness logic, I choose -1, and you can choose other value base on your logic.
    4. Use IIF or CASE compare and output Result.
    5. In your sample, the result of first row, you give it NO as value. But I think YES is more reasonable, and the OVER ROWS return YES default. Whatever, you can force it return NO as your wish, I left a commented IIF function, you can use it to get NO for first row.
    6. Your sample shown the result of row_num 2 as YES, I guess it must be a mistake.
    7. Test table I created is simple than yours, but all key columns are included.

    Here is code:

    CREATE TABLE #test
(
	[Id] INT,
	[CaseId] INT,
	[User] VARCHAR(10),
	[Snap_Id] INT,
	[row_num] INT
)

INSERT INTO #test
VALUES
 (11, 100, 'Raj', null, 1)
,(22, 100, 'Mike', null, 2)
,(33, 100, 'RJ', 33, 3)
,(44, 100, 'Mike', null, 4)
,(55, 100, 'RJ', 55, 5)
,(55, 100, 'RJ', 55, 6)


SELECT *, 
--IIF( row_num = 1,  --Deal with first row, result should be NO
--	'NO', 
	IIF(MIN(ISNULL(Snap_id, -1)) OVER(ORDER BY row_num ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) = ISNULL([Snap_Id], -1),  --NULL value can't be compared, so convert it to a value that never used in buss-logic, I use -1 here.
		'NO', 
		'YES'
	)
--)
AS Result 
FROM #test

DROP TABLE IF EXISTS #test

     

  • rodgerkong's avatar
    rodgerkong
    Iron Contributor
    Oct 25, 2024

    Ajaiswal84 

    This can be resolved by OVER ROWS clause and windows function.

    1. Use OVER ROWS to retrive 1 row before current row.
    2. Since it has to use aggregate function with OVRE, and there is only 1 row in window, so it can use either MIN() or MAX() to get the value of Snap_id of previous row.
    3. NULL value cannot compare with other values, even with NULL itself, so before the comparation, it has to use ISNULL convert NULL to a constain value that never be used in business logic, I choose -1.
    4. Use IIF/CASE to compare, and output result.
    5. You assgined a NO in the result of first row in your sample. I think YES will be more reasonable, and it is the return value of OVER ROWS. Whatever, you can force it output as NO as your wish, I left a commented IIF function in code, you can use it to do this.
    6. The result of 2nd row in your sample is YES, I think it must be a mistake.

    Code is here:

     

    CREATE TABLE #test
(
	[Id] INT,
	[CaseId] INT,
	[User] VARCHAR(10),
	[Snap_Id] INT,
	[row_num] INT
)

INSERT INTO #test
VALUES
 (11, 100, 'Raj', null, 1)
,(22, 100, 'Mike', null, 2)
,(33, 100, 'RJ', 33, 3)
,(44, 100, 'Mike', null, 4)
,(55, 100, 'RJ', 55, 5)
,(55, 100, 'RJ', 55, 6)


SELECT *, 
--IIF( row_num = 1,  --Deal with first row, result should be NO
	--'NO', 
	IIF(MIN(ISNULL(Snap_id, -1)) OVER(ORDER BY row_num ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) = ISNULL([Snap_Id], -1),  --NULL value can't be compared, so convert it to a value that never used in buss-logic, I use -1 here.
		'NO', 
		'YES'
	)
--)
AS Result 
FROM #test

DROP TABLE IF EXISTS #test

     

  • rodgerkong's avatar
    rodgerkong
    Iron Contributor
    Oct 25, 2024

    Ajaiswal84 

    You can use OVER clause and windows function to resolve it.

    1. Use OVER and ROWS to get one previous row.
    2. Since it has to use aggregate function with OVER clause, and there only one row in window, so it can use either MIN() OR MAX() to get the value of column Snap_Id.
    3. NULL value cannot compare with other value, even NULL it self, so before the comparation, use ISNULL() convert NULL to a constaint value that never be used in bussness logic, I choose -1, and you can choose other value base on your logic.
    4. Use IIF or CASE compare and output Result.
    5. In your sample, the result of first row, you give it NO as value. But I think YES is more reasonable, and the OVER ROWS return YES default. Whatever, you can force it return NO as your wish, I left a commented IIF function, you can use it to get NO for first row.
    6. Your sample shown the result of row_num 2 as YES, I guess it must be a mistake.
    7. Test table I created is simple than yours, but all key columns are included.

    Here is code:

    CREATE TABLE #test
(
	[Id] INT,
	[CaseId] INT,
	[User] VARCHAR(10),
	[Snap_Id] INT,
	[row_num] INT
)

INSERT INTO #test
VALUES
 (11, 100, 'Raj', null, 1)
,(22, 100, 'Mike', null, 2)
,(33, 100, 'RJ', 33, 3)
,(44, 100, 'Mike', null, 4)
,(55, 100, 'RJ', 55, 5)
,(55, 100, 'RJ', 55, 6)


SELECT *, 
--IIF( row_num = 1,  --Deal with first row, result should be NO
--	'NO', 
	IIF(MIN(ISNULL(Snap_id, -1)) OVER(ORDER BY row_num ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) = ISNULL([Snap_Id], -1),  --NULL value can't be compared, so convert it to a value that never used in buss-logic, I use -1 here.
		'NO', 
		'YES'
	)
--)
AS Result 
FROM #test

DROP TABLE IF EXISTS #test

     

Resources