Database

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

3 weeks ago

3 weeks ago

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?

Date | Emp-ID | Dept |

7/2/2019 | 1234 | A |

7/1/2019 | 1234 | B |

7/10/2019 | 5678 | C |

7/18/2019 | 7890 | B |

7/19/2019 | 7890 | C |

Expected Output:

Date | Emp-ID | Dept |

7/1/2019 | 1234 | A |

7/8/2019 | 5678 | C |

7/15/2019 | 7890 | C |

3 REPLIES 3

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

3 weeks ago

3 weeks ago

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

3 weeks ago

3 weeks ago

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?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

3 weeks ago

3 weeks ago

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