MitchRudi MitchRudi - 5 months ago 18
SQL Question

If Rownumber = 1 and Condition Then Condition

I've got a question about a sql result and how to achieve the following.

Screenshot of Resultset

In the Screenshot, there is a Rownumber for every ID, and every ID has another column which has a status 'old' or 'processed'. What i want is, if the RN is = 1 and the Status is processed, than all other RN of this ID should also have the status 'processed'.

Is there a possibility to achieve this in sql?

SELECT RN = ROW_NUMBER() OVER (PARTITION BY [NODE_NAME]
ORDER by REPORTING_RELEVANT_STATUS_ID DESC, BILLING_PERIOD DESC)
,[CI_EQUIPMENT_ID] AS ID_PART
,[REPORTING_RELEVANT_STATUS_ID] AS REPORTING_RELEVANT
,[BILLING_PERIOD]
, [NODE_NAME]
FROM Table

Answer

Put your query in CTE? then JOIN it with actual table:

;WITH cte AS (
SELECT RN = ROW_NUMBER() OVER (PARTITION BY [NODE_NAME]
ORDER by REPORTING_RELEVANT_STATUS_ID DESC, BILLING_PERIOD DESC)
      ,[CI_EQUIPMENT_ID] AS ID_PART
      ,[REPORTING_RELEVANT_STATUS_ID] AS REPORTING_RELEVANT
      ,[BILLING_PERIOD]
     , [NODE_NAME]
        FROM Table
)

SELECT  t.[CI_EQUIPMENT_ID] AS ID_PART,
        CASE WHEN c.RN is NOT NULL THEN c.REPORTING_RELEVANT ELSE t.[REPORTING_RELEVANT_STATUS_ID] END AS REPORTING_RELEVANT,
        t.[BILLING_PERIOD],
        t.[NODE_NAME]
FROM Table t
LEFT JOIN (
    SELECT *
    FROM cte
    WHERE RN = 1 AND REPORTING_RELEVANT = 'PROCESSED'
) as c
    ON c.ID_PART = t.[CI_EQUIPMENT_ID]