Lync会议组织者举行的会议次数以及最小、平均、最大参与人数

use LcsCDR;
 
with confsAndUsers as (
    select 
        ConferenceStartTime, ConferenceEndTime, o.UserUri as orgUserUri, u.UserUri
    from Conferences c
        join Users o on c.OrganizerId = o.UserId
        join McuJoinsAndLeaves jl on jl.SessionIdTime = c.SessionIdTime and jl.SessionIdSeq = c.SessionIdSeq
        join Users u on jl.UserId = u.UserId
        join Mcus m on jl.McuId = m.McuId
        join UriTypes ut on m.McuTypeId = ut.UriTypeId
        left join FocusJoinsAndLeaves fjl on 
            fjl.SessionIdTime = c.SessionIdTime and fjl.SessionIdSeq = c.SessionIdSeq and fjl.UserId = jl.UserId
        left join ClientVersions cv on 
            fjl.ClientVerId = cv.VersionId
    where 
        (ut.UriType = 'conf:audio-video' or ut.UriType = 'conf:applicationsharing' or ut.UriType = 'conf:data-conf')
        and cv.ClientType != 256 and cv.ClientType != 16396
    group by ConferenceStartTime,ConferenceEndTime,o.UserUri,u.UserUri
),
confsAndAttendeeCounts as (
    select 
        ConferenceStartTime,
        CONVERT(varchar,(ConferenceEndTime - ConferenceStartTime),108) as Duration,
        orgUserUri as OrganizerUri,
        COUNT(*) as AttendeeCount
    from confsAndUsers
    group by ConferenceStartTime,ConferenceEndTime,orgUserUri having COUNT(*) > 1
)
 
select 
    COUNT(*) as HostingCount,
    MIN(AttendeeCount) as MinAttendeeCount,
    AVG(AttendeeCount) as AvgAttendeeCount,
    MAX(AttendeeCount) as MaxAttendeeCount,
    OrganizerUri as UserUri
from confsAndAttendeeCounts 
group by OrganizerUri