I'm not sure what sequence this query should be executed in; could you help explain? When the join is applied, the function is executed, a new column with the Case is created, and the serial number is added, I'm perplexed. Please clarify the sequence in which all of this will be carried out.
select Row_number() OVER(ORDER BY (SELECT 1)) AS 'Serial No',
E.FullName,[dbo].[GetBookingRole](EC.CustomerId,E.BookingRole) as Role,
(select top 1 convert(varchar(10),eventDay,108)from [3rdi_EventDays] where EventNo=16) as EventDay,
WHEN '90 Day Client' THEN 'CC'
WHEN 'Association Client' THEN 'CC'
WHEN 'Autism Whisperer' THEN 'CC'
WHEN 'CampII' THEN 'AA'
WHEN 'Captain' THEN 'AA'
WHEN 'Chiropractic Assistant' THEN 'AA'
WHEN 'Coaches' THEN 'AA'
END) as Category from [3rdi_EventParticipants] as E
inner join [3rdi_EventSignup] as EC on E.SignUpId = EC.SignUpId
where E.EventId = 16
and userid in (
select distinct customerid from customerroles
--where roleid not in(6,6,60,68) and roleid not in(1,2))
where roleid not in(20, 21, 22, 23) and roleid not in(1,2))