select r.Name, dsc.Code, dsc.FriendlyName, u.LastName+', '+u.FirstName, u.EmailAddress from Users u
inner join Roles r on r.Name like '%VAMC Tech%' and r.Id = u.RoleId
inner join UserAttributes ua on ua.UserId = u.Id and ua.MetaDataId=2063
inner join MetaDataValue mdv on mdv.MetaDataId = 2063 and ua.MetaDataValueId=mdv.Id
left join dim_SiteCode dsc on mdv.DisplayText = dsc.Code 
order by r.Name, dsc.FriendlyName


select r.Name, dsc.Code, dsc.FriendlyName, u.LastName+', '+u.FirstName, u.EmailAddress from Users u
inner join Roles r on r.Name like '%AO%' and r.Id = u.RoleId
inner join UserAttributes ua on ua.UserId = u.Id and ua.MetaDataId=2063
inner join MetaDataValue mdv on mdv.MetaDataId = 2063 and ua.MetaDataValueId=mdv.Id
left join dim_SiteCode dsc on mdv.DisplayText = dsc.Code
order by r.Name, dsc.FriendlyName

select r.Name, dsc.FriendlyName, count(u.Id) as ucount from Users u
inner join Roles r on (r.Name like '%AO%' or r.Name like '%VAMC Tech%') and r.Id = u.RoleId
inner join UserAttributes ua on ua.UserId = u.Id and ua.MetaDataId=2063
inner join MetaDataValue mdv on mdv.MetaDataId = 2063 and ua.MetaDataValueId=mdv.Id
left join dim_SiteCode dsc on mdv.DisplayText = dsc.Code
group by r.Name, dsc.FriendlyName order by ucount desc