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
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.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments