筛选出对应工厂最高型号数据并计算工时和重新集成
需求：
1.P1、P2、P3、P6、P7工厂最高版本订单
2.区分是否为光电板或者是否为槽孔
3.钻孔工时的计算时间为2020年6月6号前（不包含6月6号）
SQL:
drop table #temp2
drop table #temp3
drop table #temp4
declare @factory varchar(16)
set @factory='P1'
select Id into #temp2 from V_PPEFlow as a where a.TechNo='MD_02' and factory=@factory
-- and exists (select * from #temp1 as b where b.pdctno=a.PdctNo )
select pdctno,result,case when LEN(pdctno)=10 then SUBSTRING(pdctno,1,8) else SUBSTRING(pdctno,1,9) end minpdctno
into #temp3
FROM [FP_EMS_DB].[dbo].[Log_Task_Time_Expression] as a where exists(
select * from #temp2 as b where b.Id=a.flow_id
) and indate>'2020-06-06' and factory=@factory
select MAX(pdctno) maxpdctno into #temp4 from #temp3 group by minpdctno
select *
into #temp5
from #temp3 as a where exists(
select * from #temp4 as b where a.pdctno=b.maxpdctno
)
and exists(
select * from p2.[fp_db].[dbo].[ppeMakeInfo] as d where a.pdctno = d.pdctno
)
--select * from #temp5
drop table #temp3
drop table #temp4
declare @factory varchar(16)
set @factory='P1'
select Id into #temp2 from V_PPEFlow as a where a.TechNo='MD_02' and factory=@factory
-- and exists (select * from #temp1 as b where b.pdctno=a.PdctNo )
select pdctno,result,case when LEN(pdctno)=10 then SUBSTRING(pdctno,1,8) else SUBSTRING(pdctno,1,9) end minpdctno
into #temp3
FROM [FP_EMS_DB].[dbo].[Log_Task_Time_Expression] as a where exists(
select * from #temp2 as b where b.Id=a.flow_id
) and indate>'2020-06-06' and factory=@factory
select MAX(pdctno) maxpdctno into #temp4 from #temp3 group by minpdctno
select *
into #temp5
from #temp3 as a where exists(
select * from #temp4 as b where a.pdctno=b.maxpdctno
)
and exists(
select * from p2.[fp_db].[dbo].[ppeMakeInfo] as d where a.pdctno = d.pdctno
)
--select * from #temp5
结果：
