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 AA

from (--工单完成状态

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_LIST

where 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 a

left join

(--匹配设备号

SELECT DISTINCT COMPANY,PRO_BNO AS 工单号,WORKSHOP,MACHINE_NO,

STEP,STEP2

--STEP_DETAIL AS 操作步骤,

FROM JD_BMS_PROBNO_LIST

where 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 b

on 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_LIST

where 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 c

on a.工单号= c.工单号

left join 

(SELECT distinct pro_bno1 as 工单号,step2 FROM [dbo].[JD_BMS_OUT_LIST]

where pro_bno1 is not null and LEN(pro_bno1)=16 and substring(PRO_BNO,16,1) = 'Z'

) d

on 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