Power Call record statistics query SQL

DECLARE @startTime datetime
Set @startTime = ' 2016-07-12 '

SELECT * FROM (
--seat number/Phone number/total length of call
Select Zuoxi as Zuoxi1,count (1) as Phonecount,sum (DATEDIFF (ss,jtdate,enddate)) PHONECOUNTSC from Hur_telcallrecord where Vdef2=1 and
--convert (varchar), jtdate,120) = ' 2016-07-12 '
DATEDIFF (day,jtdate, ' 2016-07-12 ') <15
GROUP BY Zuoxi
) X1 Left JOIN (
----Number of registered users
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.phon E
WHERE CONVERT (VARCHAR (), 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 (

--Recharge Total amount
SELECT Tr.zuoxi as Zuoxi3,sum (amount) as Rechargeamount from Yyd_account_moneyrecord m INNER joins Yyd_users_userinfo u on M.user_id=u.id Right JOIN
[Hur_telcallrecord] TR on U.phone=tr.phone
WHERE (moneytype= ' recharge ' or moneytype= ') and state=1 and CONVERT (VARCHAR (Ten), 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 (
--Total investment amount
SELECT Tr.zuoxi as Zuoxi4,sum (amount) as Bidamount from Yyd_borrow_bidrecord m INNER joins Yyd_users_userinfo u on M.bid_us Er_id=u.id right JOIN [Hur_telcallrecord] tr on U.phone=tr.phone
WHERE M.status=1 and CONVERT (VARCHAR (), 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

Power Call record statistics query SQL