깡민쓰~IT Story

use rtc

declare @sipQuery nvarchar(250)
set @sipQuery = 'sipusername%'

select Publisher, MIN(Status) as Status
from
(select Publisher, Status=
CASE
when Availability BETWEEN 0 AND 2999 then 'Not defined:'+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
from
(
select Publisher,
substring(
substring(PublicationDocument,patIndex('%<availability>%',PublicationDocument)+14,50),0,
patIndex('%<availability>%',substring(PublicationDocument,patIndex('%<availability>%',PublicationDocument)+14,50))
) Availability
from
(select UserAtHost Publisher,ContainerNum,CONVERT(varchar(4000),convert(varbinary(4000),Data)) PublicationDocument
from rtcdyn.dbo.PublishedInstance tblPublishedInstance,
rtc.dbo.Resource tblResource
where tblPublishedInstance.PublisherId = tblResource.ResourceId) as PublishedDocuments
where LEN(replace(PublicationDocument,'aggregateState','')) < LEN(PublicationDocument)
and ContainerNum = 2
) as PublisherAndAvailability
where Publisher like @sipQuery
union
select UserAtHost Publisher, 'Offline-Not Registered Here' Status
from rtc.dbo.Resource
where UserAtHost like @sipQuery) as PublisherAndStatus
group by Publisher

참고

http://mikestacy.typepad.com/mike-stacys-blog/sql/

Container 테이블 설명

https://msdn.microsoft.com/en-us/library/bb879521.aspx

https://msdn.microsoft.com/en-us/library/office/dn454664.aspx

Access Control List Containers