데이터베이스 질문과 답변
기존 테이블의 내용과 일부 추가 된 정보를 비교하여 원하는 값을 얻고자 합니다.
WITH Bp (fac_id,fac_name,dtm) AS
(
SELECT '04877', '04877 - GL - ELKHART', '20150818'
UNION ALL SELECT '02046', '02046 - GL - BLOOMINGTON - IN', '20150819'
UNION ALL SELECT '00093', '00093 - GL - BROOKVIEW', '20150923'
UNION ALL SELECT '14400', '14400 - LABELLE MANOR', '20160920'
상기 테이블에서 마지막 항목인 dtm(yyyymmdd)을 기준으로 3개월 이전 값/이후값 들을 Alarm_history테이블에서 찾고자 합니다. 예를 들면,
Fac_id '02046' 의 경우 dtm값이 20150819 임으로 Alarm_history 테이블에서 alarm_dtm이 20150519~20150819 (BEFORE), 20150819~20151119 (AFTER) 값들만 나타내고자 합니다. 각 Fac_id별로 dtm 값이 다름으로 각 dtm과 Alarm_history 테이블에서 alarm_dtm을 비교하여야 합니다.
제가 사용하는 쿼리는 하기와 같습니다. 매뉴얼 작업이 많아(값을 변경할 때마다 WHERE 절 값을 변경해 줘야 합니다) 불편하네요.
SELECT bp.fac_name , alarm_dtm , CASE WHEN DATEDIFF(day,alarm_dtm,dtm) between 2 and 92 THEN 'before' WHEN DATEDIFF(day,DTM,ALARM_DTM) between 2 and 92 THEN 'AFTER' END as status FROM Alarm_history as A jOIN bp ON a.fac_id = bp.fac_id WHERE CASE WHEN DATEDIFF(day,alarm_dtm,dtm) between 2 and 92 THEN 'before' WHEN DATEDIFF(day,DTM,ALARM_DTM) between 2 and 92 THEN 'AFTER' END IS NOT NULL AND alarm_dtm between '20150501' and '20161220'
답변에 미리 감사드립니다.
미리 답변에 감사드립니다.
1. a.alarm_dtm 의 자료형이 뭔가요? 자료형에 따라 조건을 다르게 주세요.
2. a.alarm_dtm = b.dtm 의 경우 어떻게 표시할지? 고민해 보세요.
- before 에 포함시킬지? After 에 포함시킬지? 별도 상태로 표시할지? 제외시킬지? (AND a.alarm_dtm != b.dtm)
SELECT b.fac_id
, b.fac_name
, b.dtm
, a.alarm_dtm
, CASE WHEN a.alarm_dtm < b.dtm THEN 'Before'
WHEN a.alarm_dtm > b.dtm THEN 'After'
END AS status
FROM Alarm_history a
jOIN bp b
ON a.fac_id = b.fac_id
-- 1. alarm_dtm Date Or DateTime --
AND a.alarm_dtm BETWEEN DATEADD(Month, -3, b.dtm)
AND DATEADD(Month, +3, b.dtm)
-- 2. alarm_dtm Varchar(8) --
-- AND a.alarm_dtm BETWEEN CONVERT(VARCHAR, DATEADD(Month, -3, b.dtm), 112)
-- AND CONVERT(VARCHAR, DATEADD(Month, +3, b.dtm), 112)
ORDER BY b.fac_id, a.alarm_dtm
;
마농님, 답변 감사합니다.
1. 자료형은 nvarchar(14) 입니다. 실제 a.alarm_dtm은 nvarchar이고 b.dtm은 yyyymmdd로 입력했습니다.
주신 옵션 1,2 (Datetim or varchar) 모두 같은 결과를 얻을 수 있었습니다.
헌데 조건을 substring(a.alarm_dtm,1,8) between Dateadd(month,-3,dtm)
and Dateadd(month,+3,dtm) 이렇게 변경하니 좀더 많은 데이타가 나타 납니다. 뭐가 잘못 된걸까요?
2. 제외시킵니다.
Where 절이 아닌 From에서 조건을 설정할 수 도 있는건가요? where 절에 사용하면 안되는지요?
1. 시분초까지 저장되어 있어서 그렇습니다.
- 예를들어 '20180202' 까지로 조건을 주면 실제로 '20180202 000000' 까지만 포함되고
- '20180202 000001' ~ '20180202 235959' 까지 하루치 데이터가 누락됩니다.
2. 해당 조건은 where 절에 사용하셔도 되고 On 절에 사용하셔도 됩니다.
SELECT b.fac_id
, b.fac_name
, b.dtm
, a.alarm_dtm
, CASE WHEN a.alarm_dtm < b.dtm THEN 'Before'
WHEN a.alarm_dtm > b.dtm THEN 'After'
END AS status
FROM Alarm_history a
jOIN bp b
ON a.fac_id = b.fac_id
AND a.alarm_dtm >= CONVERT(NVARCHAR, DATEADD(Month, -3, b.dtm) , 112)
AND a.alarm_dtm < CONVERT(NVARCHAR, DATEADD(Month, +3, b.dtm) + 1, 112)
-- AND SUBSTRING(a.alarm_dtm, 1, 8) != b.dtm
WHERE SUBSTRING(a.alarm_dtm, 1, 8) != b.dtm
ORDER BY b.fac_id, a.alarm_dtm
;
친절한 답변 감사합니다. 매번 한수 배웁니다.
추가 질문있습니다.
FROM Alarm_history a
jOIN bp b
ON a.fac_id = b.fac_id
AND a.alarm_dtm > CONVERT(NVARCHAR, DATEADD(Month, -3, b.dtm) , 112)
AND a.alarm_dtm < CONVERT(NVARCHAR, DATEADD(Month, +3, b.dtm) + 1, 112)
-- AND SUBSTRING(a.alarm_dtm, 1, 8) != b.dtm
-- WHERE SUBSTRING(a.alarm_dtm, 1, 8) != b.dtm
ORDER BY b.fac_id, a.alarm_dtm
상기처럼 하면 같은 결과가 도출 되나요? 조건, 크거나 같음에서 같음을 빼면 되지 않나 문의 드립니다.
같음(=)을 넣어야 정각(0분0초)의 자료가 포함됩니다.
) 버튼을 클릭하여 작성 하시면 됩니다.