HBSZRX/文档/sql/P_CallInRepo...

USE [HBSZRX_20190801]
GO
/****** 
Object:  StoredProcedure [dbo].[P_CallInReport]    Script Date: 08/29/2019 15:09:44 
2019-08-29 lihai T_Call_LeaveRecord时间条件修改成F_StartTime（以前是F_DealTime）
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[P_CallInReport] 
@sdate varchar(20),
@edate varchar(20)
AS
BEGIN

  create table #tmpCallInReport(时间 VARCHAR(10),电话总呼入量 int,正常接听量 int,语音留言量 int,主动放弃量 int,电话骚扰量 int)
  --create table #tmpCallInReport(date VARCHAR(10),totalcount int,answercount int,leavecount int,giveupcount int,harasscount int)

	declare @tempdate varchar(10)
  declare @temptotalcount int
  declare @tempanswercount int
  declare @templeavecount int
  declare @tempgiveupcount int
  declare @tempharasscount int
  declare @tempttotalcount int=0
  declare @temptanswercount int=0
  declare @temptleavecount int=0
  declare @temptgiveupcount int=0
  declare @temptharasscount int=0
  declare @countsum int
  declare @count int

  --办理临时表
  select * INTO #allcallintable FROM T_Call_CallRecords where CallType=0 
  and datediff(day,BeginTime,@sdate)<=0 and datediff(day,BeginTime,@edate)>=0 

  set @countsum=DATEDIFF(DAY,@sdate,@edate)+1
  select @tempdate=@sdate,@count=0
  while @count<@countsum
    begin
      select * into #daycallintable from #allcallintable where datediff(day,BeginTime,@tempdate)=0
      select @temptotalcount=count(1) from #daycallintable
      set @tempttotalcount=@tempttotalcount+@temptotalcount
      select @tempanswercount=count(1) from #daycallintable where isnull(UserCode,'') !='' and DealType != '1'
      set @temptanswercount=@temptanswercount+@tempanswercount
      select @templeavecount=count(1) from T_Call_LeaveRecord where datediff(day,F_StartTime,@tempdate)=0 
      set @temptleavecount=@temptleavecount+@templeavecount
      select @tempgiveupcount=count(1)-@templeavecount from #daycallintable where isnull(UserCode,'') ='' and DealType = '0'
      set @temptgiveupcount=@temptgiveupcount+@tempgiveupcount
      select @tempharasscount=count(1) from #daycallintable where DealType = '1'
      set @temptharasscount=@temptharasscount+@tempharasscount

      insert into #tmpCallInReport VALUES(@tempdate,@temptotalcount,@tempanswercount,@templeavecount,@tempgiveupcount,@tempharasscount)
      
      set @tempdate=CONVERT(VARCHAR(10), dateadd(DAY,1,@tempdate),120)
      set @count=@count+1
      DROP TABLE #daycallintable
    end
  insert into #tmpCallInReport VALUES(N'合计',@tempttotalcount,@temptanswercount,@temptleavecount,@temptgiveupcount,@temptharasscount)
  select * from #tmpCallInReport
  DROP TABLE #tmpCallInReport
  DROP TABLE #allcallintable
END