How to check if the two values exist in either cell1 or cell 2 in groups of 2

Question

I need a way to compare cells in blocks of two's. Need compare D2 against D3, D4 againt D5 ... D10 against D11.

If two individual cells like D2 or D3 equal "Business" or "Compliance", then return true.

If a block has identical values like "Business" in cells D6 & D7 or "Compliance" like in cell D10 & D11, then the code should return fail.

I am trying to use a variation of this code, but I can't get it to work propertly.

create table data
(
  ID int,
  [Col A]  nvarchar(10),
  [Col B]  nvarchar(10),
  [Col C] nvarchar(10)
);

insert into data (ID, [Col C]) values
(1, 'Business'),
(2, 'Compliance'),
(3, 'Compliance'),
(4, 'Business'),
(5, 'Business'),
(6, 'Business'),
(7, 'Compliance'),
(8, 'Compliance'),
(9,'Business'),
(10,'Marketing');

select d.ID,
       d.[Col C],
       case 
            when d.[Col C] <> coalesce(lag(d.[Col C]) over(order by d.ID), '')     then 'Ok'
            when d.[Col C] <> coalesce(lead(d.[Col C]) over(order by d.ID), '')    then 'Ok'
       end as B
from data d
order by d.ID;

Answer 1

Try this:

SELECT *
      ,CASE 
            WHEN [ID] % 2 = 1 AND [Col C] = 'Business' AND LEAD([Col C]) OVER(ORDER BY [ID]) = 'Compliance' THEN 'OK'
            WHEN [ID] % 2 = 1 AND [Col C] = 'Compliance' AND LEAD([Col C]) OVER(ORDER BY [ID]) = 'Business' THEN 'OK'
            WHEN [ID] % 2 = 0 AND [Col C] = 'Business' AND LAG([Col C]) OVER(ORDER BY [ID])= 'Compliance' THEN 'OK'
            WHEN [ID] % 2 = 0 AND [Col C] = 'Compliance' AND LAG([Col C]) OVER(ORDER BY [ID])= 'Business' THEN 'OK'
            ELSE 'Fail'
       END AS [Result]
FROM [dbo].[data];

I am using [ID] % 2 to check if I am interested in the next value (for 1,2,5,etc) or the previous (for 2,4,6,etc).