2017-07-09-重新导入追踪回访数据的SQL
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_HIS20180707.dbo.H_TMK a
inner join YUN_HIS20180707.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 * into TrackedRecord1 from TrackedRecord where 1=1
select * into WaitTrackCustomer1 from WaitTrackCustomer where 1=1
1853
6002
select * from H_Employee where sEmpNo='1853' or sEmpNo='6002'
--delete from TrackedRecord
--delete from WaitTrackCustomer
select * from WaitTrackCustomer
select * from WaitTrackCustomer1
--DBCC CHECKIDENT('WaitTrackCustomer', RESEED, 1)
DBCC CHECKIDENT('TrackedRecord', RESEED, 1)
drop table #temp
--select sGZOP,sOP from YUN_HIS20180707.dbo.H_TMK_GZ
select * from Department
select * from YUN_HIS20180707.dbo.H_TMK_GZ a where a.sOP not in (select Original from EmployeeID)
select * from YUN_HIS20180709.dbo.H_Employee where sEmpNo='1018'
select * from EmployeeID where Original='1018'
select * into #temp from YUN_HIS20180709.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;
select * from PositionInfo
update #temp set sgzProc=b.ID from #temp a inner join Reservation b on a.sFID=b.CustomerID;
--select * from temp
select * from YUN_HIS20180707.dbo.H_TMK_GZ
insert into
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='';
--delete from #temp where len(sGZOP)>2
update #temp set sOP='00001' where sOP='6002'
select * from #temp where len(sGZOP)>2
select * from WaitTrackCustomer
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, case when a.dHF is null then 0 else 1 end 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 * from Employee
--select distinct(gzr1) from t where gzr1 not in (select ID from Employee)
--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)) 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
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
select * from WaitTrackCustomer where PlanTrackDate=''
EXEC sp_rename 'customers','custs'
select * from YUN_HIS20180521.dbo.H_Employee where sEmpNo='1853' or sEmpNo='6002'
select * from YUN_HIS20180707.dbo.
select * from bh20180529.dbo.AccountsOfCustomer where IntegrationBalance<0
select top 100 * from WaitTrackCustomer
s
select count(*),TrackInfoID from WaitTrackCustomer1 group by TrackInfoID
select * from TrackedRecord1
select max(ID) from WaitTrackCustomer
drop table #temp
with t
as
(
select 1620385 as wtcid,isnull(b.dHF,'2017-01-01') as HFSJ,b.sCont as HFNR,d.Now as tracker,'001' as deptid,'电话' as tool,1 as valid,convert(char(10),dgzDT,120) as plandate,sFID,b.bExec as sstatus,c.Now as CustID from YUN_HIS20180709.dbo.H_TMK_GZ b
inner join bh20180529.dbo.CustomerBaseInfoID c on c.Original=b.sFID
inner join bh20180529.dbo.EmployeeID d on d.Original=b.sOP
where b.dHF is not null
)
select * into #temp from t
select * from #temp
update #temp set HFNR='' where HFNR is null
update a set wtcid=b.ID from #temp a
inner join bh20180529.dbo.WaitTrackCustomer b on a.plandate=b.PlanTrackDate and a.CustID=b.CustomerID
insert into bh20180529.dbo.TrackedRecord(WtcID,TrackedDate,TrackedContent,Traker,TrackDeptID,TrackedTool,Valid) select wtcid,HFSJ,HFNR,tracker,deptid,tool,valid from #temp
select top 100 wtcid,HFSJ,HFNR,tracker,deptid,tool,valid from #temp
select top 100 * from TrackedRecord
delete from TrackedRecord
DBCC CHECKIDENT('TrackedRecord', RESEED, 1)
select * from TrackedRecord where Traker=’00140’
update a set TrackDeptID=c.DepartmentID from bh20180529.dbo.TrackedRecord a
inner join bh20180529.dbo.Employee b on b.ID=a.Traker
inner join bh20180529.dbo.PositionInfo c on c.ID=b.PositionID
select * from MemberCardHandle where MemberCardNum='17110508'
select * from CustomerBaseInfoID where Now='LE82CB5PKV'
select * from H_TMK_GZ where sFID='XC171105013'
select * from WaitTrackCustomer
select * from Employee
select * from PositionInfo
select * from Department
select * from Department
update UpdateManage set NEW_VERSION='59'
select * from UpdateManage
select * from CustomerBaseInfo where Name='何招菊'
select * from CustomerConsumptionInfo where CustomerID='WRGLIHPUTG'
select * from CustomerConsumptionDetails
select * from MemberCardHandle where MemberCardNum='18060103'
select * from CustomerPhoneNumber where CustomerID='Q2J0H59UJR'
select * from CustomerPhoneNumber where CustomerID='ZO5MRR2RIU'
select * from YUN_HIS20180709.dbo.H_TMK where CardNO='18060103'
select * from CustomerBaseINfo where FirstPhone='13382362861'
select * from MemberCardHandle where CustomerID='Q2J0H59UJR'
select * from MemberCardHandle where CustomerID='ZO5MRR2RIU'
select * from CustomerBaseInfoID where Now='ZO5MRR2RIU'
delete from CustomerBaseInfo where
update CustomerPhoneNumber set CustomerID='ZO5MRR2RIU' where CustomerID='Q2J0H59UJR'