DATA | T1 | T2 | T3 | T4 | T5 | PANJUNG |
DATA 24~30 | 23 | 25 | 26 | 27 | 33 | NO |
DATA 컬럼에 있는값을 substring을 이용해 24 와 30 으로 나눠서 case when 을 이용해서 해봤느넫 잘안되서요..
T1~T5 에 있는값들이 한 컬럼이라도 DATA 에 있는 24~30 를 초과하거나 부족하면 무조건 NO가 나와야되고 그 반대면 YES입니다..
계속 삽질하다 질문드려요..부탁드립니다.
Comment 4
-
아스날
2015.03.03 16:28
CREATE TABLE #TT(DATA VARCHAR(100),T1 INT,T2 INT,T3 INT,T4 INT,T5 INT)GOINSERT #TTVALUES ('DATA 24~30', 23, 25, 26, 27, 33),('DATA 35~45', 33, 37, 35, 48, 10),('DATA 10~15', 11, 12, 13, 14, 15)SELECT AA.DATA,b.T1,b.T2,b.T3,b.T4,b.T5,IIF(AA.T1 + AA.T1 + AA.T1 + AA.T1 + AA.T1 >= 1, 'NO', 'YES') AS 'PANJUNG'FROM (SELECT DATA,IIF(T1 < LEFT(RIGHT(DATA, 5), 2) OR T1 > RIGHT(DATA, 2), 1, 0) AS 'T1',IIF(T2 < LEFT(RIGHT(DATA, 5), 2) OR T2 > RIGHT(DATA, 2), 1, 0) AS 'T2',IIF(T3 < LEFT(RIGHT(DATA, 5), 2) OR T3 > RIGHT(DATA, 2), 1, 0) AS 'T3',IIF(T4 < LEFT(RIGHT(DATA, 5), 2) OR T4 > RIGHT(DATA, 2), 1, 0) AS 'T4',IIF(T5 < LEFT(RIGHT(DATA, 5), 2) OR T5 > RIGHT(DATA, 2), 1, 0) AS 'T5'FROM #TT) AAJOIN #TT b ON AA.DATA = b.DATA이렇게 될까요 ~? -
고구망
2015.03.03 16:40
답변감사합니다...iif 를 사용하지않고는 불가능할까요? 2012버젼만 지원하는군요 ㅠㅠ
-
아스날
2015.03.03 16:55
2012라고 되어있길래 ^^;;2008 이하 버전이시면 아래처럼 되겠네요CREATE TABLE #TT(DATA VARCHAR(100),T1 INT,T2 INT,T3 INT,T4 INT,T5 INT)GOINSERT #TTVALUES ('DATA 24~30', 23, 25, 26, 27, 33),('DATA 35~45', 33, 37, 35, 48, 10),('DATA 10~15', 11, 12, 13, 14, 15)SELECT AA.DATA,b.T1,b.T2,b.T3,b.T4,b.T5,CASE WHEN AA.T1 + AA.T1 + AA.T1 + AA.T1 + AA.T1 >= 1 THEN 'NO' ELSE 'YES' END AS 'PANJUNG'FROM (SELECT DATA,CASE WHEN T1 < LEFT(RIGHT(DATA, 5), 2) OR T1 > RIGHT(DATA, 2) THEN 1 ELSE 0 END AS 'T1',CASE WHEN T2 < LEFT(RIGHT(DATA, 5), 2) OR T2 > RIGHT(DATA, 2) THEN 1 ELSE 0 END AS 'T2',CASE WHEN T3 < LEFT(RIGHT(DATA, 5), 2) OR T3 > RIGHT(DATA, 2) THEN 1 ELSE 0 END AS 'T3',CASE WHEN T4 < LEFT(RIGHT(DATA, 5), 2) OR T4 > RIGHT(DATA, 2) THEN 1 ELSE 0 END AS 'T4',CASE WHEN T5 < LEFT(RIGHT(DATA, 5), 2) OR T5 > RIGHT(DATA, 2) THEN 1 ELSE 0 END AS 'T5'FROM #TT) AAJOIN #TT b ON AA.DATA = b.DATA -
철스
2015.03.03 18:28
제가 생각한거는 T1 ~ T5의 Min값과 Max값을 구하여 Data값을 비교하는 방법 입니다.
; WITH AAA AS (
SELECT
LEFT(RIGHT(DATA, 5), 2) AS MIN_DATA,
RIGHT(DATA, 2) AS MAX_DATA,
(SELECT MIN(C) FROM (VALUES(T1),(T2),(T3),(T4),(T5)) T (C)) AS MIN_T,
(SELECT MAX(C) FROM (VALUES(T1),(T2),(T3),(T4),(T5)) T (C)) AS MAX_T
, DATA, T1, T2, T3, T4, T5
FROM #TT
)
SELECT
DATA, T1, T2, T3, T4, T5,
CASE WHEN MIN_DATA > MIN_T OR MAX_DATA < MAX_T THEN 'NO' ELSE 'YES' END AS PANJUNG
--, MIN_DATA, MAX_DATA, MIN_T, MAX_T
FROM AAA
