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