WITH CTR1 AS(
SELECT COUNT (D.EQUIP_NO) TESTERC FROM EAM_L_EQUIP_LIST D
WHERE 1=1 AND D.EQUIP_TYPE='TESTER' AND D.RELEASED=1 ),
CTR2 AS(
SELECT DBO.Hut() Hut),
CTR3 AS(
SELECT COUNT( DISTINCT ( A.EQUIP_NO)) OPENC
FROM dbo.EAM_H_EQUIP_STATUS A LEFT JOIN EAM_H_DOWN_DISP AS B ON A.SHEET_NO=B.SHEET_NO
LEFT OUTER JOIN dbo.EAM_M_EQUIP_STATUS C ON A.MAIN_STATE = C.MAIN_STATE
LEFT JOIN EAM_L_EQUIP_LIST D on A.EQUIP_NO=D.EQUIP_NO
WHERE 1=1 AND (A.MAIN_STATE='M07' OR A.MAIN_STATE='M09' OR A.MAIN_STATE='M02') AND (A.START_TIME BETWEEN GETDATE()-7 AND GETDATE() OR A.END_TIME BETWEEN GETDATE()-7 AND GETDATE()) AND D.EQUIP_TYPE='TESTER' AND (1=1 OR A.CUST_ID = '')
),
CTR4 AS(
SELECT
Convert(decimal(18,2),cast(SUM(CASE WHEN A.MAIN_STATE='M02' THEN A.EVENT_MINUTES ELSE 0 END) as float)/cast((10080dbo.OpenC(7)) as float)100,4) RUNT,
Convert(decimal(18,2),cast(SUM(CASE WHEN A.MAIN_STATE='M03' THEN A.EVENT_MINUTES ELSE 0 END) as float)/cast((10080dbo.OpenC(7)) as float)100,4) SETUPT,
Convert(decimal(18,2),cast(SUM(CASE WHEN A.MAIN_STATE='M04' THEN A.EVENT_MINUTES ELSE 0 END) as float)/cast((10080dbo.OpenC(7)) as float)100,4) DOWNT,
Convert(decimal(18,2),cast(SUM(CASE WHEN A.MAIN_STATE='M09' THEN A.EVENT_MINUTES ELSE 0 END) as float)/cast((10080dbo.OpenC(7)) as float)100,4) BORROWT,
Convert(decimal(18,2),cast(SUM(CASE WHEN A.MAIN_STATE='M06' THEN A.EVENT_MINUTES ELSE 0 END) as float)/cast((10080dbo.OpenC(7)) as float)100,4) PMT,
Convert(decimal(18,2),cast(SUM(CASE WHEN A.MAIN_STATE='M01' THEN A.EVENT_MINUTES ELSE 0 END) as float)/cast((10080dbo.OpenC(7)) as float)100,4) IDLET,
Convert(decimal(18,2),cast(SUM(CASE WHEN A.MAIN_STATE='M07' THEN A.EVENT_MINUTES ELSE 0 END) as float)/cast((10080dbo.OpenC(7)) as float)100,4) REWORK,
Convert(decimal(18,2),cast(SUM(CASE WHEN A.MAIN_STATE='M0B' THEN A.EVENT_MINUTES ELSE 0 END) as float)/cast((10080dbo.OpenC(7)) as float)100,4) OTHERT,
Convert(decimal(18,2),cast(SUM(CASE WHEN A.MAIN_STATE='M02' THEN A.EVENT_MINUTES ELSE 0 END) as float)/cast((10080dbo.OpenC(7)-cast(SUM(CASE WHEN A.MAIN_STATE='M01' THEN A.EVENT_MINUTES ELSE 0 END) as float)-cast(SUM(CASE WHEN A.MAIN_STATE='M0B' THEN A.EVENT_MINUTES ELSE 0 END) as float)) as float)100,4) XOEE,
Convert(decimal(18,2),cast(SUM(CASE WHEN A.MAIN_STATE='M02' THEN A.EVENT_MINUTES ELSE 0 END) as float)/cast((10080dbo.OpenC(7)) as float)100,4) OEE
FROM
(SELECT DATEPART(yyyy,START_TIME) AS OrderYear,
DATEPART(QQ,START_TIME) AS QQ,
DATEPART(mm,START_TIME) AS OrderMonth,
DATEPART(WEEK,START_TIME) AS OrderWeek
FROM EAM_H_EQUIP_STATUS) AS H,
EAM_H_EQUIP_STATUS A
LEFT JOIN EAM_H_DOWN_DISP AS B ON A.SHEET_NO=B.SHEET_NO
LEFT OUTER JOIN dbo.EAM_M_EQUIP_STATUS C ON A.MAIN_STATE = C.MAIN_STATE
LEFT JOIN EAM_L_EQUIP_LIST D on A.EQUIP_NO=D.EQUIP_NO
WHERE 1=1 AND (A.START_TIME BETWEEN GETDATE()-7 AND GETDATE() OR A.END_TIME BETWEEN GETDATE()-7 AND GETDATE()) AND D.EQUIP_TYPE='TESTER'
GROUP BY DATEPART(yyyy,START_TIME), DATEPART(QQ,START_TIME),DATEPART(mm,START_TIME),DATEPART(WEEK,START_TIME)
) select * from CTR1 A, CTR2 B, CTR3 C ,CTR4 D