항목별 다른 날짜를 기준으로 3개월 전 후 데이타 만 찾기 0 6 1,415

by 갈매기 [SQL Query] [2018.03.06 01:42:07]


기존 테이블의 내용과 일부 추가 된 정보를 비교하여 원하는 값을 얻고자 합니다. 

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'

 

답변에 미리 감사드립니다. 

by 갈매기 [2018.03.06 01:53:00]

미리 답변에 감사드립니다. 


by 마농 [2018.03.06 09:02:06]

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
;

 


by 갈매기 [2018.03.07 23:26:04]

마농님, 답변 감사합니다.

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 절에 사용하면 안되는지요? 

 

 


by 마농 [2018.03.08 08:25:51]

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
;

 


by 갈매기 [2018.03.12 23:15:57]

친절한 답변 감사합니다. 매번 한수 배웁니다. 

 

추가 질문있습니다. 

 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

상기처럼 하면 같은 결과가 도출 되나요? 조건, 크거나 같음에서 같음을 빼면 되지 않나 문의 드립니다. 


by 마농 [2018.03.13 08:16:26]

같음(=)을 넣어야 정각(0분0초)의 자료가 포함됩니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입