How to modify the first work date based on different group

Question

I have a table with employees from different group. Now I need to calculate the work date based on different group.

For example, employee from group 1 works from Monday to Friday every week. While employee from group 2 works from Sunday to Thursday.

Here is the demo table:

create table demo (employeeid int,groupid int,work_date datetime)
insert into demo values
(10011,1,'2023-10-30'),(10011,1,'2023-10-31'),(10022,2,'2023-10-30'),(10022,2,'2023-10-31')

The result should be like this:

Thanks in advance!

Answer 1

Hi @Lora

Try this:

-- Default Set Sunday is first day of week
set datefirst 7; 
select *,case when groupid = 1 then (datepart(weekday,work_date) + 5) % 7 + 1 
              when groupid = 2 then datepart(weekday,work_date)
			  end as day_of_week
from demo

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.