找回部分丢失的购买和执行记录，加上更新客户基本信息

2018 年 7 月 11 日 by 沧海之树 In 工作

--更改新库的名字和性别跟老库相同

select a.sName,b.Name from YUN_HIS20180709.dbo.H_TMK a inner join bh20180529.dbo.CustomerBaseInfo b on a.FID=b.OtherInfo
where a.sName!=b.Name

update b set b.Name=a.sName from bh20180529.dbo.CustomerBaseInfo b inner join YUN_HIS20180709.dbo.H_TMK a on a.FID=b.OtherInfo and a.sName!=b.Name

select * from bh20180529.dbo.CustomerBaseInfo b inner join YUN_HIS20180709.dbo.H_TMK a on a.FID=b.OtherInfo and a.sName!=b.Name

select * from bh20180529.dbo.CustomerBaseInfo

select * from YUN_HIS20180709.dbo.H_TMK

update b set b.Name=a.sName from bh20180529.dbo.CustomerBaseInfo b inner join YUN_HIS20180709.dbo.H_TMK a on a.FID=b.OtherInfo and a.sName!=b.Name

select * from bh20180529.dbo.CustomerBaseInfo b inner join YUN_HIS20180709.dbo.H_TMK a on a.FID=b.OtherInfo and a.sSex!=b.Sex

select a.sName,a.sSex, b.Sex from YUN_HIS20180709.dbo.H_TMK a inner join bh20180529.dbo.CustomerBaseInfo b on a.FID=b.OtherInfo
where a.sSex!=b.Sex and a.sSex='男'

--为了导出执行数据

with t
as
(
select a.* from YUN_HIS20180709.dbo.H_FrontSK_Project_Execute a inner join
YUN_HIS20180709.dbo.H_FrontSK_Project2 b on a.iSrcID=b.Keyid and a.sFID=b.FID
)
select * into YUN_HIS20180709.dbo.H_FrontSK_Project_Execute2 from t where 1=1

with t
as
(
select a.* from YUN_HIS20180709.dbo.H_FrontSK_Project_Execute_D a inner join
YUN_HIS20180709.dbo.H_FrontSK_Project_Execute2 b on a.sKeyID=b.keyid
)
select * into YUN_HIS20180709.dbo.H_FrontSK_Project_Execute_D2 from t where 1=1

select * from H_TMK_GZ where sFID='XC171105013'
select * from YUN_HIS2015092016031703.dbo.H_TMK_GZ

--判断是否回访过
select cast(case when dHF is null then 0 else 1 end as bit) as bHF ,* from H_TMK_GZ where sFID='XC161211017' order by FID desc

select * from bh20180529.dbo.EmployeeID where Original not in (select sEmpNo from YUN_HIS20180707.dbo.H_Employee)

select * from bh20180529.dbo.Employee where ID='00156'

select * from YUN_HIS20180707.dbo.H_Employee where sEmpNo not in (select Original from bh20180529.dbo.EmployeeID)

select * from YUN_HIS20180707.dbo.H_DeptSet where FID='001015001'

select * from bh20180529.dbo.EmployeeID

insert into bh20180529.dbo.EmployeeID values('1853','00447')

with t
as
(
select c.Now as cNow, a.sZXGW,b.sName,d.Now as dNow from YUN_HIS20180707.dbo.H_TMK a
inner join YUN_HIS20180707.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

select * from YUN_HIS20180707.dbo.H_TMK where djr

select * from YUN_HIS20180707.dbo.H_TMK where sSJ='18906298678'

select count(*),SceneEmployee,b.Name from bh20180529.dbo.CustomerBaseInfo a
inner join bh20180529.dbo.Employee b on a.SceneEmployee=b.ID
group by a.SceneEmployee,b.Name order by count(*) DESC

select count(*),a.sZXGW,b.sName from YUN_HIS20180707.dbo.H_TMK a
inner join YUN_HIS20180707.dbo.H_Employee b on a.sZXGW=b.sEmpNo
group by a.sZXGW,b.sName order by count(*) DESC

select * from YUN_HIS20180707.dbo.H_TMK where sZXGW='1001'
select * from H_Employee

select a.TMKEmployee,count(*),b.Name from bh20180529.dbo.CustomerBaseInfo a
inner join bh20180529.dbo.Employee b on a.TMKEmployee=b.ID
group by a.TMKEmployee,b.Name order by count(*) DESC

select count(*),sRegOP from YUN_HIS20180707.dbo.H_TMK group by sRegOP,sName order by count(*) DESC

select a.sRegOP,b.sName,count(*) from YUN_HIS20180707.dbo.H_TMK a inner join YUN_HIS20180707.dbo.H_Employee b on a.sRegOP=b.sEmpNo
group by a.sRegOP,b.sName order by count(*) Desc

with t
as
(
select c.Now as cNow, a.sRegOP,b.sName,d.Now as dNow from YUN_HIS20180709.dbo.H_TMK a
inner join YUN_HIS20180709.dbo.H_EMPLOYEE b on b.sEmpNo=a.sRegOP
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 TMKEmployee=t.cNow from bh20180529.dbo.CustomerBaseInfo a
inner join t on a.ID=t.dNow

select * from YUN_HIS20180707.dbo.H_Employee

select * from ItemInfo where IsTreatment=1 and TimesOfOneTreatment is null

select * from CustomerPhoneNumber where CustomerID not in (select ID from CustomerBaseInfo)

select * from CustomerBaseInfo where ID not in (select CustomerID from CustomerPhoneNumber) and (SecondPhone is not NULL or FirstPhone is not null)

select * from MemberCardHandle where CustomerID='YAZ71RJHI3'

select * from CustomerPhoneNumber where CustomerID='YAZ71RJHI3'

select * from MemberCardHandle where MemberCardNum='50006310'
48NXJ8N4MZ
update MemberCardHandle set CustomerID='48NXJ8N4MZ' where MemberCardNum='50006310'

select * from CustomerPhoneNumber where CustomerID='YAZ71RJHI3'

select * from MemberCardHandle where MemberCardNum='50006310'

select * from CustomerPhoneNumber where CustomerID='956BHH3UWJ'

select * from PositionInfo

select * from Role where Name like '%管%'

select * from InvoiceMaked

select * from WaitTrackCustomer

select * from CustomerBaseINfo where Name like '%许燕%'

select * from MemberCardHandle where MemberCardNum='18061015'

select * from CustomerPhoneNumber where CustomerID='ABO6QHN9A2'

select * from CustomerBaseInfo where ID='ABO6QHN9A2'

select * from CustomerConsumptionInfo where CustomerID='ABO6QHN9A2'

select * from YUN_HIS20180709.dbo.H_FrontSK_Project where stype='项目' and fNum>0 and dDT >='2010-01-20' and dDT <='2018-06-23' and Keyid not in (select Original　from bh20180529.dbo.ReturnMoneyForCertainDocID)--select *　from bh20180529.dbo.ReturnMoneyForCertainDocID

select *　from bh20180529.dbo.CustomerConsumptionDetailsID where Original not in (select Keyid from YUN_HIS20180709.dbo.H_FrontSK_Project where dDT >='2018-06-10' and dDT <='2018-06-11')

select * into YUN_HIS20180709.dbo.H_FrontSK_Project2 from YUN_HIS20180709.dbo.H_FrontSK_Project where stype='项目' and fNum>0 and dDT >='2010-01-20' and dDT <='2018-06-23' and Keyid not in (select Original　from bh20180529.dbo.CustomerConsumptionDetailsID)

select * from YUN_HIS20180709.dbo.H_FrontSK where dDT >='2018-04-20' and dDT <='2018-06-23' and FID='SK180610046'

select * from YUN_HIS20180709.dbo.H_FrontSK_Project where dDT >='2018-04-20' and dDT <='2018-06-23'

select * from YUN_HIS20180709.dbo.H_FrontSK_Project2 where dDT>'2018-04-20'

select * into YUN_HIS20180709.dbo.H_FrontSK2 from YUN_HIS20180709.dbo.H_FrontSK where FID in (select FID from YUN_HIS20180709.dbo.H_FrontSK_Project2) --where dDT>'2018-04-20')

select * into YUN_HIS20180709.dbo.H_FrontSK2 from YUN_HIS20180709.dbo.H_FrontSK where FID in (select FID from YUN_HIS20180709.dbo.H_FrontSK_Project2)

select * from YUN_HIS20180709.dbo.H_FrontSK2 where sCID in (select MemberCardNum from bh20180529.dbo.MemberCardHandle) --(select CardNO from YUN_HIS20180709.dbo.H_TMK )

SELECT [t0].[FID], [t0].[ZYFID], [t0].[sCID], [t0].[dDT], [t0].[sName], [t0].[sSFZH], [t0].[sHYType], [t0].[sSex], [t0].[iAge], [t0].[sTel], [t0].[sSJH], [t0].[sKHGW], [t0].[sKDKS], [t0].[sKDR], [t0].[fXMZK], [t0].[fYPZK], [t0].[fZK], [t0].[sSort], [t0].[bStau], [t0].[sOP], [t0].[bZS], [t0].[mLT], [t0].[mTotal], [t0].[museJF], [t0].[mYSTotal], [t0].[mYHtotal], [t0].[mSpacTotal], [t0].[mJFTotal], [t0].[mSYJF], [t0].[mSKTotal], [t0].[mTKTotal], [t0].[mBTotal], [t0].[mBFactTotal], [t0].[mBJFTotal], [t0].[mBFactJFTotal], [t0].[tBRemark], [t0].[bBSort], [t0].[dBDT], [t0].[bJFStau], [t0].[bOutFlag], [t0].[binStau], [t0].[sAccRemark], [t0].[sPaydt], [t0].[sPayID], [t0].[bLPStau], [t0].[bAccStau], [t0].[sRegOP], [t0].[ypSendStau], [t0].[ypSendStauOP], [t0].[ypSendStauDT], [t0].[bFP], [t0].[FPNo], [t0].[sCFID], [t0].[sDataType], [t0].[bZSFlag], [t0].[sTCName], [t0].[pKC], [t0].[mKCMoney], [t0].[sSFLB], [t0].[backmoney], [t0].[diffmoney], [t0].[QKFID], [t0].[export], [t0].[sMFFID], [t0].[sMF], [t0].[bFlag], [t0].[QKACCDT], [t0].[ZYACCDT], [t0].[TCFID], [t0].[TCName], [t0].[TCType], [t0].[TCiNum], [t0].[TCMoney], [t0].[TCFZK], [t0].[dEndDT], [t0].[TCProject], [t0].[sDeptOP], [t0].[mJFNew]
FROM [dbo].[H_FrontSK2] AS [t0]
WHERE ([t0].[sAccRemark] <> '前台挂账') AND ([t0].[sSFLB] = '收费')

select * from YUN_HIS20180709.dbo.H_TMK where CardNO='80026089'
select * from MemberCardHandle where MemberCardNum='80026089'

select * from CustomerConsumptionInfoID
select * from CustomerConsumptionInfoID where Original in ()

select * from YUN_HIS20180709.dbo.H_FrontSK_Project2 where FID in (select Original from CustomerConsumptionInfoID)

select * from YUN_HIS20180709.dbo.H_FrontSK2;

select * into CustomerConsumptionInfo2 from CustomerConsumptionInfo where ExcutionNumber in (select Now from CustomerConsumptionInfoID where Original in (select FID from YUN_HIS20180709.dbo.H_FrontSK_Project2)) order by Date

select * from CustomerConsumptionInfoID
select * from CustomerConsumptionInfo

select top 100 * from YUN_HIS20180709.dbo.H_FrontSK_Project2

select top 100 * from YUN_HIS20180709.dbo.H_FrontSK_Project_Execute_D;

select top 100 * from YUN_HIS20180709.dbo.H_FrontSK_Project_Execute;
select top 100 * from YUN_HIS20180709.dbo.H_FrontSK_Project_Execute_D

select * from YUN_HIS20180709.dbo.H_FrontSK_Project_Execute where sFID in (select FID from YUN_HIS20180709.dbo.H_FrontSK_Project2)

drop table CustomerConsumptionInfo2

select * from YUN_HIS20180709.dbo.H_FrontSK_Project2
select * from CustomerConsumptionDetails

select * from YUN_HIS20180709.dbo.H_FrontSK2

select * from YUN_HIS20180709.dbo.H_FrontSK_Project_Execute_D

select * from ExcuteInfo

='180610085715573431'

exec sp_executesql N'SELECT [t0].[AllTotal], [t0].[ArrearsAmount], [t0].[BigCustomerEmployee], [t0].[BigCustomerEmployeeName], [t0].[Came], [t0].[CardNameID], [t0].[CashAmount], [t0].[CashOfMoneyCardAmount], [t0].[CertainProjectID], [t0].[ChargeBy], [t0].[ChargeByName], [t0].[ChargeOrReturn], [t0].[CustomerID], [t0].[CustomerStatus], [t0].[CustomerStatusID], [t0].[Date], [t0].[DocCode], [t0].[DocHandleBy], [t0].[DocHandleByName], [t0].[DocHandleDeptID], [t0].[DocHandleDeptName], [t0].[DocInfoCode], [t0].[DocType], [t0].[SceneEmployee], [t0].[TMKEmployee], [t0].[TempEmployee], [t0].[ID], [t0].[IncomeTypeID], [t0].[GoodsID], [t0].[HandselOfMoneyCardAmount], [t0].[InsteadMoneyAmount], [t0].[IntegrationAmount], [t0].[ItemID], [t0].[LeadersID], [t0].[MedicineID], [t0].[MHDetailsID], [t0].[MoneyCardPercent], [t0].[MoneyCardRealAmount], [t0].[PrePaymentAmount], [t0].[Quantity], [t0].[Quantity2], [t0].[ReturnCertainID], [t0].[ReturnMHID], [t0].[SceneEmployeeName], [t0].[TempEmployeeName], [t0].[TMKDeptID], [t0].[TMKDeptName], [t0].[TMKEmployeeName], [t0].[UnitPrice], [t0].[YearCardAmount]
FROM [dbo].[FinanceTotal] AS [t0]
WHERE [t0].[CustomerID] = @p0
ORDER BY [t0].[Date] DESC',N'@p0 varchar(8000)',@p0='ABO6QHN9A2'

select a.sName,b.Name from YUN_HIS20180709.dbo.H_TMK a inner join bh20180529.dbo.CustomerBaseInfo b on a.FID=b.OtherInfo
where a.sName!=b.Name

update b set b.Name=a.sName from bh20180529.dbo.CustomerBaseInfo b inner join YUN_HIS20180709.dbo.H_TMK a on a.FID=b.OtherInfo and a.sName!=b.Name

select * from bh20180529.dbo.CustomerBaseInfo b inner join YUN_HIS20180709.dbo.H_TMK a on a.FID=b.OtherInfo and a.sName!=b.Name

select * from bh20180529.dbo.CustomerBaseInfo

select * from YUN_HIS20180709.dbo.H_TMK

update b set b.Name=a.sName from bh20180529.dbo.CustomerBaseInfo b inner join YUN_HIS20180709.dbo.H_TMK a on a.FID=b.OtherInfo and a.sName!=b.Name

select * from bh20180529.dbo.CustomerBaseInfo b inner join YUN_HIS20180709.dbo.H_TMK a on a.FID=b.OtherInfo and a.sSex!=b.Sex

select a.sName,a.sSex, b.Sex from YUN_HIS20180709.dbo.H_TMK a inner join bh20180529.dbo.CustomerBaseInfo b on a.FID=b.OtherInfo
where a.sSex!=b.Sex and a.sSex='男'

select * from GoodsInfo where Name like '%假体%'

update GoodsInfo set OutType='1' where Name like '%假体%'

update GoodsInfo set OutType='1' where SellingPrice<0.5