2018-06-23

2018 年 6 月 23 日2018 年 6 月 23 日 by 沧海之树 In 工作

select max(Date) from FinanceTotal

select * from YUN_HIS20180621.dbo.H_Project;

select * from bh20180529.dbo.ItemCategory

select * from YUN_HIS20180521.dbo.H_Project;

select * from bh20180529.dbo.ItemInfo

select * from YUN_HIS20180521.dbo.H_Project_Data

select * from YUN_HIS20180621.dbo.H_Project_Data

select * from YUN_HIS20180621.dbo.H_Project_Sort

select * from YUN_HIS20180521.dbo.H_Project_Sort

select * from YUN_HIS20180521.dbo.H_DeptSet
select * from YUN_HIS20180621.dbo.H_DeptSet

select * from YUN_HIS20180521.dbo.H_Employee
select * from YUN_HIS20180621.dbo.H_Employee where sEmpNo not in (select sEmpNo from YUN_HIS20180521.dbo.H_Employee)

select * from bh20180529.dbo.Employee where Name like '刘%'

select * from bh20180420.dbo.Employee

select * from bh20180529.dbo.PositionInfo where PositionName like '%咨询%';

select count(*) from YUN_HIS20180621.dbo.H_TMK;

select count(*) from bh20180529.dbo.CustomerBaseInfo;

select * from bh20180529.dbo.category

select * from YUN_HIS20180621.dbo.H_Drugs_Index

select * from bh20180529.dbo.GoodsInfo

select * from YUN_HIS20180621.dbo.H_WZProject_Data

select * from bh20180529.dbo.medicineinfo

select * from YUN_HIS20180621.dbo.H_Drugs_index_Data

select * from bh20180529.dbo.MemberCardHandle where MemberCardNum like '1806%'

select * from bh20180529.dbo.PrePaymentRecharge;

select * from bh20180529.dbo.MorHKeepAccountsDetails where ID=44496

select * from bh20180529.dbo.ChargeForMHDetails

select * from bh20180529.dbo.FinanceTotal where DocCode=140223--DocInfoCode='Z868CWVIHYIN'

select * from bh20180529.dbo.ReturnMoneyForCertainDoc where Date>'2018-05-20'

44496 VYS9GSYUW27A3I4QTGC7 NULL m000015 NULL 17.00 1.00 1.00 17.00 0 NULL NULL 2
44496 VYS9GSYUW27A3I4QTGC7 NULL m000015 NULL 17.00 1.00 1.00 17.00 0 NULL NULL 2

select count(*) from bh20180529.dbo.CustomerBaseInfo;

select * from bh20180529.dbo.StatusOfCustomer;

exec sp_executesql N'SELECT [t6].[Age], (
SELECT COUNT(*)
FROM (
SELECT NULL AS [EMPTY]
FROM [dbo].[CustomerComeLog] AS [t17]
WHERE [t17].[CustomerID] = [t6].[ID]
) AS [t18]
) AS [ComeCount],
(CASE
WHEN [t7].[LastestConsultingItem] IS NOT NULL THEN [t8].[Name]
ELSE CONVERT(NVarChar(50),@p15)
END) AS [ConsultingItem], [t6].[IsCome] AS [cs], [t6].[ID] AS [CustomerID], [t9].[Status] AS [cs2], [t10].[Success] AS [ss], [t7].[FirstComeDate],
(CASE
WHEN [t7].[FirstConsultingItem] IS NOT NULL THEN [t11].[Name]
ELSE CONVERT(NVarChar(50),@p16)
END) AS [FirstConsultingItem],
(CASE
WHEN [t7].[ReferralsID] IS NOT NULL THEN 1
ELSE 0
END) AS [ISReferral], [t7].[LastestComeDate], [t7].[LastestTrackDate],
(CASE
WHEN [t12].[MemberCardNum] IS NULL THEN NULL
ELSE (
SELECT [t21].[NewRankID]
FROM (
SELECT [t20].[NewRankID]
FROM (
SELECT [t19].[NewRankID], [t19].[Valid]
FROM [dbo].[MemberCardHandle] AS [t19]
WHERE [t19].[CustomerID] = [t6].[ID]
) AS [t20]
WHERE [t20].[Valid] = 1
) AS [t21]
)
END) AS [MemberCardLevelID], [t12].[MemberCardNum], [t6].[Name], [t7].[Rating], [t13].[Region], [t6].[RegisterDate],
(CASE
WHEN [t6].[SceneEmployee] IS NOT NULL THEN [t14].[Name]
ELSE CONVERT(NVarChar(20),@p17)
END) AS [SceneEmployeeName], [t6].[Sex],
(CASE
WHEN [t6].[TMKEmployee] IS NOT NULL THEN [t15].[Name]
ELSE CONVERT(NVarChar(20),@p18)
END) AS [TmkEmployeeName],
(CASE
WHEN [t6].[UnderstandWayID] IS NULL THEN CONVERT(NVarChar(20),@p19)
ELSE [t16].[UnderstandWayName]
END) AS [UnderstandWay], [t6].[VIP] AS [Vip], [t6].[Address], [t6].[Amount], [t6].[Balance], [t6].[BirthDay], [t6].[ChannelEmployee], [t6].[CIdentity], [t6].[ComeTimes], [t6].[PresentationPeopleID], [t6].[RegionID], [t6].[CustomerServiceEmployee], [t6].[UnderstandWayID], [t6].[DoctorEmployee], [t6].[EducationID], [t6].[Email], [t6].[SceneEmployee], [t6].[TMKEmployee], [t6].[TempEmployee], [t6].[FirstPhone], [t6].[Forzen], [t6].[HasMedical], [t6].[HasSickHistory], [t6].[IntegrationBalance], [t6].[IsMember], [t6].[LastestOperateEmployee], [t6].[MSN], [t6].[NetChannelID], [t6].[OtherInfo], [t6].[PresentationInsideID], [t6].[PresentationMode], [t6].[PresentationOutsideID], [t6].[ProfessionID], [t6].[QQ], [t6].[RankID], [t6].[Remark], [t6].[Remove], [t6].[RowVersion], [t6].[SecondPhone], [t6].[Status], [t6].[Success], [t6].[SuccessTimes], [t6].[ThirdPhone], [t6].[ZipCode]
FROM (
SELECT [t5].[ID], [t5].[Address], [t5].[Age], [t5].[Amount], [t5].[Balance], [t5].[BirthDay], [t5].[ChannelEmployee], [t5].[CIdentity], [t5].[ComeTimes], [t5].[PresentationPeopleID], [t5].[RegionID], [t5].[CustomerServiceEmployee], [t5].[UnderstandWayID], [t5].[DoctorEmployee], [t5].[EducationID], [t5].[Email], [t5].[SceneEmployee], [t5].[TMKEmployee], [t5].[TempEmployee], [t5].[FirstPhone], [t5].[Forzen], [t5].[HasMedical], [t5].[HasSickHistory], [t5].[IntegrationBalance], [t5].[IsCome], [t5].[IsMember], [t5].[LastestOperateEmployee], [t5].[MSN], [t5].[Name], [t5].[NetChannelID], [t5].[OtherInfo], [t5].[PresentationInsideID], [t5].[PresentationMode], [t5].[PresentationOutsideID], [t5].[ProfessionID], [t5].[QQ], [t5].[RankID], [t5].[RegisterDate], [t5].[Remark], [t5].[Remove], [t5].[RowVersion], [t5].[SecondPhone], [t5].[Sex], [t5].[Status], [t5].[Success], [t5].[SuccessTimes], [t5].[ThirdPhone], [t5].[VIP], [t5].[ZipCode], [t5].[ROW_NUMBER]
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY [t4].[ID], [t4].[Address], [t4].[Age], [t4].[Amount], [t4].[Balance], [t4].[BirthDay], [t4].[ChannelEmployee], [t4].[CIdentity], [t4].[ComeTimes], [t4].[PresentationPeopleID], [t4].[RegionID], [t4].[CustomerServiceEmployee], [t4].[UnderstandWayID], [t4].[DoctorEmployee], [t4].[EducationID], [t4].[Email], [t4].[SceneEmployee], [t4].[TMKEmployee], [t4].[TempEmployee], [t4].[FirstPhone], [t4].[Forzen], [t4].[HasMedical], [t4].[HasSickHistory], [t4].[IntegrationBalance], [t4].[IsCome], [t4].[IsMember], [t4].[LastestOperateEmployee], [t4].[MSN], [t4].[Name], [t4].[NetChannelID], [t4].[OtherInfo], [t4].[PresentationInsideID], [t4].[PresentationMode], [t4].[PresentationOutsideID], [t4].[ProfessionID], [t4].[QQ], [t4].[RankID], [t4].[RegisterDate], [t4].[Remark], [t4].[Remove], [t4].[RowVersion], [t4].[SecondPhone], [t4].[Sex], [t4].[Status], [t4].[Success], [t4].[SuccessTimes], [t4].[ThirdPhone], [t4].[VIP], [t4].[ZipCode]) AS [ROW_NUMBER], [t4].[ID], [t4].[Address], [t4].[Age], [t4].[Amount], [t4].[Balance], [t4].[BirthDay], [t4].[ChannelEmployee], [t4].[CIdentity], [t4].[ComeTimes], [t4].[PresentationPeopleID], [t4].[RegionID], [t4].[CustomerServiceEmployee], [t4].[UnderstandWayID], [t4].[DoctorEmployee], [t4].[EducationID], [t4].[Email], [t4].[SceneEmployee], [t4].[TMKEmployee], [t4].[TempEmployee], [t4].[FirstPhone], [t4].[Forzen], [t4].[HasMedical], [t4].[HasSickHistory], [t4].[IntegrationBalance], [t4].[IsCome], [t4].[IsMember], [t4].[LastestOperateEmployee], [t4].[MSN], [t4].[Name], [t4].[NetChannelID], [t4].[OtherInfo], [t4].[PresentationInsideID], [t4].[PresentationMode], [t4].[PresentationOutsideID], [t4].[ProfessionID], [t4].[QQ], [t4].[RankID], [t4].[RegisterDate], [t4].[Remark], [t4].[Remove], [t4].[RowVersion], [t4].[SecondPhone], [t4].[Sex], [t4].[Status], [t4].[Success], [t4].[SuccessTimes], [t4].[ThirdPhone], [t4].[VIP], [t4].[ZipCode]
FROM (
SELECT DISTINCT [t1].[ID], [t1].[Address], [t1].[Age], [t1].[Amount], [t1].[Balance], [t1].[BirthDay], [t1].[ChannelEmployee], [t1].[CIdentity], [t1].[ComeTimes], [t1].[PresentationPeopleID], [t1].[RegionID], [t1].[CustomerServiceEmployee], [t1].[UnderstandWayID], [t1].[DoctorEmployee], [t1].[EducationID], [t1].[Email], [t1].[SceneEmployee], [t1].[TMKEmployee], [t1].[TempEmployee], [t1].[FirstPhone], [t1].[Forzen], [t1].[HasMedical], [t1].[HasSickHistory], [t1].[IntegrationBalance], [t1].[IsCome], [t1].[IsMember], [t1].[LastestOperateEmployee], [t1].[MSN], [t1].[Name], [t1].[NetChannelID], [t1].[OtherInfo], [t1].[PresentationInsideID], [t1].[PresentationMode], [t1].[PresentationOutsideID], [t1].[ProfessionID], [t1].[QQ], [t1].[RankID], [t1].[RegisterDate], [t1].[Remark], [t1].[Remove], [t1].[RowVersion], [t1].[SecondPhone], [t1].[Sex], [t1].[Status], [t1].[Success], [t1].[SuccessTimes], [t1].[ThirdPhone], [t1].[VIP], [t1].[ZipCode]
FROM [dbo].[CustomerComeLog] AS [t0]
INNER JOIN [dbo].[CustomerBaseInfo] AS [t1] ON [t1].[ID] = [t0].[CustomerID]
LEFT OUTER JOIN [dbo].[MemberCardInfo] AS [t2] ON [t2].[CustomerID] = [t1].[ID]
LEFT OUTER JOIN [dbo].[CustomerAppendInfo] AS [t3] ON [t3].[CustomerID] = [t1].[ID]
WHERE ([t0].[Date] >= @p0) AND ([t0].[Date] < @p1) AND ([t1].[Name] LIKE @p2) AND ((
(CASE
WHEN @p3 = 1 THEN
(CASE
WHEN ([t2].[MemberCardNum] IS NOT NULL) AND ([t2].[MemberCardNum] LIKE @p4) THEN 1
WHEN NOT (([t2].[MemberCardNum] IS NOT NULL) AND ([t2].[MemberCardNum] LIKE @p4)) THEN 0
ELSE NULL
END)
WHEN [t2].[MemberCardNum] IS NOT NULL THEN CONVERT(Int,
(CASE
WHEN [t2].[MemberCardNum] LIKE @p5 THEN 1
WHEN NOT ([t2].[MemberCardNum] LIKE @p5) THEN 0
ELSE NULL
END))
ELSE @p6
END)) = 1) AND (NOT ([t1].[Remove] = 1)) AND ((CONVERT(TinyInt,[t3].[Rating])) IN (@p7, @p8, @p9, @p10, @p11, @p12))
) AS [t4]
) AS [t5]
WHERE [t5].[ROW_NUMBER] BETWEEN @p13 + 1 AND @p13 + @p14
) AS [t6]
LEFT OUTER JOIN [dbo].[CustomerAppendInfo] AS [t7] ON [t7].[CustomerID] = [t6].[ID]
LEFT OUTER JOIN [dbo].[ItemForConsultingCategory] AS [t8] ON [t8].[ID] = [t7].[LastestConsultingItem]
LEFT OUTER JOIN [dbo].[StatusOfCustomer] AS [t9] ON [t9].[CustomerID] = [t6].[ID]
LEFT OUTER JOIN [dbo].[SuccessOfCustomer] AS [t10] ON [t10].[CustomerID] = [t6].[ID]
LEFT OUTER JOIN [dbo].[ItemForConsultingCategory] AS [t11] ON [t11].[ID] = [t7].[FirstConsultingItem]
LEFT OUTER JOIN [dbo].[MemberCardInfo] AS [t12] ON [t12].[CustomerID] = [t6].[ID]
LEFT OUTER JOIN [dbo].[CustomerRegion] AS [t13] ON [t13].[ID] = [t6].[RegionID]
LEFT OUTER JOIN [dbo].[Employee] AS [t14] ON [t14].[ID] = [t6].[SceneEmployee]
LEFT OUTER JOIN [dbo].[Employee] AS [t15] ON [t15].[ID] = [t6].[TMKEmployee]
LEFT OUTER JOIN [dbo].[CustomerUnderstandWay] AS [t16] ON [t16].[ID] = [t6].[UnderstandWayID]
ORDER BY [t6].[ROW_NUMBER]',N'@p0 datetime,@p1 datetime,@p2 nvarchar(4000),@p3 int,@p4 varchar(8000),@p5 varchar(8000),@p6 int,@p7 tinyint,@p8 tinyint,@p9 tinyint,@p10 tinyint,@p11 tinyint,@p12 tinyint,@p13 int,@p14 int,@p15 nvarchar(4000),@p16 nvarchar(4000),@p17 nvarchar(4000),@p18 nvarchar(4000),@p19 nvarchar(4000)',@p0='2018-06-01 00:00:00',@p1='2018-06-23 00:00:00',@p2=N'%%',@p3=0,@p4='%%',@p5='%%',@p6=1,@p7=0,@p8=1,@p9=2,@p10=3,@p11=4,@p12=5,@p13=0,@p14=100,@p15=N'',@p16=N'',@p17=N'',@p18=N'',@p19=N''

select * from CustomerBaseInfo where ID=''

select * from MemberCardInfo where MemberCardNum='18050296'

select * from SuccessofCustomer

select * from FinanceTotal where CustomerID='02H9BDMRPD'

update SuccessofCustomer where

select * from FinanceTotal

update SuccessofCustomer set Success=5 where CustomerID in (select CustomerID from FinanceTotal)
select * from SuccessofCustomer where CustomerID in (select CustomerID from FinanceTotal

select distinct(CustomerID) from FinanceTotal;

select * from StatusOfCustomer

select count(*) from CustomerComeLog a inner join StatusofCustomer b on a.CustomerID=b.CustomerID where

select * from CustomerComeLog where count(*)>1

-------------------------------------------

with t
as
(
select CustomerID,count(*) cnt from CustomerComeLog group by CustomerID
)
--select * from StatusOfCustomer where CustomerID not in (select CustomerID from t)
update StatusOfCustomer set Status=0 where CustomerID not in (select CustomerID from t)

with t
as
(
select CustomerID,count(*) cnt from CustomerComeLog group by CustomerID
)
update SuccessOfCustomer set Success=0 where CustomerID not in (select CustomerID from t)

-----------------------------------

with t
as
(
select CustomerID,count(*) cnt from CustomerComeLog group by CustomerID
)
--select * from StatusOfCustomer a inner join t on t.CustomerID=a.CustomerID where cnt=1;
update StatusOfCustomer set Status=3 from StatusOfCustomer a inner join t on t.CustomerID=a.CustomerID where cnt=1;

with t
as
(
select CustomerID,count(*) cnt from CustomerComeLog group by CustomerID
)
update SuccessOfCustomer set Success=1 from SuccessOfCustomer a inner join t on t.CustomerID=a.CustomerID where cnt=1;

---------------------------------------------

with t
as
(
select CustomerID,count(*) cnt from CustomerComeLog group by CustomerID
)
update StatusOfCustomer set Status=4 from StatusOfCustomer a inner join t on t.CustomerID=a.CustomerID where cnt>1;

---------------------------------------------

with t
as
(
select CustomerID,count(*) cnt from FinanceTotal group by CustomerID
)
update StatusOfCustomer set Status=5 from StatusOfCustomer a inner join t on t.CustomerID=a.CustomerID where cnt>0;

with t
as
(
select CustomerID,count(*) cnt from FinanceTotal group by CustomerID
)
update SuccessOfCustomer set Success=2 from SuccessOfCustomer a inner join t on t.CustomerID=a.CustomerID where cnt>0;

---------------------------------------------

select * from FinanceTotal

select * from IntegrationDetails;

select * from CustomerBaseInfo where RegisterDate>='2018-06-22'

select * from AccountsOfCustomer where CustomerID='2A8IQNRVVY'

select * from PrePaymentRecharge where MemberCardNum='2A8IQNRVVY'

update UpdateManage set NEW_VERSION='32'

select * from UpdateManage

update bh20180529.dbo.CustomerAppendInfo set Rating=5 where CustomerID in
( select Now from bh20180529.dbo.CustomerBaseInfoID a inner join YUN_HIS20180622.dbo.H_TMK b on a.Original=b.FID where b.sBJ ='A' )

update bh20180529.dbo.CustomerAppendInfo set Rating=4 where CustomerID in
( select Now from bh20180529.dbo.CustomerBaseInfoID a inner join YUN_HIS20180622.dbo.H_TMK b on a.Original=b.FID where b.sBJ ='B' )

update bh20180529.dbo.CustomerAppendInfo set Rating=3 where CustomerID in
( select Now from bh20180529.dbo.CustomerBaseInfoID a inner join YUN_HIS20180622.dbo.H_TMK b on a.Original=b.FID where b.sBJ ='C' )

update bh20180529.dbo.CustomerAppendInfo set Rating=2 where CustomerID in
( select Now from bh20180529.dbo.CustomerBaseInfoID a inner join YUN_HIS20180622.dbo.H_TMK b on a.Original=b.FID where b.sBJ ='D' )

update bh20180529.dbo.CustomerAppendInfo set Rating=0 where CustomerID in
( select Now from bh20180529.dbo.CustomerBaseInfoID a inner join YUN_HIS20180622.dbo.H_TMK b on a.Original=b.FID where b.sBJ not in ('A','B','C','D') )

update CustomerConsumptionDetails set ExcutePercent=1,ExecuteStatus=2,ChargeStatus=1,CanExecute=0 where ID in (select CertainProjectID from FinanceTotal where DocType='住院或门诊划价单')
select * from FinanceTotal where DocType='住院或门诊划价单'

update CustomerConsumptionDetails set ReturnMoneyStatus=2 where ID in (select CertainProjectID from FinanceTotal where DocType='退项目单据')

update CustomerConsumptionDetails set ReturnMoneyStatus=2,IsDispute=1 where ID in (select CertainProjectID from FinanceTotal where DocType='纠纷退费单据')

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 where tmk.DT>'2018-05-21 20:00:00.000') AS src
UNPIVOT (
PhoneNumber FOR PhoneType IN
(sSJ, sTel, sTel1, sTel2)) AS UNPVT
where ISNULL(PhoneNumber,'') <>''
)
insert into bh20180529.dbo.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 t
as
(
select isnull(a.dgzDT,'2017-01-01') as PlanTrackDate,sFID as cusid, 5 as trackinfoid, bExec as status, LEFT(CONCAT('问题:',sCont,'结果:',sResult),200) as remark,
isnull(a.dSET,'2016-01-01') as redate ,sOP as gzr1,'' as newgzr ,sDept as dptid, '' as positionid,sOP as gzr2 from #temp a
)

insert into bh20180529.dbo.WaitTrackCustomer(PlanTrackDate,CustomerID,TrackInfoID,Status,Remark,RecordBuildDate,Tracker,NewTracker,TrackerDept,PositionID,RegisterBy)
select PlanTrackDate,cusid,trackinfoid,status,remark,redate,gzr1,newgzr,dptid,positionid,gzr2 from t

select * from medicinecategory;

select PhoneNumber from CustomerPhoneNumber group by PhoneNumber having count(PhoneNumber)>1

delete CustomerPhoneNumber where ID in (
select ID from(
select ROW_NUMBER() over (partition by PhoneNumber order by PhoneNumber) rn,ID from CustomerPhoneNumber
) as t
where rn>1 )

select * from bh20180420.dbo.OperationReservation;

select * from bh20180529.dbo.OperationReservation

insert into [bh20180502].[dbo].EducationInfo select * from [bh20180420].[dbo].EducationInfo

select * from bh20180420.dbo.EducationInfo;

select * from bh20180529.dbo.EducationInfo

drop table OperationReservation

select * into OperationReservation
from bh20180420.dbo.OperationReservation

alter table OperationReservation add primary key(id)

delete from OperationReservation

with t
as
(
select ID as id,FLOOR(datediff(DY,BirthDay,getdate())/365.25) as age from CustomerBaseInfo where BirthDay!='' and FLOOR(datediff(DY,BirthDay,getdate())/365.25)>2
)
update CustomerBaseInfo set Age=t.age from CustomerBaseInfo a inner join t on t.id=a.ID;

select * from bh.dbo.UpdateManage

update bh.dbo.UpdateManage set NEW_VERSION='30'

select * from bh.dbo.UpdateManage

show table bh.dbo.UpdateManage

select * from AccountsOfCustomer

update AccountsOfCustomer set IntegrationBalance=IntegrationBalance/20;[bh20180502]

select * from AccountsOfCustomer where CustomerID='A87I7NVPM5'

USE master
GO

ALTER DATABASE bh20180420 SET MULTI_USER WITH ROLLBACK IMMEDIATE

ALTER DATABASE bh20180420 SET READ_WRITE WITH NO_WAIT
GO

EXEC sp_renamedb 'bh20180420','bh20180420-1'

select * from WaitTrackCustomer where RecordBuildDate>'2018-05-01'

select distinct(CustomerStatusID) from FinanceTotal;

select count(*) from FinanceTotal where CustomerStatusID=3;
select count(*) from FinanceTotal where CustomerStatusID=4;
select count(*) from FinanceTotal where CustomerStatusID=5;
select count(*) from FinanceTotal where CustomerStatusID=6;

update FinanceTotal set CustomerStatusID=1

select * from FinanceTotal;

select

exec sp_executesql N'SELECT [t2].[ChargeDate] AS [ChargeDate], [t0].[ID] AS [Cpid], [t4].[MemberCardNum] AS [CustomerMemberCardNum], [t3].[Name] AS [CustomerName], [t0].[ExecuteStatus] AS [cs],
(CASE
WHEN (CONVERT(Int,[t0].[HandselItemModeID])) IS NOT NULL THEN [t5].[Mode]
ELSE CONVERT(NVarChar(20),@p6)
END) AS [HandselMode], [t1].[Name] AS [ItemName], [t1].[Standard] AS [ItemStandard], [t6].[ItemTypeName] AS [ItemType], [t1].[Unit] AS [ItemUnit], [t0].[Quantity], [t0].[Quantity2] AS [Quantity1], [t0].[Remark], [t0].[Version]
FROM [dbo].[CustomerConsumptionDetails] AS [t0]
INNER JOIN [dbo].[ItemInfo] AS [t1] ON [t1].[ID] = [t0].[ItemID]
INNER JOIN [dbo].[CustomerConsumptionInfo] AS [t2] ON [t2].[ExcutionNumber] = [t0].[ExcutionNumber]
INNER JOIN [dbo].[CustomerBaseInfo] AS [t3] ON [t3].[ID] = [t2].[CustomerID]
LEFT OUTER JOIN [dbo].[MemberCardInfo] AS [t4] ON [t4].[CustomerID] = [t3].[ID]
LEFT OUTER JOIN [dbo].[HandselItemMode] AS [t5] ON [t5].[ID] = [t0].[HandselItemModeID]
INNER JOIN [dbo].[ItemTypeInfo] AS [t6] ON [t6].[ID] = [t0].[ItemTypeID]
WHERE ([t0].[CanExecute] = 1) AND ([t0].[ChargeStatus] = @p0) AND (([t1].[MustExcute]) = 1) AND ((
(CASE
WHEN @p1 = 1 THEN
(CASE
WHEN ([t4].[MemberCardNum] IS NOT NULL) AND ([t4].[MemberCardNum] LIKE @p2) THEN 1
WHEN NOT (([t4].[MemberCardNum] IS NOT NULL) AND ([t4].[MemberCardNum] LIKE @p2)) THEN 0
ELSE NULL
END)
WHEN [t4].[MemberCardNum] IS NOT NULL THEN CONVERT(Int,
(CASE
WHEN [t4].[MemberCardNum] LIKE @p3 THEN 1
WHEN NOT ([t4].[MemberCardNum] LIKE @p3) THEN 0
ELSE NULL
END))
ELSE @p4
END)) = 1) AND ([t3].[Name] LIKE @p5) AND ([t2].[ChargeDate] IS NOT NULL)',N'@p0 int,@p1 int,@p2 varchar(8000),@p3 varchar(8000),@p4 int,@p5 nvarchar(4000),@p6 nvarchar(4000)',@p0=1,@p1=0,@p2='%%',@p3='%%',@p4=1,@p5=N'%%',@p6=N''

with t
as
(
select c.Now as cNow, a.sZXGW,b.sName,d.Now as dNow from YUN_HIS20180622.dbo.H_TMK a
inner join YUN_HIS20180622.dbo.H_EMPLOYEE b on b.sEmpNo=a.sZXGW
inner join bh20180529.dbo.EmployeeID c on c.Original=b.sEmpNo
inner join bh20180529.dbo.CustomerBaseInfoID d on d.Original=a.FID
)
update a set SceneEmployee=t.cNow from bh20180529.dbo.CustomerBaseInfo a
inner join t on a.ID=t.dNow

from SuccessOfCustomer a

select * from YUN_HIS20180622.dbo.H_TMK
select * from YUN_HIS20180622.dbo.H_EMPLOYEE

select * from bh20180529.dbo.EmployeeID
select * from bh20180529.dbo.CustomerBaseInfoID
select * from bh20180529.dbo.CustomerBaseInfo where ID='JS3LPV9BH7'

select * from YUN_HIS20180622.dbo.H_Project_Data

select * from bh20180529.dbo.ItemInfo;

update bh20180529.dbo.ItemInfo set Valid=1

select * from bh20180529.dbo.ItemInfoID

select * from YUN_HIS20180622.dbo.H_Project_Data

select * from bh20180529.dbo.CustomerPhoneNumber where PhoneNumber='13962712191'

select * from bh20180529.dbo.CustomerBaseInfo where ID='DVR5MGUZGS'

update bh20180529.dbo.CustomerBaseInfo set TMKEmployee='00154' where ID='DVR5MGUZGS'

select c.Now as cNow, a.sZXGW, b.sName, d.Now as dNow from YUN_HIS20180622.dbo.H_Project_Data a
inner join bh20180529.dbo.ItemInfoID c on c.Original=b.sEmpNo
inner join bh20180529.dbo.CustomerBaseInfoID d on d.Original=a.FID

select * from TrackInfo;

select * from bh20180529.dbo.WaitTrackCustomer where Tracker='00150' and RecordBuildDate>'2017-06-01 00:00:00'

select * from YUN_HIS20180420.dbo.H_TMK_GZ where sOP='1503' and bExec=1 and dHF >'2018-05-01 00:00:00'

select * from YUN_HIS20180420.dbo.H_Employee where sName like '张晶'

select count(*),sOP from YUN_HIS20180420.dbo.H_TMK_GZ group by sOP

select * from YUN_HIS20180420.dbo.H_Employee where sEmpNo='1101'

select * from YUN_HIS20180622.dbo.H_TMK_GZ where sFID='JZ180305016'

select * from YUN_HIS20180521.dbo.H_TMK where FID='JZ180305016'

delete from bh20180529.dbo.WaitTrackCustomer;

select * from bh20180529.dbo.WaitTrackCustomer;

select * from bh20180529.dbo.TrackedRecord

select * from YUN_HIS20180622.dbo.H_TMK_GZ

select * from YUN_HIS20180622.dbo.H_TMK_GZ t where t.FID not in (select FID from YUN_HIS20180420.dbo.H_TMK_GZ)
select * from YUN_HIS20180622.dbo.H_TMK_GZ t where t.sFID ='JZ180305016'
select * from YUN_HIS20180622.dbo.H_TMK_GZ
select count(*) from YUN_HIS20180420.dbo.H_TMK_GZ

drop table #temp
select * into #temp from YUN_HIS20180622.dbo.H_TMK_GZ t where t.FID not in (select FID from YUN_HIS20180420.dbo.H_TMK_GZ)
update #temp set sFID=b.Now from #temp a inner join CustomerBaseInfoID b on a.sFID=b.Original
update #temp set sOP=b.Now from #temp a inner join EmployeeID b on a.sOP=b.Original
update #temp set sDept=b.Now from #temp a inner join DepartmetID b on a.sDept=b.Original

update #temp set sGZOP=b.PositionID from #temp a inner join Employee b on a.sOP=b.ID;

update #temp set sgzProc=b.ID from #temp a inner join Reservation b on a.sFID=b.CustomerID;

delete from #temp where len(sFID)>10
delete from #temp where len(sGZOP)=4
select * from #temp where sOP='00001'
update #temp set sOP='00001' where sOP='';
with t
as
(
--select cast(ISNUMERIC(sgzProc) as bigint) as reserve from #temp a
--select case when isnumeric(isnull(sgzProc,”)) = 0 then 0 else sgzProc end as reserve from #temp a where isnull(sgzProc,”) = ”
select isnull(a.dgzDT,'2017-01-01') as PlanTrackDate,sFID as cusid, 14 as trackinfoid, bExec as status, LEFT(CONCAT('问题:',sCont,'结果:',sResult),200) as remark,isnull(a.dSET,'2016-01-01') as redate ,sOP as gzr1,sOP as newgzr ,'001' as dptid, sGZOP as positionid,sOP as gzr2 from #temp a--,cast(sgzProc as bigint) as reserve
)
--select distinct gzr1 from t where t.gzr1 not in (select ID from Employee);
--select * from t where len(positionid)=4
--select max(len(PlanTrackDate)),max(len(cusid)),max(len(trackinfoid)),max(len(status)),max(len(remark)),max(len(redate)),max(len(gzr1)),max(len(newgzr)),max(len(dptid)),max(len(positionid)),max(len(gzr2)),max(len(reserve)) from t
insert into bh20180529.dbo.WaitTrackCustomer(PlanTrackDate,CustomerID,TrackInfoID,Status,Remark,RecordBuildDate,Tracker,NewTracker,TrackerDept,PositionID,RegisterBy)
select PlanTrackDate,cusid,trackinfoid,status,remark,redate,gzr1,newgzr,dptid,positionid,gzr2 from t

select * from Employee;

select * from TrackInfo;

with t
as(
select a.ID,c.DepartmentID,b.PositionID from WaitTrackCustomer a inner join Employee b on a.Tracker=b.ID
inner join PositionInfo c on c.ID=b.PositionID
)
update WaitTrackCustomer set TrackerDept=t.DepartmentID from WaitTrackCustomer a inner join t on t.ID=a.ID

update

select * from Department;

select * from PositionInfo

select * from WaitTrackCustomer

with t
as(
select convert(char(10),dgzDT,120) as plandate,sFID,c.Now,b.bExec as sstatus from YUN_HIS20180622.dbo.H_TMK_GZ b
inner join bh20180529.dbo.CustomerBaseInfoID c on c.Original=b.sFID
)
--select count(*) from bh20180529.dbo.WaitTrackCustomer a inner join t on a.PlanTrackDate=t.plandate and a.CustomerID=t.Now
update a set Status=sstatus from bh20180529.dbo.WaitTrackCustomer a inner join t on a.PlanTrackDate=t.plandate and a.CustomerID=t.Now

select * from YUN_HIS20180622.dbo.H_TMK_GZ a
inner join bh20180529.dbo.CustomerBaseInfoID c on c.Original=a.sFID
inner join bh20180529.dbo.WaitTrackCustomer b on convert(char(10),a.dgzDT,120)=b.PlanTrackDate and c.Now=b.CustomerID

select * from bh20180529.dbo.CustomerBaseInfoID

exec sp_executesql N'SELECT [t1].[ID], [t1].[Address], [t1].[Age], [t1].[Amount], [t1].[Balance], [t1].[BirthDay], [t1].[ChannelEmployee], [t1].[CIdentity], [t1].[ComeTimes], [t1].[PresentationPeopleID], [t1].[ProfessionID], [t1].[RegionID], [t1].[CustomerServiceEmployee], [t1].[UnderstandWayID], [t1].[DoctorEmployee], [t1].[EducationID], [t1].[Email], [t1].[SceneEmployee], [t1].[TMKEmployee], [t1].[TempEmployee], [t1].[FirstPhone], [t1].[Forzen], [t1].[HasMedical], [t1].[HasSickHistory], [t1].[IntegrationBalance], [t1].[IsCome], [t1].[IsMember], [t1].[LastestOperateEmployee], [t1].[MSN], [t1].[Name], [t1].[NetChannelID], [t1].[OtherInfo], [t1].[PresentationInsideID], [t1].[PresentationMode], [t1].[PresentationOutsideID], [t1].[QQ], [t1].[RankID], [t1].[RegisterDate], [t1].[Remark], [t1].[Remove], [t1].[RowVersion], [t1].[SecondPhone], [t1].[Sex], [t1].[Status], [t1].[Success], [t1].[SuccessTimes], [t1].[ThirdPhone], [t1].[VIP], [t1].[ZipCode], [t0].[ID] AS [ID2], [t4].[Name] AS [LastestConsultingItem], [t2].[MemberCardNum], [t0].[PlanTrackDate],
(CASE
WHEN [t1].[SceneEmployee] IS NOT NULL THEN CONVERT(NVarChar(MAX),[t5].[Name])
ELSE NULL
END) AS [SceneName], [t0].[Remark] AS [Subject],
(CASE
WHEN [t0].[TrackerDept] IS NOT NULL THEN CONVERT(NVarChar(MAX),[t6].[Name])
ELSE NULL
END) AS [TrackDeptName],
(CASE
WHEN [t0].[Tracker] IS NOT NULL THEN CONVERT(NVarChar(MAX),[t7].[Name])
ELSE NULL
END) AS [Tracker], [t8].[TrackName]
FROM [dbo].[WaitTrackCustomer] AS [t0]
INNER JOIN [dbo].[CustomerBaseInfo] AS [t1] ON [t1].[ID] = [t0].[CustomerID]
LEFT OUTER JOIN [dbo].[MemberCardInfo] AS [t2] ON [t2].[CustomerID] = [t1].[ID]
LEFT OUTER JOIN [dbo].[CustomerAppendInfo] AS [t3] ON [t3].[CustomerID] = [t1].[ID]
LEFT OUTER JOIN [dbo].[ItemForConsultingCategory] AS [t4] ON [t4].[ID] = [t3].[LastestConsultingItem]
LEFT OUTER JOIN [dbo].[Employee] AS [t5] ON [t5].[ID] = [t1].[SceneEmployee]
LEFT OUTER JOIN [dbo].[Department] AS [t6] ON [t6].[ID] = [t0].[TrackerDept]
LEFT OUTER JOIN [dbo].[Employee] AS [t7] ON [t7].[ID] = [t0].[Tracker]
INNER JOIN [dbo].[TrackInfo] AS [t8] ON [t8].[ID] = [t0].[TrackInfoID]
WHERE ([t0].[Tracker] = @p0) AND ([t0].[PlanTrackDate] >= @p1) AND ([t0].[PlanTrackDate] < @p2) AND ([t1].[Name] LIKE @p3) AND ((
(CASE
WHEN @p4 = 1 THEN
(CASE
WHEN ([t2].[MemberCardNum] IS NOT NULL) AND ([t2].[MemberCardNum] LIKE @p5) THEN 1
WHEN NOT (([t2].[MemberCardNum] IS NOT NULL) AND ([t2].[MemberCardNum] LIKE @p5)) THEN 0
ELSE NULL
END)
WHEN [t2].[MemberCardNum] IS NOT NULL THEN CONVERT(Int,
(CASE
WHEN [t2].[MemberCardNum] LIKE @p6 THEN 1
WHEN NOT ([t2].[MemberCardNum] LIKE @p6) THEN 0
ELSE NULL
END))
ELSE @p7
END)) = 1) AND (([t0].[Status] = @p8) OR ([t0].[Status] = @p9)) AND (NOT ([t1].[Forzen] = 1))',N'@p0 varchar(8000),@p1 date,@p2 date,@p3 nvarchar(4000),@p4 int,@p5 varchar(8000),@p6 varchar(8000),@p7 int,@p8 int,@p9 int',@p0='00131',@p1='2018-06-23',@p2='2018-06-24',@p3=N'%%',@p4=0,@p5='%%',@p6='%%',@p7=1,@p8=0,@p9=3

select * from