|
#2
laowan0012023-10-06 08:34
|
--更新数据
update a set a.oee_num=isnull(b.bd_num,0),a.oee_den=isnull(b.bd_total,0),
a.cal_oee=round(case when isnull(b.bd_total,0)=0 then 0.00 else isnull(b.bd_num,0)/b.bd_total*100.0 end,2),
a.oee_coef=isnull(b.bd_coef,1),
a.dis_oee=round(case when isnull(b.bd_total,0)=0 then 0.00 else isnull(b.bd_num,0)/b.bd_total*100.0 end*isnull(b.bd_coef,1),2),
a.modify_time=getdate()
from ky_mcp.dbo.ky_mcp_oee a
left join(
select c.parent_location_id, b.location_id,b.date_code,b.data_type,b.bd_coef,sum(b.bd_num) as bd_num,sum(b.bd_total) as bd_total--,b.bd_cal_data,b.bd_coef,b.bd_dis_data
from ky_bd_tmp_data b with(nolock)
join bs_location c with(nolock) on b.location_id=c.location_id
where b.bd_type='OEE'
group by c.parent_location_id, b.location_id,b.date_code,b.data_type,b.bd_coef
)b on a.wsp_id=b.parent_location_id and a.line_id=b.location_id and a.day_type=b.date_code
declare @wsp_id varchar(50),@tody varchar(10)
declare ky_proc_mcp_oee_cursor_wsp cursor for
select location_id
from bs_location with(nolock)
where parent_location_id='' and location_type_code='area' and is_delete=0
open ky_proc_mcp_oee_cursor_wsp
fetch next from ky_proc_mcp_oee_cursor_wsp into @wsp_id
while @@FETCH_STATUS=0
begin
select @tody = right(dbo.get_shift_date(getdate(),'0',@wsp_id),10) --根据当前时刻判定当前是哪天
update x set x.oee_num=isnull(y.oee_num,0),x.oee_den=isnull(y.oee_den,0),x.cal_oee=isnull(y.cal_oee,0),
x.oee_coef= case when isnull(y.foee,0)=0 then 1 else y.foee end,
x.dis_oee = round(isnull(y.cal_oee,0)*case when isnull(y.foee,0)=0 then 1 else y.foee end,2),
x.modify_time=getdate()
from dbo.ky_mcp_oee x
left join (
select e.parent_location_id,b.location_id,sum(isnull(user_num9,0))as oee_num,sum(isnull(d.time_open,0)*isnull(d.num_hour,0)) as oee_den,
case when sum(isnull(d.time_open,0)*isnull(d.num_hour,0))>0 then round( sum(isnull(user_num9,0))/sum(isnull(d.time_open,0)*isnull(d.num_hour,0)) * 100,2) else 0 end as cal_oee,max(d.foee) as foee
from ky_hch_eqptline d with(nolock)
join(select max(id) as id from ky_hch_eqptline with(nolock) group by eqpt_id) dd on d.id=d.id
join bs_eqpt_sub c with(nolock) on d.eqpt_id= c.eqpt_id
join bs_eqpt_all b with(nolock) on c.sub_eqpt_id = b.eqpt_id
left join ky_hch_daynum a with(nolock) on c.sub_eqpt_id=a.eqpt_id and a.wo_code= @tody
join bs_location e with(nolock) on b.location_id=e.location_id
where b.res1 = '1' and e.parent_location_id=@wsp_id
group by e.parent_location_id, b.location_id
)y on x.wsp_id=y.parent_location_id and x.line_id=y.location_id
where x.day_type = 'TODAY' and x.wsp_id=@wsp_id
fetch next from ky_proc_mcp_oee_cursor_wsp into @wsp_id
end
close ky_proc_mcp_oee_cursor_wsp
deallocate ky_proc_mcp_oee_cursor_wsp
[此贴子已经被作者于2023-10-5 22:22编辑过]
