统计SQL

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