Using If statement with a Row_Number then +1

Question

I have tried creating the code below but it didn't work for me.

what I'm trying to do is use Row_number however check if the column MP =' Yes' then +1 to the Row_number value if it does otherwise just give me the Row_number.

  SELECT
 [Key]
,[Key2]
,MP
,OriginalOrder = ROW_NUMBER() OVER(Partition by [Key] ORDER BY  [Key],[Key2])
,IIF(MP = 'Yes' , ROW_NUMBER() OVER(Partition by [Key] ORDER BY  [Key],[Key2])+1,ROW_NUMBER() OVER(Partition by [Key] ORDER BY  [Key],[Key2]))what_i_have_tried
FROM
cte_RowNumbertest
ORDER BY
1,2

Answer 1

If I understand correctly, you can try to use SUM window function with CASE WHEN, if MP is yes add one more.

SELECT
     [Key]
    ,[Key2]
    ,MP
    ,ROW_NUMBER() OVER(Partition by [Key] ORDER BY  [Key],[Key2]) OriginalOrder 
    ,SUM(CASE WHEN MP = 'Yes' THEN 2 ELSE 1 END) OVER(Partition by [Key] ORDER BY  [Key],[Key2]) what_i_have_tried
FROM
cte_RowNumbertest
ORDER BY
1,2

Answer 2

I think what you actually want is a ROW_NUMBER and a conditional running COUNT:

 SELECT [Key]
        [Key2],
        MP,
        ROW_NUMBER() OVER (Partition by [Key] ORDER BY [Key],[Key2]) AS OriginalOrder,
        ROW_NUMBER() OVER (Partition by [Key] ORDER BY [Key],[Key2]) +
        COUNT(CASE MP WHEN 'Yes' THEN 1 END) OVER (Partition by [Key] ORDER BY [Key],[Key2]) AS ExpectedOutput
FROM dbo.YourTable
ORDER BY [Key]
         [Key2];