2018-05-04

更新次数

with t
as
(
select CustomerID as id,count(*) as cometimes from bh20180502.dbo.CustomerComeLog group by CustomerID
)
update CustomerBaseInfo set ComeTimes=t.cometimes from CustomerBaseInfo inner join t on CustomerBaseInfo.ID=t.id

with t
as
(
select CustomerID as id,count(*) as cometimes from bh20180502.dbo.CustomerComeLog group by CustomerID
)
update CustomerBaseInfo set ComeTimes=t.cometimes from CustomerBaseInfo inner join t on CustomerBaseInfo.ID=t.id

with t
as
(
select CustomerID as id,max(Date) as date from bh20180502.dbo.CustomerComeLog group by CustomerID
)
update CustomerAppendInfo set LastestComeDate=t.date from CustomerAppendInfo inner join t on CustomerAppendInfo.CustomerID=t.id

with t
as
(
select CustomerID as id,min(Date) as date from bh20180502.dbo.CustomerComeLog group by CustomerID
)
update CustomerAppendInfo set FirstComeDate=t.date from CustomerAppendInfo inner join t on CustomerAppendInfo.CustomerID=t.id

insert into [bh20180420].[dbo].[ReservationType] select * from [bh].[dbo].[ReservationType]

insert into Reservation(CustomerID,Date,ReservationComeDate,ReservationTypeID,ReservationContent,Status,HandleBy,DeptID,Tracked)
select d.Now,bYYDT,dDT,0,sYYPro,0,b.Now,e.DepartmentID,0 from YUN_HIS20180420.dbo.H_YYCenter a
inner join EmployeeID b on a.sYYOP=b.Original
inner join Employee c on c.ID=b.Now
inner join CustomerBaseInfoID d on d.Original=a.FID
inner join PositionInfo e on e.ID=c.PositionID
where len(sYYPro)>0 and len(sYYOP)>0 and len(dDT)>0 and len(bYYDT)>0

with t
as(
select sNo, sSpec, b.Now from YUN_HIS20180420.dbo.H_Drugs_Index_data a
inner join medicineinfoid b on a.sNo=b.Original
)
update medicineinfo set standard=t.sSpec
from medicineinfo a inner join t on a.code=t.Now

with t
as(
select KID, sSpec, b.Now from YUN_HIS20180420.dbo.H_WZProject_data a
inner join GoodsInfoID b on a.KID=b.Original
)
update GoodsInfo set standard=t.sSpec
from GoodsInfo a inner join t on a.code=t.Now