日期:2014-05-17 浏览次数:20666 次
select CustomerID,Convert(Varchar(7),lineDate,120) as yyyymm,
sum(case when ResultTypeID=20 then 0 else 1 end) as 接通次数,COUNT(id) as 打过次数
from lines where LineDate> DateAdd(Month,-3,getdate())
group by CustomerID, Convert(Varchar(7),lineDate,120)
DECLARE @LatestMonth Datetime
-- 以现月为限
SET @LatestMonth = GETDATE()
-- 以上月为限
-- SET @LatestMonth = DateAdd(Month,-1,GETDATE())
SELECT t.CustomerID, Convert(VARCHAR(7),t.lineDate,120) AS yyyymm, SUM(CASE WHEN t.ResultTypeID=20 THEN 0 ELSE 1 END) AS 接通次数, SUM(CASE WHEN t.ResultTypeID<>20 THEN 0 ELSE 1 END) AS 拒接次数, COUNT(t.CustomerID) AS 打过次数
FROM lines t WHERE Convert(VARCHAR(7),t.lineDate,120) BETWEEN CONVERT(VARCHAR(7), DateAdd(Month,-2,@LatestMonth) ,120) AND CONVERT(VARCHAR(7), @LatestMonth, 120)
-- 第一个月有打过
AND EXISTS (SELECT CustomerID FROM lines WHERE CustomerID=t.CustomerID AND Convert(VARCHAR(7),lineDate,120)=CONVERT(VARCHAR(7), DateAdd(Month,-2,@LatestMonth) ,120))
-- 第二个月有打过
AND EXISTS (SELECT CustomerID FROM lines WHERE CustomerID=t.CustomerID AND Convert(VARCHAR(7),lineDate,120)=CONVERT(VARCHAR(7), DateAdd(Month,-1,@LatestMonth) ,120))
-- 第三个月有打过
AND EXISTS (SELECT CustomerID FROM lines WHERE CustomerID=t.CustomerID AND Convert(VARCHAR(7),lineDate,120)=CONVERT(VARCHAR(7), @LatestMonth, 120))
GROUP BY t.CustomerID, Convert(VARCHAR(7),t.lineDate,120)
select CustomerID,Convert(Varchar(7),lineDate,120) as yyyymm,
sum(case when ResultTypeID=20 then 0 else 1 end) as 接通次数,COUNT(id) as 打过次数
from lines where LineDate> DateAdd(Month,-3,getdate())
--新增同时满足的事条件:每个月都需要有拨打次数。总次数大于等于3次,注意:此处的3是个变量。
AND DATEPART(mm,Linedate) = DATEPART(mm,getdate())
AND DATEPART(mm,Linedate) = DATEPART(mm,getdate())-1
AND DATEPART(mm,Linedate) = DATEPART(mm,getdate())-2
AND COUNT(id)>=3
group by CustomerID, Convert(Varchar(7),lineDate,120)