By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE sample_table (
Person varchar(1),
month varchar(5),
color varchar(5),
);
INSERT INTO sample_table (Person, month, color)
VALUES
('A','Dec','Blue'),
('A','Nov','Blue'),
('A','Oct','Red'),
('A','Sept','Red'),
('B','Dec','Red'),
('B','Nov','Blue'),
('B','Jan','Red')
7 rows affected
SELECT * FROM sample_table
| Person | month | color |
|---|---|---|
| A | Dec | Blue |
| A | Nov | Blue |
| A | Oct | Red |
| A | Sept | Red |
| B | Dec | Red |
| B | Nov | Blue |
| B | Jan | Red |
SELECT * FROM sample_table ORDER BY PERSON ASC, MONTH ASC
| Person | month | color |
|---|---|---|
| A | Dec | Blue |
| A | Nov | Blue |
| A | Oct | Red |
| A | Sept | Red |
| B | Dec | Red |
| B | Jan | Red |
| B | Nov | Blue |
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
| Person | month | color | Change |
|---|---|---|---|
| A | Dec | Blue | 0 |
| A | Nov | Blue | 1 |
| A | Oct | Red | 0 |
| A | Sept | Red | 0 |
| B | Dec | Red | 0 |
| B | Nov | Blue | 1 |
| B | Jan | Red | 0 |