参会次数统计报表

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 = 38
) As AA
) as BB
Group By BB.UserID,BB.UserName,MeetingID

转载于:https://www.cnblogs.com/laopo/p/4368962.html