SELECT LOWER(UserAtHost) AS UserAtHost, Status=
	CASE
		WHEN Availability BETWEEN 0 AND 2999 THEN Availability
		WHEN Availability BETWEEN 3000 AND 4499 THEN 'Available'
		WHEN Availability BETWEEN 4500 and 5999 THEN 'Available - Idle'
		WHEN Availability BETWEEN 6000 and 7499 THEN 'Busy'
		WHEN Availability BETWEEN 7500 and 8999 THEN 'Busy - Idle'
		WHEN Availability BETWEEN 9000 and 11999 THEN 'Do not Disturb'
		WHEN Availability BETWEEN 12000 and 14999 THEN 'Be right back'
		WHEN Availability BETWEEN 15000 and 17999 THEN 'Away'
		WHEN Availability >= 18000 THEN 'Offline'
	END,
	LastPubTime
FROM rtc.dbo.Resource Resource
	RIGHT JOIN (
		SELECT
			Instance.PublisherId,
			SUBSTRING(Instance.Data, CHARINDEX('<availability>', Instance.Data) + 14, CHARINDEX('</availability>', Instance.Data) - CHARINDEX('<availability>', Instance.Data) - 14) AS Availability,
			Instance.LastPubTime
		FROM (
				SELECT PublisherId, cast(substring(Data, 0, 256) AS varchar(256)) AS Data, LastPubTime FROM rtcdyn.dbo.PublishedInstance WHERE ContainerNum = 2 AND CategoryId = 4
				UNION ALL
				SELECT PublisherId, cast(substring(Data, 0, 256) AS varchar(256)) AS Data, LastPubTime FROM rtc.dbo.PublishedStaticInstance WHERE ContainerNum = 2 AND CategoryId = 4
			) AS Instance
		WHERE
			CHARINDEX('aggregateState', Data) > 0
) AS UserAndAvailability ON Resource.ResourceId = PublisherId
/*WHERE UserAtHost = 'somebody@example.com' COLLATE SQL_Latin1_General_CP1_CI_AS*/
ORDER BY UserAtHost, LastPubTime DESC