DECLARE @startTime datetime

set @startTime='2016-07-12'

SELECT * FROM (

--坐席号/电话个数/通话总时长

select zuoxi as zuoxi1,COUNT(1) as PhoneCount,SUM(DATEDIFF(ss,jtDate,endDate)) PhoneCountSC from HUR_TelCallRecord where vdef2=1 and

--Convert(varchar(10),jtDate,120)='2016-07-12'

DATEDIFF(day,jtdate,'2016-07-12')<15

group by zuoxi

)x1 LEFT JOIN (

----注册用户数

SELECT tr.zuoxi as zuoxi2,COUNT(1) as RegUserCount FROM [dbo].YYD_Users_UserInfo u INNER JOIN YYD_Users_RegInfo r ON u.user_id=r.id RIGHT JOIN [HUR_TelCallRecord] tr ON u.phone=tr.phone

WHERE CONVERT(VARCHAR(10), RegTime,120)= '2016-07-12' AND

DATEDIFF(DAY,tr.jtDate,'2016-07-12')<15

GROUP BY zuoxi

)x2 ON x1.zuoxi1 = x2.zuoxi2 LEFT JOIN (

--充值总金额

SELECT tr.zuoxi as zuoxi3,sum(amount) as RechargeAmount FROM YYD_Account_MoneyRecord m INNER JOIN YYD_Users_UserInfo u ON m.user_id=u.id RIGHT JOIN

[HUR_TelCallRecord] tr ON u.phone=tr.phone

WHERE (moneytype='充值' or moneytype='线下充值' ) and state=1 and CONVERT(VARCHAR(10), paytime,120)='2016-07-12' and

DATEDIFF(DAY,tr.jtDate,'2016-07-12')<15

GROUP BY zuoxi

)x3 ON x1.zuoxi1=x3.zuoxi3 LEFT JOIN (

--投资总金额

SELECT tr.zuoxi as zuoxi4,sum(amount) as BidAmount FROM YYD_Borrow_BidRecord m INNER JOIN YYD_Users_UserInfo u ON m.bid_user_id=u.id RIGHT JOIN [HUR_TelCallRecord] tr ON u.phone=tr.phone

WHERE m.status=1 and CONVERT(VARCHAR(10), createtime,120)='2016-07-12'

and borrow_id in(select borrow_id from YYD_Borrow_borrowinfo_ext as ext where ext.borrow_product<>50) and

DATEDIFF(DAY,tr.jtDate,'2016-07-12')<15

GROUP BY zuoxi

)x4 ON x1.zuoxi1=x4.zuoxi4