lyfz_repos/铂金版源码/DBFile/SQL/更新相关文件/重置聚合客户表.sql

--创建聚合后的客户视图
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[Vw_AggregationCustomer]'))
DROP VIEW [dbo].[Vw_AggregationCustomer]
GO
CREATE VIEW [dbo].[Vw_AggregationCustomer]
as	
with t as(
select 
    GM_CustomerGroupID
     ,CONVERT(varchar(200),dbo.FunTrim(dbo.AggregateString([Cus_Name]+'{$$}/{where}'+CONVERT(varchar(5),[GM_Relation])+'{$$}={$$}本人{$$}s')
     +'/'+ dbo.AggregateString([Cus_Name]+'{$$}/{where}'+CONVERT(varchar(5),[GM_Relation])+'{$$}<>{$$}本人{$$}s'),'/')) as Cus_Name
   --,CONVERT(varchar(200),dbo.AggregateString([Cus_Name]+'{$$}/')) as Cus_Name
  ,CONVERT(varchar(200),dbo.FunTrim(dbo.AggregateString([Cus_Name_py]+'{$$}/{where}'+CONVERT(varchar(5),[GM_Relation])+'{$$}={$$}本人{$$}s')
     +'/'+ dbo.AggregateString([Cus_Name_py]+'{$$}/{where}'+CONVERT(varchar(5),[GM_Relation])+'{$$}<>{$$}本人{$$}s'),'/'))  as Cus_Name_py
 -- ,CONVERT(varchar(200),dbo.AggregateString([Cus_Name_py]+'{$$}/')) as Cus_Name_py
   ,CONVERT(varchar(200),dbo.FunTrim(dbo.AggregateString([Cus_Sex_cs]+'{$$}/{where}'+CONVERT(varchar(5),[GM_Relation])+'{$$}={$$}本人{$$}s')
     +'/'+ dbo.AggregateString([Cus_Sex_cs]+'{$$}/{where}'+CONVERT(varchar(5),[GM_Relation])+'{$$}<>{$$}本人{$$}s'),'/')) as Cus_Sex_cs
 -- ,CONVERT(varchar(200),dbo.AggregateString([Cus_Sex_cs]+'{$$}/{where}'+CONVERT(varchar(1),[GM_ProtagonistCustomer])+'{$$}={$$}1{$$}i')) as  Cus_Sex_cs
  ,CONVERT(varchar(200),dbo.FunTrim(dbo.AggregateString([Cus_Telephone]+'{$$}/{where}'+CONVERT(varchar(5),[GM_Relation])+'{$$}={$$}本人{$$}s')
     +'/'+ dbo.AggregateString([Cus_Telephone]+'{$$}/{where}'+CONVERT(varchar(5),[GM_Relation])+'{$$}<>{$$}本人{$$}s'),'/')) as Cus_Telephone
 --,CONVERT(varchar(200),dbo.AggregateString([Cus_Telephone]+'{$$}/')) as Cus_Telephone
 --地区
  ,CONVERT(varchar(200),dbo.FunTrim(dbo.AggregateString([Cus_Region]+'{$$}/{where}'+CONVERT(varchar(5),[GM_Relation])+'{$$}={$$}本人{$$}s')
     +'/'+ dbo.AggregateString([Cus_Region]+'{$$}/{where}'+CONVERT(varchar(5),[GM_Relation])+'{$$}<>{$$}本人{$$}s'),'/')) as Cus_Region
 --,CONVERT(varchar(200),dbo.AggregateString([Cus_Region]+'{$$}/')) as Cus_Region
--年龄字符串
 ,CONVERT(varchar(200),dbo.AggregateString(CONVERT(varchar(10),[Age_String])+'{$$}/{where}'+CONVERT(varchar(1),[GM_ProtagonistCustomer])+'{$$}={$$}1{$$}i')) as Age_String
--儿童年龄天数 int 型
,CONVERT(int,dbo.AggregateString(CONVERT(varchar(10),[Age_Day])+'{$$}/{where}'+CONVERT(varchar(5),[GM_Relation])+'{$$}={$$}本人{$$}s')) as Age_Day
  --儿童年龄岁数 int 型
,CONVERT(int,dbo.AggregateString(CONVERT(varchar(10),[Age_Year])+'{$$}/{where}'+CONVERT(varchar(5),[GM_Relation])+'{$$}={$$}本人{$$}s')) as Age_Year
  --儿童生日或主联系人生日 统一转为公历
,CONVERT(datetime,dbo.AggregateString(CONVERT(varchar(10),[GregorianCalendar_Birthday])+'{$$}/{where}'+CONVERT(varchar(5),[GM_Relation])+'{$$}={$$}本人{$$}s')) as GregorianCalendar_Birthday
  --儿童当前年生日日期或主联系人当前年生日日期 统一转为公历
,CONVERT(datetime,dbo.AggregateString(CONVERT(varchar(10),[GregorianCalendar_NowYearBirthday])+'{$$}/{where}'+CONVERT(varchar(5),[GM_Relation])+'{$$}={$$}本人{$$}s')) as GregorianCalendar_NowYearBirthday
  --主联系人婚期 统一转为公历
,CONVERT(datetime,dbo.AggregateString(CONVERT(varchar(10),[GregorianCalendar_DayForMarriage])+'{$$}/{where}'+CONVERT(varchar(5),[GM_Relation])+'{$$}={$$}本人{$$}s')) as GregorianCalendar_DayForMarriage
  --主联系人当前年婚期日期 统一转为公历
,CONVERT(datetime,dbo.AggregateString(CONVERT(varchar(10),[GregorianCalendar_NowYearDayForMarriage])+'{$$}/{where}'+CONVERT(varchar(5),[GM_Relation])+'{$$}={$$}本人{$$}s')) as GregorianCalendar_NowYearDayForMarriage

--以下是主要联系人
,CONVERT(varchar(200),dbo.AggregateString([Cus_CustomerNumber]+'{$$}/{where}'+CONVERT(varchar(1),[GM_Master])+'{$$}={$$}1{$$}i')) as M_Cus_CustomerNumber
,CONVERT(varchar(200),dbo.AggregateString([Cus_Name]+'{$$}/{where}'+CONVERT(varchar(1),[GM_Master])+'{$$}={$$}1{$$}i')) as M_Cus_Name
,CONVERT(varchar(200),dbo.AggregateString([Cus_Name_py]+'{$$}/{where}'+CONVERT(varchar(1),[GM_Master])+'{$$}={$$}1{$$}i')) as M_Cus_Name_py
,CONVERT(varchar(200),dbo.AggregateString([Cus_Sex_cs]+'{$$}/{where}'+CONVERT(varchar(1),[GM_Master])+'{$$}={$$}1{$$}i')) as M_Cus_Sex_cs
,CONVERT(varchar(200),dbo.AggregateString([Cus_Telephone]+'{$$}/{where}'+CONVERT(varchar(1),[GM_Master])+'{$$}={$$}1{$$}i')) as M_Cus_Telephone
--主要联系人微信号
,CONVERT(varchar(200),dbo.AggregateString([Cus_MicroSignal]+'{$$}/{where}'+CONVERT(varchar(1),[GM_Master])+'{$$}={$$}1{$$}i')) as M_Cus_MicroSignal
,CONVERT(varchar(200),dbo.AggregateString([Cus_QQ]+'{$$}/{where}'+CONVERT(varchar(1),[GM_Master])+'{$$}={$$}1{$$}i')) as M_Cus_QQ
--主要联系人地区
,CONVERT(varchar(200),dbo.AggregateString([Cus_Region]+'{$$}/{where}'+CONVERT(varchar(1),[GM_Master])+'{$$}={$$}1{$$}i')) as M_Cus_Region
--主要联系人地址
,dbo.AggregateString([Cus_Address]+'{$$}/{where}'+CONVERT(varchar(1),[GM_Master])+'{$$}={$$}1{$$}i') as M_Cus_Address
--客户组中成员个数
,count(Cus_CustomerNumber) as Ord_ViceOrderCount
	 from 
	   dbo.View_CustomerGroupMembersAndErpCustomer
	 group by GM_CustomerGroupID  
)
select 
[tb_ErpCustomerGroup].id
,[GP_OrderNumber]
,[GP_CustomerGroupID]
,[GP_GroupType]
,[GP_CustomerType]
,[GP_CustomerStatus]
,[GP_LossReason]
,[GP_DegreeOfIntent]
,[GP_CustomerSource]
,GP_CreateDatetime
,GP_UpdateDatetime
,GP_ISIntentionCustomer
,GP_ReminderTime
,[GP_IntroducerCustomerID],t.*
,[dbo].[Vw_ErpMemberCardSimpleInfo].[Mc_CradNumber]
,[dbo].[Vw_ErpMemberCardSimpleInfo].[Mc_CardType]
from 
	  [tb_ErpCustomerGroup]
	  left join
	   t
		on [tb_ErpCustomerGroup].[GP_CustomerGroupID]=t.[GM_CustomerGroupID]
		left join
		[dbo].[Vw_ErpMemberCardSimpleInfo]
		on t.M_Cus_CustomerNumber=[dbo].[Vw_ErpMemberCardSimpleInfo].[Mc_CustomerNumber]
GO
--根据聚合后的客户视图创建聚合后的客户临时表
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tempTB_AggregationCustomer]'))
DROP table [dbo].[tempTB_AggregationCustomer]
GO
select * into tempTB_AggregationCustomer from [Vw_AggregationCustomer]
go

--唯一索引
CREATE UNIQUE NONCLUSTERED INDEX [IX_tempTB_AggregationCustomer_GP_CustomerGroupID] ON [dbo].[tempTB_AggregationCustomer] 
(
	[GP_CustomerGroupID] desc
)
--索引
CREATE NONCLUSTERED INDEX [IX_tempTB_AggregationCustomer_GP_OrderNumber] ON [dbo].[tempTB_AggregationCustomer] 
(
	[GP_OrderNumber] desc
)
go
CREATE NONCLUSTERED INDEX [IX_tempTB_AggregationCustomer_Cus_Name] ON [dbo].[tempTB_AggregationCustomer] 
(
	[Cus_Name] desc
)
go
CREATE NONCLUSTERED INDEX [IX_tempTB_AggregationCustomer_Cus_Name_py] ON [dbo].[tempTB_AggregationCustomer] 
(
	Cus_Name_py desc
)
go
CREATE NONCLUSTERED INDEX [IX_tempTB_AggregationCustomer_Cus_Telephone] ON [dbo].[tempTB_AggregationCustomer] 
(
	Cus_Telephone desc
)
go
CREATE NONCLUSTERED INDEX [IX_tempTB_AggregationCustomer_GP_CustomerSource] ON [dbo].[tempTB_AggregationCustomer] 
(
	[GP_CustomerSource] desc
)
go
CREATE NONCLUSTERED INDEX [IX_tempTB_AggregationCustomer_M_Cus_Name] ON [dbo].[tempTB_AggregationCustomer] 
(
	[M_Cus_Name] desc
)
go
CREATE NONCLUSTERED INDEX [IX_tempTB_AggregationCustomer_M_Cus_Name_py] ON [dbo].[tempTB_AggregationCustomer] 
(
	M_Cus_Name_py desc
)
go
CREATE NONCLUSTERED INDEX [IX_tempTB_AggregationCustomer_M_Cus_Telephone] ON [dbo].[tempTB_AggregationCustomer] 
(
	M_Cus_Telephone desc
)
go
CREATE NONCLUSTERED INDEX [IX_tempTB_AggregationCustomer_Mc_CradNumber] ON [dbo].[tempTB_AggregationCustomer] 
(
	Mc_CradNumber desc
)
go
--创建视图 dbo.tempTB_AggregationCustomer 表的Vw_GroupByAggregationCustomer视图
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[Vw_GroupByAggregationCustomer]'))
DROP VIEW [dbo].[Vw_GroupByAggregationCustomer]
GO
CREATE VIEW [Vw_GroupByAggregationCustomer]
AS

with t as(		
SELECT  
max(GP_CustomerGroupID) as GP_CustomerGroupID
from tempTB_AggregationCustomer
  group by Cus_Name,M_Cus_CustomerNumber
  )
  select * from tempTB_AggregationCustomer 
  where GP_CustomerGroupID in (select GP_CustomerGroupID from t)
go

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tempTB_GroupByAggregationCustomer]'))
DROP table [dbo].[tempTB_GroupByAggregationCustomer]
GO
select * into tempTB_GroupByAggregationCustomer from [Vw_GroupByAggregationCustomer]
go

--唯一索引
CREATE UNIQUE NONCLUSTERED INDEX [IX_tempTB_GroupByAggregationCustomer_GP_CustomerGroupID] ON [dbo].[tempTB_GroupByAggregationCustomer] 
(
	[GP_CustomerGroupID] desc
)
--索引
CREATE NONCLUSTERED INDEX [IX_tempTB_GroupByAggregationCustomer_GP_OrderNumber] ON [dbo].[tempTB_GroupByAggregationCustomer] 
(
	[GP_OrderNumber] desc
)
go
CREATE NONCLUSTERED INDEX [IX_tempTB_GroupByAggregationCustomer_Cus_Name] ON [dbo].[tempTB_GroupByAggregationCustomer] 
(
	[Cus_Name] desc
)
go
CREATE NONCLUSTERED INDEX [IX_tempTB_GroupByAggregationCustomer_Cus_Name_py] ON [dbo].[tempTB_GroupByAggregationCustomer] 
(
	Cus_Name_py desc
)
go
CREATE NONCLUSTERED INDEX [IX_tempTB_GroupByAggregationCustomer_Cus_Telephone] ON [dbo].[tempTB_GroupByAggregationCustomer] 
(
	Cus_Telephone desc
)
go
CREATE NONCLUSTERED INDEX [IX_tempTB_GroupByAggregationCustomer_GP_CustomerSource] ON [dbo].[tempTB_GroupByAggregationCustomer] 
(
	[GP_CustomerSource] desc
)
go
CREATE NONCLUSTERED INDEX [IX_tempTB_GroupByAggregationCustomer_M_Cus_Name] ON [dbo].[tempTB_GroupByAggregationCustomer] 
(
	[M_Cus_Name] desc
)
go
CREATE NONCLUSTERED INDEX [IX_tempTB_GroupByAggregationCustomer_M_Cus_Name_py] ON [dbo].[tempTB_GroupByAggregationCustomer] 
(
	M_Cus_Name_py desc
)
go
CREATE NONCLUSTERED INDEX [IX_tempTB_GroupByAggregationCustomer_M_Cus_Telephone] ON [dbo].[tempTB_GroupByAggregationCustomer] 
(
	M_Cus_Telephone desc
)
go
CREATE NONCLUSTERED INDEX [IX_tempTB_GroupByAggregationCustomer_Mc_CradNumber] ON [dbo].[tempTB_GroupByAggregationCustomer] 
(
	Mc_CradNumber desc
)
go