SQL - How to check the status of salary incremented or decremented?-CodePudding

I want to check if the salary has been increased or decreased. How can I do it? A new column can be opened and written as ascending and descending.

I am using MSSQL.

Table View; enter image description here

CodePudding user response：

Use a CTE (or a sub-query) with a LAG() to get each row's preceding wage value.

Then use a CASE expression to categorise the change.

WITH
  data_lagged AS
(
  SELECT
    *,
    LAG(wage) OVER (PARTITION BY CurrAccCode ORDER BY StartDate)  AS wage_lag
  FROM
   yourTable
)
SELECT
  *,
  CASE WHEN wage > wage_lag THEN 'increase'
       WHEN wage = wage_lag THEN 'no_change'
       WHEN wage < wage_lag THEN 'decrease'
                            ELSE 'new_wage' END  AS wage_change_mode
FROM
  data_lagged