TSQL-Compare values of two columns and add 0 or 1 to another column of the same table-CodePudding

I have a table with 3 Columns (Userid, Hits, In&Out).

Userid // Hits // In&Out

12 // 2022-05-2510:12:53.000

12 // 2022-05-2514:17:43.000

17 // 2022-05-2509:11:28.000

17 // 2022-05-2517:07:10.000

18 // 2022-05-2508:54:31.000

18 // 2022-05-2517:01:31.000

I want the Column In&Out to get 0 or 1 after a comparing of the value of Hit column (date&time) according to each UserID and the earlier datetime will be 0 and the latest will be 1

e.g

Userid // Hits // In&Out

12 // 2022-05-25 10:12:53.000 // 0

12 // 2022-05-25 14:17:43.000 // 1

17 // 2022-05-25 09:11:28.000 // 0

17 // 2022-05-25 17:07:10.000 // 1

18 // 2022-05-25 08:54:31.000 // 0

18 // 2022-05-25 17:01:31.000 // 1

CodePudding user response：

A simple lead test will give the result you asked for

SELECT *,
        CASE WHEN LEAD(HITS) OVER (PARTITION BY ID ORDER BY HITS) IS NULL THEN 1 ELSE 0 END 'IN&OUT'  
FROM T

Null in this case means that there is no later entry for this id.