2018-05-01
INSERT INTO [dbo].[CustomerLevelDetails]
([CustomerID]
,[CashAmount]
,[ReturnCashAmount]
,[Balance]
,[LevelID]
,[Manual])
select id,0,0,0,0,0
from CustomerBaseInfo
--导电话号码
with t
as
(
SELECT FID, PhoneNumber, 0 Belong, 0 CallStatus,0 SmsStatus,
GetDate() RegisterDate, GetDate() LastestUpdateTime, '00001' HandleBy
FROM (
SELECT FID, sSJ, sTel, sTel1, sTel2
FROM H_TMK tmk) AS src
UNPIVOT (
PhoneNumber FOR PhoneType IN
(sSJ, sTel, sTel1, sTel2)) AS UNPVT
where ISNULL(PhoneNumber,'') <> ''
)
insert into CustomerPhoneNumber (CustomerID,PhoneNumber,Belong,CallStatus,SmsStatus,RegisterDate,LastestUpdateTime,HandleBy)
SELECT i.Now, PhoneNumber, 0 Belong, 0 CallStatus,0 SmsStatus,
GetDate() RegisterDate, GetDate() LastestUpdateTime, '00001' HandleBy
from t
inner join CustomerBaseInfoID i on t.FID=i.Original
--------------------
with a
as
(
select sum(AllTotal) total, CustomerID from FinanceTotal
where DocType not like '退%'
group by CustomerID
),
b as
(
select sum(AllTotal) rtn, CustomerID from FinanceTotal
where DocType like '退%'
group by CustomerID
)
insert into CustomerLevelDetails
select a.CustomerID, total, ABS(isnull(rtn,0)) rtn, total+isnull(rtn, 0) Balance,
case when (total+isnull(rtn, 0)) <50000 then 0
when (total+isnull(rtn, 0)) >=50000 and (total+isnull(rtn, 0)) <150000 then 1
when (total+isnull(rtn, 0)) >=150000 and (total+isnull(rtn, 0)) <300000 then 2
when (total+isnull(rtn, 0)) >=300000 and (total+isnull(rtn, 0)) <500000 then 3
when (total+isnull(rtn, 0)) >=500000 then 4
end level, 0 manual
from a
left join b on a.CustomerID=b.CustomerID