SELECT MODEL_NAME ,

CASE WHEN NUM2=0 THEN 0 ELSE NUM2/NUM1 END FILED1,

CASE WHEN NUM2=0 THEN 1 ELSE NUM2/NUM1 END FILED2

FROM (

SELECT COUNT(A.NUMBER_ID) AS NUM1 ,C.MODEL_NAME,COUNT(B.NUMBER_ID) AS NUM2

FROM(

SELECT NUMBER_ID ,MODEL_ID FROM SAJET.G_IPQC_CONFIG

WHERE ENABLED ='Y'

${if(班别=='白班',

"AND TO_NUMBER(SUBSTR(START_TIME,0,2))>=8 AND TO_NUMBER(SUBSTR(START_TIME,0,2))<20

AND TO_NUMBER(SUBSTR(END_TIME,0,2))>8 AND TO_NUMBER(SUBSTR(END_TIME,0,2))<=20",

"AND (TO_NUMBER(SUBSTR(START_TIME,0,2))>=20 OR TO_NUMBER(SUBSTR(START_TIME,0,2))<8)

AND (TO_NUMBER(SUBSTR(END_TIME,0,2))>20 OR TO_NUMBER(SUBSTR(END_TIME,0,2))<=8)")}

)A

LEFT JOIN SAJET.SYS_FIPQC_CONFIG B ON A.NUMBER_ID=B.NUMBER_ID

AND A.MODEL_ID=B.MODEL_ID AND B.ENABLED='N'

${if(班别=='白班',

"AND B.CREATE_DATE >=TO_DATE('"+开始+ " 08:00:00','YYYY-MM-DD HH24:MI:SS') 

AND B.CREATE_DATE <TO_DATE('"+开始+ " 20:00:00','YYYY-MM-DD HH24:MI:SS')",

"AND B.CREATE_DATE >=TO_DATE('"+开始+ " 20:00:00','YYYY-MM-DD HH24:MI:SS') 

AND B.CREATE_DATE <TO_DATE('"+开始1 +" 08:00:00','YYYY-MM-DD HH24:MI:SS')")}

LEFT JOIN SAJET.SYS_MODEL C ON A.MODEL_ID=C.MODEL_ID

WHERE 1=1

GROUP BY C.MODEL_NAME)

WHERE 1=1

ORDER BY MODEL_NAME 

${if(班别=='白班',

"AND B.CREATE_DATE >=TO_DATE(CONCAT(开始,' 08:00:00'),'YYYY-MM-DD HH24:MI:SS') 

AND B.CREATE_DATE <TO_DATE(CONCAT(开始,' 20:00:00'),'YYYY-MM-DD HH24:MI:SS')",

"AND B.CREATE_DATE >=TO_DATE(CONCAT(开始,' 20:00:00'),'YYYY-MM-DD HH24:MI:SS') 

AND B.CREATE_DATE <TO_DATE(CONCAT(开始,' 08:00:00'),'YYYY-MM-DD HH24:MI:SS')")}

------------