I thought that there would be a context switch each time present changed for the Id since I was looking the contiguous range of dates with present being 1 and resetting the row_number() each time a sub-context changes Present from 1 or 0 and vice versa. As well as the Id, changes to a new Id.
Why isn’t the correct answer for 2 2024-03-13 1 1 and not 2 2024-03-13 1 2 —wrong should be one?
id date present RowNumberPresent
2 2024-03-11 1 1 — Correct
2 2024-03-12 0 1 — Correct
2 2024-03-13 1 2 —wrong should be one?
;with RankEmpolyeeAttendance
as (select id,
date,present,
row_number() over (partition by id, present order by date) as RowNumberPresent
from dbo.attendance
)
SELECT r.id,
r.date,
r.present,
r.RowNumberPresent FROM RankEmpolyeeAttendance as r
order by r.id,
r.date
id date present RowNumberPresent
1 2024-03-12 1 1
1 2024-03-13 1 2
1 2024-03-14 1 3
1 2024-03-15 0 1
2 2024-03-11 1 1
2 2024-03-12 0 1
2 2024-03-13 1 2
3 2024-03-14 1 1
3 2024-03-15 1 2
No file chosen
Why isn’t the correct answer for 2 2024-03-13 1 1 and not 2 2024-03-13 1 2 ?
PeterH is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.