SQL - only increment ROW_NUMBER() on specific values-CodePudding

I have the below table and am trying to add a column using row number (partitioning by the product and ordering by the contract start date) which will only increment when the contract_status_id is not 4. If the contract_status_id is 4, it should show as -1

So the column for the below would show as

1,
2,
-1,
3,

What would be the best way of achieving this? My existing code looks like the below.. but this is as far as my row number knowledge goes!

ROW_NUMBER() OVER (PARTITION BY a.product ORDER BY a.contract_start_date ASC)

CodePudding user response：

There are a few ways you could do this. You could use ROW_NUMBER to start with, but you'd need to partition on the contract status with an IIF:

CASE Contract_Status_ID WHEN 4 THEN -1
     ELSE ROW_NUMBER() OVER (PARTITION BY Product, IIF(Contract_Status_ID  = 4,1,0) ORDER BY Contract_Start_Date)
END

You could, alternatively, use a conditional COUNT:

CASE Contract_Status_ID WHEN 4 THEN -1
     ELSE COUNT(CASE WHEN Contract_Status_ID != 4 THEN 1 END) OVER (PARTITION BY Product  ORDER BY Contract_Start_Date)
END

CodePudding user response：

First you need a column to indicate the status (table x). Then you get the row number for the ones you care about via partition (table z). Finally you fix the values to match what you need.

SELECT -- all the stuff
  CASE WHEN z.c_status = 1 THEN val ELSE -1 END as final_result
  FROM (
    SELECT -- all the stuff
      Row_number() OVER ( partition BY x.product,x.c_status ORDER BY x.contract_start_date ASC ) as val
    FROM (
      SELECT -- all the stuff,
        CASE WHEN contract_status_id=1 THEN 1 ELSE 0 END as c_status
      FROM tableyoudidnotname
    ) x
  ) z

CodePudding user response：

Larnu's answer is definitely the "elegant" way to go, but if someone deals with a similar issue and works with a SQL Server version prior to 2012 (when the support of the ORDER BY clause was added for the aggregation functions), a UNION also does the trick:

SELECT Product, Contract_ID, Contract_Status_ID, Contract_Start_Date,
       ROW_NUMBER() OVER (PARTITION BY Product ORDER BY Contract_Start_Date) 
Contract_Seq_Number
FROM table
WHERE Contract_Status_ID != 4
UNION
SELECT Product, Contract_ID, Contract_Status_ID, Contract_Start_Date,
       -1 Contract_Seq_Number
FROM table
WHERE Contract_Status_ID = 4
ORDER BY Product, Contract_Start_Date, Contract_Seq_Number