2018-05-10

by 沧海之树 In 工作

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 * into #temp 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

 

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, 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, sGZOP as positionid,sOP as gzr2 from #temp a--,cast(sgzProc as bigint) as reserve
)
--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 bh20180420.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