I'm Trying to creating a new column that values are dependent to the previous index row values like below example : I'm creating Tag column :
CASE WHEN AGE > 25 AND HireMonth => 9 THEN (next row value = 1 AND same row ID ) ELSE ID
END AS Tag
| Index | ID | AGE | SEX | HireMonth | Tag |
|---|---|---|---|---|---|
| 1 | 101 | 23 | M | 9 | 101 |
| 2 | 102 | 32 | M | 12 | 102 |
| 3 | 103 | 25 | F | 11 | 1 |
| 4 | 104 | 29 | M | 10 | 104 |
| 5 | 105 | 45 | F | 1 | 1 |
| 6 | 106 | 21 | M | 7 | 106 |
| 7 | 107 | 56 | F | 6 | 107 |
| 8 | 108 | 12 | M | 4 | 108 |
CodePudding user response：
Have you tried:
select [Index], ID, AGE, SEX, HireMonth,
CASE WHEN LAG(AGE, 1) OVER (ORDER BY [Index]) > 25 AND LAG(HireMonth, 1) OVER (ORDER BY [Index]) >= 9
THEN 1
ELSE ID
END AS Tag
FROM SOME_TABLE
CodePudding user response：
You can use a use a SELF LEFT JOIN to match each ID with its previous one. If the requirements you pointed match, then you can assign 1 otherwise you assign the ID of the same row, using a CASE statement.
SELECT
t1.*,
CASE WHEN t2.ID_ IS NOT NULL THEN 1 ELSE t1.ID_ END AS Tag
FROM tab t1
LEFT JOIN tab t2
ON t1.ID_ = t2.ID_ 1
AND t2.AGE > 25
AND t2.HireMonth > 8
Try it here.