black_list.sql

INSERT INTO black_list.dbo.Block_list_4G
SELECT ANI,
       GETDATE(),
       0
FROM cskh_hds.dbo.t_Termination_Call_Detail
where CallDispositionFlag =1  and DateTime >=dateadd(day,datediff(day,0,GETDATE()),0)
and Variable5 is not NULL and AgentPeripheralNumber is not NULL
group by ANI having count(ANI) >=10

UNION
SELECT CASE
           WHEN substring(ANI,1,6) ='assist' THEN concat('0',SUBSTRING(ANI,10,len(ANI)))
           WHEN SUBSTRING(ANI,1,2) = '84' THEN concat('assist-',ANI)
           WHEN SUBSTRING(ANI,1,1) = '0' THEN concat('84',SUBSTRING(ANI,2,len(ANI)))
           ELSE '000000'
       END AS ANI,
       GETDATE(),
       0
FROM cskh_hds.dbo.t_Termination_Call_Detail
where CallDispositionFlag =1  and DateTime >=dateadd(day,datediff(day,0,GETDATE()),0)
and Variable5 is not NULL and AgentPeripheralNumber is not NULL
group by ANI having count(ANI) >=10

UNION
SELECT CASE
           WHEN substring(ANI,1,6) ='assist' THEN concat('84',SUBSTRING(ANI,10,len(ANI)))
           WHEN SUBSTRING(ANI,1,2) = '84' THEN concat('assist-0',SUBSTRING(ANI,2,len(ANI)))
           WHEN SUBSTRING(ANI,1,1) = '0' THEN concat('assist-',ANI)
           ELSE '000000'
       END AS ANI,
       GETDATE(),
       0
FROM cskh_hds.dbo.t_Termination_Call_Detail
where CallDispositionFlag =1  and DateTime >=dateadd(day,datediff(day,0,GETDATE()),0)
and Variable5 is not NULL and AgentPeripheralNumber is not NULL
group by ANI having count(ANI) >=10

UNION
SELECT CASE
           WHEN substring(ANI,1,6) ='assist' THEN concat('assist-0',SUBSTRING(ANI,10,len(ANI)))
           WHEN SUBSTRING(ANI,1,2) = '84' THEN concat('0',substring(ANI,2,len(ANI)))
           WHEN SUBSTRING(ANI,1,1) = '0' THEN concat('assist-84',SUBSTRING(ANI,2,len(ANI)))
           ELSE '000000'
       END AS ANI,
       GETDATE(),
       0
FROM cskh_hds.dbo.t_Termination_Call_Detail
where CallDispositionFlag =1  and DateTime >=dateadd(day,datediff(day,0,GETDATE()),0)
and Variable5 is not NULL and AgentPeripheralNumber is not NULL
group by ANI having count(ANI) >=10

blacklist4Viettel4G

INSERT INTO black_list.dbo.Block_list_4G
SELECT ANI,
       GETDATE(),
       0
FROM cskh_hds.dbo.t_Termination_Call_Detail
where CallDispositionFlag =1  and DateTime >=dateadd(day,datediff(day,0,GETDATE()),0)
and Variable5 is not NULL and AgentPeripheralNumber is not NULL
group by ANI having count(ANI) >=10

UNION
SELECT CASE
           WHEN substring(ANI,1,6) ='assist' THEN concat('0',SUBSTRING(ANI,10,len(ANI)))
           WHEN SUBSTRING(ANI,1,2) = '84' THEN concat('assist-',ANI)
           WHEN SUBSTRING(ANI,1,1) = '0' THEN concat('84',SUBSTRING(ANI,2,len(ANI)))
           ELSE '000000'
       END AS ANI,
       GETDATE(),
       0
FROM cskh_hds.dbo.t_Termination_Call_Detail
where CallDispositionFlag =1  and DateTime >=dateadd(day,datediff(day,0,GETDATE()),0)
and Variable5 is not NULL and AgentPeripheralNumber is not NULL
group by ANI having count(ANI) >=10

UNION
SELECT CASE
           WHEN substring(ANI,1,6) ='assist' THEN concat('84',SUBSTRING(ANI,10,len(ANI)))
           WHEN SUBSTRING(ANI,1,2) = '84' THEN concat('assist-0',SUBSTRING(ANI,2,len(ANI)))
           WHEN SUBSTRING(ANI,1,1) = '0' THEN concat('assist-',ANI)
           ELSE '000000'
       END AS ANI,
       GETDATE(),
       0
FROM cskh_hds.dbo.t_Termination_Call_Detail
where CallDispositionFlag =1  and DateTime >=dateadd(day,datediff(day,0,GETDATE()),0)
and Variable5 is not NULL and AgentPeripheralNumber is not NULL
group by ANI having count(ANI) >=10

UNION
SELECT CASE
           WHEN substring(ANI,1,6) ='assist' THEN concat('assist-0',SUBSTRING(ANI,10,len(ANI)))
           WHEN SUBSTRING(ANI,1,2) = '84' THEN concat('0',substring(ANI,2,len(ANI)))
           WHEN SUBSTRING(ANI,1,1) = '0' THEN concat('assist-84',SUBSTRING(ANI,2,len(ANI)))
           ELSE '000000'
       END AS ANI,
       GETDATE(),
       0
FROM cskh_hds.dbo.t_Termination_Call_Detail
where CallDispositionFlag =1  and DateTime >=dateadd(day,datediff(day,0,GETDATE()),0)
and Variable5 is not NULL and AgentPeripheralNumber is not NULL
group by ANI having count(ANI) >=10