I have a set of data that looks like this

I am attempting to create a fourth column that is a binary 0 1 depending on if the color changed from Red to blue(0 for no 1 for yes) from the previous month. How can I do this using SQL? New table should look something like this.

I have tried some if statements but the problem that I am having is that my code needs to look at the previous months color not just the current one. For example, if November for person A is blue and December for person A is also blue then there is no change from red to blue.

CodePudding user response：

select *
      ,case when lag(color) over(partition by person order by month) <> color and color = 'Blue' then 1 else 0 end as change
from   t

Fiddle

CodePudding user response：

Using a CTE to convert the month string into datetime then LAG to compare the previous result:

WITH cte AS (
  SELECT *,
  Month(cast(substring("month",0,4) '1 2016' as datetime)) AS month_converted
  FROM sample_table
  )
SELECT Person, "month", color,
  CASE WHEN 
  LAG(color) OVER(PARTITION BY person ORDER BY month_converted) <> color 
  and color = 'Blue' 
    THEN 1
    ELSE 0
  END AS Change
FROM cte 
ORDER BY Person ASC, month_converted DESC

Result:

db<>fiddle here.

Also, its worth nothing, the column named month is a Reserved Word in SQL Server. It is advised to change it to something else that is not reserved.