select one value over another

Database
Enthusiast

select one value over another

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?

 

DateEmp-IDDept
7/2/20191234A
7/1/20191234B
7/10/20195678C
7/18/20197890B
7/19/20197890C

Expected Output:

DateEmp-IDDept
7/1/20191234A
7/8/20195678C
7/15/20197890C
3 REPLIES 3
Ambassador

Re: select one value over another

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
else 3
end) = 1
Enthusiast

Re: select one value over another

Thank you! The solution works like a charm. I do not understand how the row number is generated. can you please explain?

Ambassador

Re: select one value over another


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