|
장비상태별로 수량합계 구하는 쿼리인데요.
장비상태 : TMS_EQUIPMENT.EQUIPMENTSTATUS = 0(신규), 1(수리).......
LINE(공정)별로 신규,수리,교체,폐기,가동수,고장수를 두 장비로 분류하여 합계를 구합니다.
궁금한것은 등록일자를 기준으로 해당 년도를 지정하면 월별로 표시하고 싶습니다.
등록일자 Table : TMS_EQUIPMENT.InstalledDate 입니다.(2012-10-04 오전 12:00:00)
SELECT LINE, SUM(L_NEW) AS L_신규, SUM(L_REPARE) AS L_수리, SUM(L_REPLACE) AS L_교체,
SUM(L_MOVE) AS L_이동, SUM(L_DISUSE) AS L_폐기,
SUM(L_NEW + L_REPLACE + L_MOVE + L_DISUSE + L_REPARE) AS L_전체,
SUM(L_NEW + L_REPLACE + L_MOVE) AS L_가동수, SUM(L_DISUSE + L_REPARE) AS L_고장수,
SUM(N_NEW) AS N_신규, SUM(N_REPARE) AS N_수리, SUM(N_REPLACE) AS N_교체,
SUM(N_MOVE) AS N_이동, SUM(N_DISUSE) AS N_폐기,
SUM(N_NEW + N_REPLACE + N_MOVE + N_DISUSE + N_REPARE) AS N_전체,
SUM(N_NEW + N_REPLACE + N_MOVE) AS N_가동수, SUM(N_DISUSE + N_REPARE) AS N_고장수
FROM (SELECT TOP (100) PERCENT TMS_LINE.LineName AS LINE, TMS_LINE.LineOrder AS 공정순서,
SUM(CASE WHEN (tms_equipment.usage = 0 AND TMS_EQUIPMENT.EQUIPMENTSTATUS = 0)
THEN 1 ELSE 0 END) AS L_NEW, SUM(CASE WHEN (tms_equipment.usage = 0 AND
TMS_EQUIPMENT.EQUIPMENTSTATUS = 1) THEN 1 ELSE 0 END) AS L_DISUSE,
SUM(CASE WHEN (tms_equipment.usage = 0 AND TMS_EQUIPMENT.EQUIPMENTSTATUS = 2)
THEN 1 ELSE 0 END) AS L_REPARE, SUM(CASE WHEN (tms_equipment.usage = 0 AND
TMS_EQUIPMENT.EQUIPMENTSTATUS = 3) THEN 1 ELSE 0 END) AS L_REPLACE,
SUM(CASE WHEN (tms_equipment.usage = 0 AND TMS_EQUIPMENT.EQUIPMENTSTATUS = 4)
THEN 1 ELSE 0 END) AS L_MOVE, SUM(CASE WHEN (tms_equipment.usage = 1 AND
TMS_EQUIPMENT.EQUIPMENTSTATUS = 0) THEN 1 ELSE 0 END) AS N_NEW,
SUM(CASE WHEN (tms_equipment.usage = 1 AND TMS_EQUIPMENT.EQUIPMENTSTATUS = 1)
THEN 1 ELSE 0 END) AS N_DISUSE, SUM(CASE WHEN (tms_equipment.usage = 1 AND
TMS_EQUIPMENT.EQUIPMENTSTATUS = 2) THEN 1 ELSE 0 END) AS N_REPARE,
SUM(CASE WHEN (tms_equipment.usage = 1 AND TMS_EQUIPMENT.EQUIPMENTSTATUS = 3)
THEN 1 ELSE 0 END) AS N_REPLACE, SUM(CASE WHEN (tms_equipment.usage = 1 AND
TMS_EQUIPMENT.EQUIPMENTSTATUS = 4) THEN 1 ELSE 0 END) AS N_MOVE
FROM TMS_LINE LEFT OUTER JOIN
TMS_EQUIPMENT ON TMS_EQUIPMENT.LineName = TMS_LINE.LineName
GROUP BY TMS_LINE.LineName, TMS_LINE.LineOrder, TMS_EQUIPMENT.Usage) AS derivedtbl_1
GROUP BY LINE, 공정순서
현재 결과
LINE L_신규 L_수리 L_교체 L_이동 L_폐기 L_전체 ......................................N_고장수
CC 0 2 0 2 0 0 ---------- 1년합계 통계
AA 2 3 0 0 1 0
변경후
LINE 월 L_신규 L_수리 L_교체 L_이동 L_폐기 L_전체 ......................................N_고장수
CC 1 0 1 0 1 0 0
CC 2 0 1 0 0 0 0
CC 12 0 0 0 0 0 0
도움 부탁합니다. |
