/*
手机动作统计
*/
CREATE PROC usp_ActionCount
@planid int,
@updatetype varchar(50)
AS
--------------------------------------------------------------------
CREATE TABLE #tempA (
id int IDENTITY(1,1) NOT NULL ,
mobile varchar(20) NOT NULL ,
updatetype varchar(50)
)
CREATE TABLE #tempB(
id int IDENTITY(1,1) NOT NULL,
mobile varchar(20) NOT NULL ,
updatetype varchar(20)
)
INSERT INTO #tempA (mobile,updatetype) SELECT DISTINCT msisdn,'' FROM tb_PlanMsisdn WHERE plan_id=@planid
DECLARE @id int,@mobile varchar(20)
SELECT @id=COUNT(1) FROM #tempA ta
WHILE @id>0
BEGIN
SELECT @mobile=mobile FROM #tempA ta WHERE ta.id=@id
TRUNCATE TABLE #tempB
INSERT INTO #tempB(mobile,updatetype) SELECT DISTINCT msisdn,updatetype FROM tb_PlanMsisdn tpm WHERE tpm.msisdn=@mobile AND tpm.plan_id=@planid
DECLARE @innerid int,@utype varchar(20),@innermobile varchar(20)
SELECT @innerid=COUNT(1) FROM #tempB tb
WHILE @innerid>0
BEGIN
SELECT @utype=updatetype,@innermobile=tb.mobile FROM #tempB tb WHERE tb.id=@innerid
IF charindex(@utype,@updatetype)>0
BEGIN
UPDATE #tempA SET updatetype =updatetype + CASE @utype
WHEN '1' THEN '周期更新 '
WHEN '2' THEN '关机 '
WHEN '3' THEN '开机 '
WHEN '4' THEN '进入小区 '
WHEN '5' THEN '寻呼响应 '
WHEN '6' THEN '发短信 '
WHEN '7' THEN '主叫 '
WHEN '8' THEN '短信接收 '
ELSE ''
END WHERE mobile=@innermobile
END
SET @innerid=@innerid-1
END
SET @id=@id-1
END
SELECT * FROM #tempA ta
DROP TABLE #tempB
DROP TABLE #tempA
GO
