2018-05-09

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

更新确诊单状态

with t
as
(
select b.ExcutionNumber,c.CertainProjectID from FinanceTotal a
inner join CustomerConsumptionDetails b on a.DocInfoCode=b.ExcutionNumber and a.ItemID=b.ItemID
inner join ChargeForCertainDocDetails c on c.ExcutionNumber=a.DocInfoCode
)
update FinanceTotal set CertainProjectID=t.CertainProjectID from FinanceTotal aa inner join t on aa.DocInfoCode=t.ExcutionNumber

更改确诊单号

update FinanceTotal set CertainProjectID=DocCode

更改执行状态

update CustomerConsumptionDetails set ExecuteStatus=1,CanExecute=1 where ExcutePercent<1

增加客服回访类型

insert into bh20180502.dbo.TrackInfo values(9,5,'来院未成交',0,'','',1,'10')
insert into bh20180502.dbo.TrackInfo values(10,5,'皮肤已治疗',0,'','',1,'10')
insert into bh20180502.dbo.TrackInfo values(11,5,'脱毛项目管理',0,'','',1,'10')
insert into bh20180502.dbo.TrackInfo values(12,5,'整形已治疗',0,'','',1,'10')
insert into bh20180502.dbo.TrackInfo values(13,5,'非手术已治疗',0,'','',1,'10')

 

 

with t
as
(
select a.dgzDT as plandate, b.Now as cusid, 5 as trackinfoid, a.bExec as status, CONCAT('问题:',sCont,' 结果:',sResult) as remark,a.dSET as redate ,c.Now as gzr1,'' as newgzr ,e.DepartmentID as dptid,'' as wtcid,e.ID as positionid,c.Now as gzr2,'' as reserve from H_TMK_GZ a
inner join bh20180420.dbo.CustomerBaseInfoID b on b.Original=a.sFID
inner join bh20180420.dbo.EmployeeID c on c.Original=a.sOP
inner join bh20180420.dbo.Employee d on d.ID=c.Now
inner join bh20180420.dbo.PositionInfo e on e.ID=d.PositionID
inner join bh20180420.dbo.Department f on f.ID=e.DepartmentID
)
insert into bh20180420.dbo.WaitTrackCustomer(PlanTrackDate,CustomerID,TrackInfoID,Status,Remark,RecordBuildDate,Tracker,NewTracker,TrackerDept,WtcID,PositionID,RegisterBy,ReservationID)
select plandate,cusid,trackinfoid,status,remark,redate,gzr1,newgzr,dptid,wtcid,positionid,gzr2,reserve from t

 

 

with t
as
(
select isnull(a.dgzDT,'2017-01-01') as plandate, b.Now as cusid, 5 as trackinfoid, a.bExec as status, LEFT(CONCAT('问题:',sCont,' 结果:',sResult),200) as remark,isnull(a.dSET,'2016-01-01') as redate ,c.Now as gzr1,'' as newgzr ,e.DepartmentID as dptid,'' as wtcid,e.ID as positionid,c.Now as gzr2,g.ID as reserve from H_TMK_GZ a
inner join bh20180420.dbo.CustomerBaseInfoID b on b.Original=a.sFID
inner join bh20180420.dbo.EmployeeID c on c.Original=a.sOP
inner join bh20180420.dbo.Employee d on d.ID=c.Now
inner join bh20180420.dbo.PositionInfo e on e.ID=d.PositionID
inner join bh20180420.dbo.Department f on f.ID=e.DepartmentID
inner join bh20180420.dbo.Reservation g on g.CustomerID=b.Now
)
insert into bh20180420.dbo.WaitTrackCustomer(PlanTrackDate,CustomerID,TrackInfoID,Status,Remark,RecordBuildDate,Tracker,NewTracker,TrackerDept,PositionID,RegisterBy,ReservationID)
select plandate,cusid,trackinfoid,status,remark,redate,gzr1,newgzr,dptid,positionid,gzr2,reserve from t

 

select sum(mSKTotal) from H_FrontSK where dDT>'2018-05-01 00:00:00.000' and dDT<'2018-05-04 00:00:00.000'