UPDATE SFIS.LINE_TIME SET IS_WORK=1,WORK_TIME=30,REST_TIME=0,ONLINE_CNT=b.ONCNT,OFFLINE_CNT=b.OFFCNT
FROM SFIS.LINE_TIME a,
(SELECT SUBSTRING(CONVERT(VARCHAR,a.CREATE_DATE,120),1,10) WORK_DATE,a.LINE_ID,a.SEGMENT_ID
,MAX(ISNULL(d.INSD_CNT,0)) ONCNT,MAX(ISNULL(d.OUSD_CNT,0)) OFFCNT
FROM SFIS.MFG_TIME a,SFIS.MFG_INFO b,SFIS.WIP_INFO c LEFT OUTER JOIN CPS.MTL_UPH d ON c.MTL_NO=d.MTL_NO
WHERE a.MFG_ID=b.MFG_ID AND b.WIP_NO=c.WIP_NO
GROUP BY SUBSTRING(CONVERT(VARCHAR,a.CREATE_DATE,120),1,10),a.LINE_ID,a.SEGMENT_ID
) b where a.WORK_DATE=b.WORK_DATE AND a.LINE_ID=b.LINE_ID AND a.TIME_ID=b.SEGMENT_ID
FROM SFIS.LINE_TIME a,
(SELECT SUBSTRING(CONVERT(VARCHAR,a.CREATE_DATE,120),1,10) WORK_DATE,a.LINE_ID,a.SEGMENT_ID
,MAX(ISNULL(d.INSD_CNT,0)) ONCNT,MAX(ISNULL(d.OUSD_CNT,0)) OFFCNT
FROM SFIS.MFG_TIME a,SFIS.MFG_INFO b,SFIS.WIP_INFO c LEFT OUTER JOIN CPS.MTL_UPH d ON c.MTL_NO=d.MTL_NO
WHERE a.MFG_ID=b.MFG_ID AND b.WIP_NO=c.WIP_NO
GROUP BY SUBSTRING(CONVERT(VARCHAR,a.CREATE_DATE,120),1,10),a.LINE_ID,a.SEGMENT_ID
) b where a.WORK_DATE=b.WORK_DATE AND a.LINE_ID=b.LINE_ID AND a.TIME_ID=b.SEGMENT_ID