안녕하세요? 미국에서 근무하면서 본 사이트에서 많은 공부를 하고 있습니다. 항상 답변을 해 주시는 모습에 용기를 내여 질문을 올려 봅니다.
[질문]
1. 원본 테이블에는 alarm_num가 두가지 존재 합니다. 214 번과 210 번 입니다.
[원하는 사항]
2. Fac_id 별로, dev_id2 가 같은 레코드의 최초 214번이 뜬 날짜와 최초 210번이 뜬 날짜의 차이(시간 포함)를 알고 싶습니다.
[원본]
fac_id | YYYYMMDDMMSS | Alarm_num | Dev_id2 |
03678 | 201601021200 | 214 | 188 |
03678 | 201601051300 | 210 | 188 |
03678 | 201601041300 | 214 | 188 |
03678 | 201602221200 | 210 | 188 |
06789 | 201601011200 | 214 | 100 |
06789 | 201601050200 | 214 | 100 |
06789 | 201601091300 | 210 | 100 |
06789 | 201601091200 | 214 | 100 |
06789 | 201601121200 | 210 | 100 |
[원하는 값]
fac_id | Dev_id2 | First date of 214 | First date of 210 | Diff Day | Diff Hour |
03678 | 188 | 201601021200 | 201601051300 | 3 | 1 |
06789 | 100 | 201601011200 | 201601091300 | 8 | 1 |
부탁 드리겠습니다.
감사합니다.
일단 문자형이라고 가정하고 다음과 같이...
날짜형이라면 복잡한 변환과정이 필요 없겠죠.
WITH t(fac_id, YYYYMMDDMMSS, Alarm_num, Dev_id2) AS ( SELECT '03678', '201601021200', 214, 188 UNION ALL SELECT '03678', '201601051300', 210, 188 UNION ALL SELECT '03678', '201601041300', 214, 188 UNION ALL SELECT '03678', '201602221200', 210, 188 UNION ALL SELECT '06789', '201601011200', 214, 100 UNION ALL SELECT '06789', '201601050200', 214, 100 UNION ALL SELECT '06789', '201601091300', 210, 100 UNION ALL SELECT '06789', '201601091200', 214, 100 UNION ALL SELECT '06789', '201601121200', 210, 100 ) SELECT fac_id, Dev_id2 , s "First date of 214" , e "First date of 210" , DATEDIFF(DAY , st, et) "Diff Day" , DATEDIFF(HOUR, st, et)%24 "Diff Hour" FROM (SELECT fac_id, Dev_id2 , s, e , CAST( SUBSTRING(s, 1, 8) + ' ' + SUBSTRING(s, 9, 2) + ':' + SUBSTRING(s, 11, 2) AS DATETIME) st , CAST( SUBSTRING(e, 1, 8) + ' ' + SUBSTRING(e, 9, 2) + ':' + SUBSTRING(e, 11, 2) AS DATETIME) et FROM (SELECT fac_id, Dev_id2 , MIN(CASE Alarm_num WHEN 214 THEN YYYYMMDDMMSS END) s , MIN(CASE Alarm_num WHEN 210 THEN YYYYMMDDMMSS END) e FROM t GROUP BY fac_id, Dev_id2 ) a ) a ORDER BY fac_id, Dev_id2 ;