参会人员统计报表

=====================新表=============================

select
AA.UserID AS UserID ,
AA.UserName AS UserName,
SUM( 1) AS SumNeedJoinMeetingCount ,
SUM( CASE WHEN AA.JoinBeginDate IS NULL THEN 0 ELSE 1 END) AS SumReallyJoinMeetingCount
from
(select
U.UserID AS UserID,
U.UserName AS UserName,
M.BeginDate AS MeetingBeginDate,
J.BeginDate AS JoinBeginDate
from
T_Meeting AS M ,
T_JoinMeetingPerson AS J,
T_UserBaseInfo AS U
WHERE
M.MeetingID = J.MeetingID and
U.UserID = J.UserID and
M.MeetingID =37) as AA
group by
AA.UserID,
AA.UserName

====================旧表============================

Select
BB.UserID,
BB.UserName,
Sum( NeedJoinMeetingCount ) As SumNeedJoinMeetingCount,
Sum( ReallyJoinMeetingCount ) As SumReallyJoinMeetingCount
From
( Select
BeginDate,
MeetingID,
AA.UserID,
AA.UserName,
1 As NeedJoinMeetingCount,
Case When AA.AttDateTime IS Null Then 0 Else 1 End As
ReallyJoinMeetingCount
From
( Select
A.MeetingID MeetingID,
D.UserName As UserName,
A.MeetingCaption,
A.BeginDate As BeginDate,
C.UserID,
( Select Top 1 AttDateTime From T_AttDataInfo Where CardNo = D.LogonID And AttDateTime >= DateAdd(Minute,-20,A.BeginDate) And AttDateTime <= DateAdd(Minute,20,A.BeginDate) ) As AttDateTime
From T_Meeting A, T_MeetingAddress B, T_JoinMeetingPerson C, T_UserBaseInfo D Where A.MeetingAddressID = B.MeetingAddressID And A.MeetingID = C.MeetingID And C.UserID = D.UserID and A.MeetingID = 37
) As AA
) as BB
Group By BB.UserID,BB.UserName,MeetingID