电Call记录统计查询sql

最新推荐文章于 2024-06-03 08:37:33 发布

weixin_34043301

最新推荐文章于 2024-06-03 08:37:33 发布

阅读量151

点赞数

本文介绍了一个复杂的SQL查询案例，该查询用于统计特定日期内不同坐席的电话记录详情，包括电话数量、通话总时长、注册用户数、充值总金额及投资总金额等关键指标。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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