2018-07-06

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

--更新项目可执行状态，如果执行次数小于顾客购买的次数

with t
as
(
select a.CertainProjectID as cntid,count(*) as cnt,b.ID as cntid2,b.Quantity as number,b.ExcutePercent ep,b.ExecuteStatus es,b.CanExecute as ce,count(*)/b.Quantity as pct from bh20180529.dbo.ExcuteInfo a
inner join bh20180529.dbo.CustomerConsumptionDetails b on a.CertainProjectID =b.ID
--inner join bh20180529.dbo.CustomerConsumptionInfo c on b.ExcutionNumber=c.ExcutionNumber
group by a.CertainProjectID,b.ID,b.Quantity,b.ExcutePercent,b.ExecuteStatus,b.CanExecute--,c.CustomerID
)
--select * from t where cnt<number-- and ce=0 and es=2 and ep=1
select * from CustomerConsumptionDetails c inner join t on t.cntid=c.ID where t.cnt<t.number
--update c set ExcutePercent=t.cnt/t.number,CanExecute=1,ExecuteStatus=1 from CustomerConsumptionDetails c inner join t on t.cntid=c.ID where t.cnt<t.number