最新电Call记录统计-full hash join用法

declare @time datetime
set @time='2016-07-01'

--最新的电Call记录统计查询
--SELECT t.zuoxi1,t.PhoneCount,t.PhoneCountSC,t.zuoxi2,t.RegUserCount,t.zuoxi3,t.RechargeAmount,
SELECT ISNULL(ISNULL(t.zuoxi1,(ISNULL(t.zuoxi2,t.zuoxi3))),t.zuoxi4) as zuoxi,t.PhoneCount,t.PhoneCountSC,t.zuoxi2,t.RegUserCount,t.zuoxi3,t.RechargeAmount,

t.zuoxi4,t.BidAmount from(
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-20'
--DATEDIFF(day,jtdate,'2016-06-25')<15
group by zuoxi
) as tel
full hash join
(
----注册用户数
SELECT tr.zuoxi as zuoxi2,COUNT(distinct u.user_id) 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-20' AND
DATEDIFF(DAY,tr.jtDate,'2016-07-20')<15 AND DATEDIFF(DAY,tr.jtDate,'2016-07-20')>0
GROUP BY zuoxi
)
as reg
on reg.zuoxi2=tel.zuoxi1
full hash join
(
--充值总金额
SELECT tr.zuoxi as zuoxi3,sum(amount) as RechargeAmount FROM YYD_Account_MoneyRecord m left JOIN YYD_Users_UserInfo u ON m.user_id=u.user_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-20' and
DATEDIFF(DAY,tr.jtDate,'2016-07-20')<15 AND DATEDIFF(DAY,tr.jtDate,'2016-07-20')>0
GROUP BY zuoxi
) as re
on re.zuoxi3=tel.zuoxi1
full hash join
(
--投资总金额
SELECT tr.zuoxi as zuoxi4,sum(amount) as BidAmount FROM YYD_Borrow_BidRecord m left JOIN YYD_Users_UserInfo u ON m.bid_user_id=u.user_id RIGHT JOIN [HUR_TelCallRecord] tr ON u.phone=tr.phone
WHERE m.status=1 and CONVERT(VARCHAR(10), createtime,120)='2016-07-20'
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-20')<15 AND DATEDIFF(DAY,tr.jtDate,'2016-07-20')>0
GROUP BY zuoxi
)as bid
on bid.zuoxi4 = tel.zuoxi1
WHERE tel.zuoxi1 IS NOT NULL
) as t order by PhoneCountSC desc

 

 

--充值表
select * from YYD_Account_MoneyRecord order by ID desc

where state=1 and CONVERT(VARCHAR(10), paytime,120)='2016-06-25'

update YYD_Account_MoneyRecord set user_id=83946,paytime='2016-06-25 10:00:41.232',state=1,moneytype='充值' where ID=12399
--投资表
select * from YYD_Borrow_BidRecord order by ID desc
update YYD_Borrow_BidRecord set createtime='2016-06-25 11:00:23.211',bid_user_id=83946,bid_user_account='13025874103',status=1 where ID=5820

--YYD_Borrow_BorrowInfo
select * from YYD_Borrow_BorrowInfo order by ID desc
update YYD_Borrow_BorrowInfo set user_id=83946 where ID=1882

select * from YYD_Users_RegInfo where ID=83946 where CONVERT(VARCHAR(10), RegTime,120)= '2016-06-25' order by ID

select * from [HUR_TelCallRecord] where phone=13025874103 order by id desc

update [HUR_TelCallRecord] set phone=13025874166 where id=104212 or id=104210 or id=104212

update [HUR_TelCallRecord] set vdef2=1 where id=104209 or id=104206

select * from YYD_Users_UserInfo as u where phone in(select phone from [HUR_TelCallRecord] as t)

select * from [HUR_TelCallRecord] where phone=13025874103 order by id desc

select * from YYD_Users_RegInfo as a where ID in(select b.user_id from YYD_Users_UserInfo as b where a.ID = b.user_id)

order by ID desc

select * from YYD_Users_UserInfo where user_id=1144

select * from YYD_Borrow_BidRecord where bid_user_id =1239

select * from HUR_TelCallRecord order by jtDate desc

 

 

select * from HUR_TelCallRecord where vdef2=1 and
Convert(varchar(10),jtDate,120)='2016-07-01' order by id desc

 

--可以查询出那个坐席号是空的
select zuoxi as zuoxi1,COUNT(1) as PhoneCount,SUM(DATEDIFF(ss,jtDate,endDate)) PhoneCountSC,id from HUR_TelCallRecord where vdef2=1 and
Convert(varchar(10),jtDate,120)='2016-07-01'
group by zuoxi,id