巧用链接服务器同步数据 - Office中国论坛/Access中国论坛

CREATE PROCEDURE [dbo].[A9_Sametime](@startDate date = null) as
if @startDate is null
set @startDate=GETDATE()
declare
--长日期
@startDateTime datetime,
@firstDayDateTime datetime,
@endDateTime datetime,
@sql1 varchar(8000),
@sql2 varchar(1000),
@sql3 varchar(1000)
set @startDateTime=DATEADD(day,-1,@startDate)
set @firstDayDateTime=DATEADD(day,1-day(@startDate),@startDate)
set @endDateTime=DATEADD(MILLISECOND,-3,convert(datetime,@startdate))
--追加前一天数据
set @sql1 = 'select * into ##temp from openquery(A9Server, ''select CustomerID 客服ID,SalesStaff 工号,max(StartTime) 最近接通日期,'
+' CONVERT(VARCHAR(8),DATEADD(ss,sum(BillableSeconds),''''1900-01-01 00:00:00''''),108) as 通话时长,'
+' count(SalesStaff) 通话次数, sum(case when BillableSeconds>=1 then 1 else 0 end) 接通次数,'
+' Destination 外呼号码 from [A9SERVER].[S60623].[dbo].[bmdcallcenterrecord] '
+' where StartTime between '''''+convert(varchar(23),@startDateTime,21)+''''' and '''''
+convert(varchar(23),@endDateTime,21)+''''' group by CustomerID,SalesStaff,Destination'')'
--更新订单号信息
set @sql2 = 'select * into ##temp from openquery(A9Server,''select a.CustomerID,a.ID,a.LastFollowDate,'
+' a.LastFollowContent,cs.CommunicateStatus from [A9SERVER].[S60623].[dbo].[bdCustomerAllocate] a,'
+' [A9SERVER].[S60623].[dbo].[mdcommunicatestatus] cs where len(a.LastCommunicateStatus)>0 '
+' and LastFollowDate between '''''+convert(varchar(23),@startDateTime,21)+''''' and '''''
+convert(varchar(23),@endDateTime,21)+''''' and a.LastCommunicateStatus=convert(varchar(50),cs.ID)'')'
--更新提醒信息
set @sql3 = 'select * into ##temp from openquery(A9Server,'
+' ''select u.UserName,d.DepartName,u.WorkNo from [A9SERVER].[S60623].[dbo].[frmuser] u,'
+' [A9SERVER].[S60623].[dbo].[mdDepartment] d where u.DepartmentID=d.id'')'
BEGIN
--set nocount on
--set nocount off
exec(@sql1)
insert into tblWidth(客户ID,工号,最近接通日期, 通话时长,通话次数,接通次数,外呼号码)
select * from ##temp
if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..##temp') and type='U')
drop table ##temp
--
exec(@sql2)
update tblWidth set 分配ID=s.ID,最后沟通时间=s.LastFollowDate,沟通结果=s.LastFollowContent,
沟通状态=s.CommunicateStatus from ##temp s where
s.CustomerID=tblWidth.客户ID and tblWidth.分配ID is null
if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..##temp') and type='U')
drop table ##temp
exec(@sql3)
update tblWidth set 客服=UserName,部门=DepartName from ##temp s
where s.WorkNo=工号 and 客服 is null
if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..##temp') and type='U')
drop table ##temp
END

复制代码