SELECT a.COMPANY as 公司,a.WORKSHOP as 车间,a.GY as 产品规格,a.工单号,c.工单完成状态,a.STEP as 工段1,a.STEP2 as 工段2,a.STEP_NO as 工段1顺序,b.MACHINE_NO,a.STEP as 操作步骤,case when a.步骤完成状态 = '未完成' and len(d.step2) >0 then '已终止'else a.步骤完成状态 end as 步骤完成状态,d.step2 as 出料step,LAG(a.步骤完成状态,1,0) OVER (ORDER BY a.工单号,a.STEP_NO,a.STEP2 asc) AS 上步状态,CASE when a.STEP = 'Q' THEN a.步骤完成状态 when a.步骤完成状态 = '已终止' then '已终止' WHEN LAG(a.步骤完成状态,1,0) OVER (ORDER BY a.工单号,a.STEP_NO,a.STEP2 asc) = '未完成' then '待上工段完成' ELSE a.步骤完成状态 END AS AAfrom (--工单完成状态SELECT DISTINCT COMPANY,PRO_BNO AS 工单号,WORKSHOP,GY,--CASE WHEN AVG(STATE) = 1 THEN '已完成' ELSE '未完成' END AS 步骤完成状态,CASE WHEN sum(STATE)/count(state) = 1 THEN '已完成' WHEN (sum(STATE)+0.0)/count(state) < 0 THEN '已终止' ELSE '未完成' END AS 步骤完成状态,STEP,STEP2,STEP_NO--STEP_DETAIL AS 操作步骤,FROM JD_BMS_PROBNO_LISTwhere LEN(PRO_BNO)=16 and substring(PRO_BNO,16,1) = 'Z'AND company = '${公司}' AND WORKSHOP = '${车间}'and dbo.settlement_datetime(CREATE_TIME,'year') = '${公司年份}'and dbo.settlement_datetime(CREATE_TIME,'month') = '${公司月份}'GROUP BY COMPANY,PRO_BNO,WORKSHOP,GY,STEP,STEP2,STEP_NO)as aleft join(--匹配设备号SELECT DISTINCT COMPANY,PRO_BNO AS 工单号,WORKSHOP,MACHINE_NO,STEP,STEP2--STEP_DETAIL AS 操作步骤,FROM JD_BMS_PROBNO_LISTwhere LEN(PRO_BNO)=16 and substring(PRO_BNO,16,1) = 'Z'AND company = '${公司}' AND WORKSHOP = '${车间}'and dbo.settlement_datetime(CREATE_TIME,'year') = '${公司年份}'and dbo.settlement_datetime(CREATE_TIME,'month') = '${公司月份}'AND MACHINE_NO IS NOT NULL) as bon CONCAT(a.工单号,a.step2) = CONCAT(b.工单号,b.step2)left join(--工单完成状态SELECT PRO_BNO AS 工单号,--CASE WHEN AVG(STATE) = 1 THEN '已完成' ELSE '未完成' END AS 工单完成状态CASE WHEN sum(STATE)/count(state) = 1 THEN '已完成' WHEN (sum(STATE)+0.0)/count(state) < 0 THEN '已终止' ELSE '未完成' END AS 工单完成状态FROM JD_BMS_PROBNO_LISTwhere LEN(PRO_BNO)=16 and substring(PRO_BNO,16,1) = 'Z'AND company = '${公司}' AND WORKSHOP = '${车间}'and dbo.settlement_datetime(CREATE_TIME,'year') = '${公司年份}'and dbo.settlement_datetime(CREATE_TIME,'month') = '${公司月份}'GROUP BY PRO_BNO) as con a.工单号= c.工单号left join (SELECT distinct pro_bno1 as 工单号,step2 FROM .where pro_bno1 is not null and LEN(pro_bno1)=16 and substring(PRO_BNO,16,1) = 'Z') don concat(a.工单号,a.step2) = concat(d.工单号,d.step2)where 1=1${if(len(完成状态) == 0,"","and c.工单完成状态 = '" + 完成状态 + "'")}${if(len(工单号) == 0,"","and a.工单号 = '" + 工单号 + "'")}order by a.工单号 desc,charindex(a.STEP,'Q,H,L,F,Y,C,G,S'),CAST(REPLACE(a.STEP2,LEFT(a.STEP2,1),'') AS INT) asc
