统计
/****** Object: Procedure [dbo].[JOB_UP_Summit_UserConfStat] Script Date: 2014-3-17 10:00:50 ******/ USE [ytSummitTeleConf_DB]; GO SET ANSI_NULLS ON; GO SET QUOTED_IDENTIFIER ON; GO /*============================================================= =============================================================*/ CREATE PROC [dbo].[JOB_UP_Summit_UserConfStat] @BeginTime DATETIME = NULL, @EndTime DATETIME = NULL AS SET NOCOUNT ON DECLARE @RangeBeginTime DATETIME, @RangeEndTime DATETIME IF(@BeginTime IS NOT NULL AND @EndTime IS NOT NULL) BEGIN SELECT @RangeBeginTime = @BeginTime, @RangeEndTime = @EndTime END ELSE IF(@BeginTime IS NULL AND @EndTime IS NULL) BEGIN SELECT @RangeBeginTime = CONVERT(CHAR(10), DATEADD(DAY, -1, GETDATE()), 120), @RangeEndTime = CONVERT(CHAR(10), GETDATE(), 120) END ELSE BEGIN RAISERROR('日期范围错误!', 1, 16) RETURN END --通话消费 INSERT INTO WTC_TB_USERCONF_DetailStat ( CONFROOM, SeqNo, startTime, CallerNum, CalledNum, HoldTime, Banlnce, CallFlag, MebID, CreateTime, ConfGUID, BalanceType, Data ) SELECT B.ConfROOM, C.SeqNO, C.startTime, C.CallerNum, C.CalledNum, C.HoldTime, C.ConsumeAmount AS Banlnce, C.SubSerFlag AS CallFlag, C.BatchCode, GETDATE() AS CreateTime, NEWID() AS ConfGUID, C.BalanceType, B.Data FROM dbo.WTC_TB_USERCONF A WITH(NOLOCK) INNER JOIN dbo.WTC_TB_CONFMEMBERS B WITH(NOLOCK) ON A.CONFROOM = B.CONFROOM INNER JOIN IB_UserConsumeList C WITH(NOLOCK) ON A.SeqNo = C.SeqNo --AND B.BatchID = C.BatchID AND CAST(B.MebID AS VARCHAR(36)) = C.BatchCode AND C.Serflag = 4 WHERE A.Flag = 1 --召开成功的会议 AND A.CONFTIME >= @RangeBeginTime AND A.CONFTIME < @RangeEndTime --短信消费 INSERT INTO dbo.WTC_TB_USERSMS_DetailStat ( CONFROOM, SeqNo, MsgID, Mobile, SendTime, [State], SmsID, MessageContent, SmsType, Amount ) SELECT A.CONFROOM, A.SeqNo, B.MsgID, C.Mobile, B.SendTime, B.[State], C.SmsID, MessageContent = [Message], B.SmsType, B.Amount FROM dbo.WTC_TB_USERCONF A WITH(NOLOCK) INNER JOIN dbo.IB_Sms_Send_Bill B WITH(NOLOCK) ON A.CONFROOM = B.CONFROOM INNER JOIN dbo.IB_Sms_Send_Detail_Bill C WITH(NOLOCK) ON B.MsgID = C.MsgID WHERE A.CONFTIME >= @RangeBeginTime --所有的会议都有可能收取短信费用 AND A.CONFTIME < @RangeEndTime --会议统计 INSERT INTO dbo.WTC_TB_USERCONF_Stat ( CONFROOM, SeqNo, CONFTITLE, CONFTIME, confHoldTimeSum, confBancleSum, confBancleSumMoth, CreateTime, BillCallNum, ConfCallNum, SmsBancleSum ) SELECT A.CONFROOM, A.SeqNo, A.CONFTITLE, A.CONFTIME, ConfHoldTimeSum = ISNULL(B.ConfHoldTimeSum, 0), ConfBancleSum = ISNULL(B.ConfBancleSum, 0), ConfBancleSumMoth = ISNULL(B.ConfBancleSumMoth, 0), GETDATE(), BillCallNum = ISNULL(B.BillCallNum, 0), --计费通话数 ConfCallNum = ISNULL(C.ConfCallNum, 0), --会议通话数 SmsBancleSum = ISNULL(D.SmsBancleSum, 0) FROM dbo.WTC_TB_USERCONF A WITH(NOLOCK) OUTER APPLY ( SELECT ConfHoldTimeSum = SUM(CASE HoldTime%60 WHEN 0 THEN HoldTime ELSE (HoldTime/60+1)*60 END), ConfBancleSum = SUM(Banlnce), ConfBancleSumMoth = SUM(CASE WHEN BalanceType <> 3 THEN Banlnce END), BillCallNum = COUNT(1) FROM dbo.WTC_TB_USERCONF_DetailStat M WITH(NOLOCK) WHERE M.CONFROOM = A.CONFROOM ) B OUTER APPLY ( SELECT ConfCallNum = COUNT(1) FROM dbo.WTC_TB_CONFMEMBERS M WITH(NOLOCK) WHERE M.CONFROOM = A.CONFROOM ) C OUTER APPLY ( SELECT SmsBancleSum = SUM(Amount) FROM dbo.WTC_TB_USERSMS_DetailStat M WITH(NOLOCK) WHERE M.CONFROOM = A.CONFROOM ) D WHERE A.CONFTIME >= @RangeBeginTime AND A.CONFTIME < @RangeEndTime GO