Hi,
I have a column that represents each employee's weekly work schedule. i.e : 8-8-8-8-8, 8-8-6-6-0
I use the following script to split the values:
Select
dbo.Zohouser.ZohoUserId as UserId,
CAST(N'<x>' + REPLACE(WorkHoursSchedule, '-', N'</x><x>') + N'</x>' AS XML).value('/x[1]', 'nvarchar(max)') [Mon],
CAST(N'<x>' + REPLACE(WorkHoursSchedule, '-', N'</x><x>') + N'</x>' AS XML).value('/x[2]', 'nvarchar(max)') [Tue],
CAST(N'<x>' + REPLACE(WorkHoursSchedule, '-', N'</x><x>') + N'</x>' AS XML).value('/x[3]', 'nvarchar(max)') [Wed],
CAST(N'<x>' + REPLACE(WorkHoursSchedule, '-', N'</x><x>') + N'</x>' AS XML).value('/x[4]', 'nvarchar(max)') [Thu],
CAST(N'<x>' + REPLACE(WorkHoursSchedule, '-', N'</x><x>') + N'</x>' AS XML).value('/x[5]', 'nvarchar(max)') [Fri]
from dbo.Zohouser
where dbo.Zohouser.ZohoUserId in (1111)
Result:
I have the following query to retrieve all days taken in July 2023 for a specific employee
select
ZohoUserId as UserId,
day(LeaveDate) as LeaveDay,
Case DATEPART(WEEKDAY, LeaveDate)-1
When '1' then 'Mon'
When '2' then 'Tue'
When '3' then 'Wed'
When '4' then 'Thu'
When '5' then 'Wed'
End as DayoftheWeek
from LeaveDay
where LeaveYear = 2023
and LeaveDay.ApprovalStatus in ('Approved','Pending')
and LeaveType <> 'Holiday'
and ZohoUserId in (1111)
and month(LeaveDate) = 07
order by 2
Result:
I need for each day of vacation to retrieve the right schedule.
How can I get that:
- on Mon 3rd, the work schedule was 8?
- on Tue 4th, the work schedule was 8?
- on Wed 5th, the work schedule was 6?
- on Thu 6th, the work schedule was 6?
……
