declare @sDate VARCHAR(10) DECLARE @eDate VARCHAR(10) SET @sdate = ‘2014-06-25‘ SET @edate = ‘2014-06-26‘ --生成日期表 CREATE TABLE #DateTable([date] VARCHAR(10)) WHILE (@sdate <=@edate) BEGIN INSERT #DateTable([date]) VALUES(@sdate) --select CONVERT(VARCHAR(10), DATEADD(day, 1, @sdate), 120) SET @sdate =CONVERT(VARCHAR(10), DATEADD(day, 1, @sdate), 120) END --SELECT * FROM #DateTable --查出所有的聊天实例 --SELECT * INOT #ChatOnline FROM ChatOnline WHERE --查出所有的聊天记录 SELECT b.ID AS LogID, a.ID AS ChatOnlineID, a.cvMainID, a.CaMainID, a.Initiative, a.StartDate, a.EndDate, CONVERT(VARCHAR(10), b.AddDate, 120) AS AddDate, b.SenderType, b.IsViewed INTO #LogTable FROM ChatOnline a, ChatOnlineLog b WHERE a.ID=b.ChatOnlineID AND ManagerUserID=0 --AND b.AddDate BETWEEN @sdate AND @edate --SELECT * FROM #LogTable --生成发起聊天数量表 SELECT DISTINCT ChatOnlineID, cvMainID, caMainID, Initiative, AddDate INTO #ChatOnline FROM #LogTable --SELECT * FROM #ChatOnline --查询结果,赋值为0的,取出后在C#内计算 SELECT b.[date],--日期 InitiativeCount =(SELECT COUNT(ChatOnlineID) FROM #ChatOnline WHERE AddDate=b.[date]), --会话次数 SendCount = (SELECT COUNT(LogID) FROM #LogTable WHERE Initiative=SenderType AND AddDate=b.[date]), --发送条数 ReceiveCount = (SELECT COUNT(LogID) FROM #LogTable WHERE Initiative!=SenderType AND AddDate=b.[date]), --发送条数 PaUsageCount =(SELECT COUNT(ID) FROM ChatOnline WHERE CvMainID!=0 AND AddDate=b.[date]),--求职者主动使用次数 PaLogInCount = (SELECT COUNT(ID) FROM PaLoginLog WHERE AddDate=b.[date]),--求职者总登录次数 PaUsagePercent=0,--求职者主动使用比例 CaUsageCount = (SELECT COUNT(ID) FROM ChatOnline WHERE caMainID!=0 AND AddDate=b.[date]),--企业主动使用次数 CaLogInCount=(SELECT COUNT(ID) FROM CaLoginLog WHERE AddDate=b.[date]),--企业总登录次数 CaUsagePercent=0,--企业主动使用比例 AvgUsageCountWhenLog=0,--使用次数除以登录次数 AvgSendCountPerChat = 0,--对话平均发送条数 UnReadChatCount,--留言次数(发送,对方没回复) UnReadCount = (SELECT COUNT(LogID) FROM #LogTable WHERE IsViewed=0 AND AddDate=b.[date]) --留言条数 FROM #LogTable a RIGHT JOIN #DateTable b ON a.AddDate=b.[date] WHERE 1=1 GROUP BY b.[date] DROP TABLE #DateTable DROP TABLE #LogTable DROP TABLE #ChatOnline