I have a table employee as below. Say emp 1234 is assigned to 2 departments A and B for a given week. In the output i need the week start which is monday of the week. When an employee has dept A and B then A should be selected in the output, when emp has B and C then C should be in the output, when emp has C and A then A should be in the output. How do I write a SQL?
Find the Monday of each week and apply a ROW_NUMBER ordered by a CASE implementing your priority
select * from mytab qualify row_number() over(partition by empid, td_monday(datevol) -- Monday of the current week order by case dept
when 'A' then 1
when 'C' then 2
end) = 1
I do not understand how the row number is generated. can you please explain?
PARTITION BY groups all rows with the same empid and start of week
ORDER BY sorts those rows based on your priority A-C-B