SQL server create how to create a new column based on previous row condition

Question

I'm trying to create a new column, whose values are dependent to the previous index row values, like in the example below:

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

Here's how I'm creating the Tag column :

CASE WHEN AGE > 25 AND HireMonth => 9 
     THEN (next row value = 1 AND same row ID ) 
     ELSE ID
END AS Tag

Answer 1

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

Answer 2

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.