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