| record_dt | M1 | M2 | M3 | Y1 | Y2 |
| 20221122010000 | 0 | 20221121000000 | 0 | 20221121120000 | 0 |
| 20221122020000 | 20221122130000 | 0 | 0 | 0 | 0 |
M table
record_dt = 입력된 날짜시간
M1 ~ M3 = 날짜시간 데이터
Y1 ~ Y2 = 날짜시간 데이터
| record_dt | tagname | tagvalue |
| 20221121120000 | kwh | 100 |
| 20221121123000 | kwh | 150 |
| 20221121130000 | kwh | 200 |
T table
record_dt = 입력된 날짜시간
tagname = 태그 이름
tagvalue = 태그 값
M1부터 M3의 컬럼에서 0이 아닌 값과 Y1부터 Y2의 컬럼에서 0이 아닌 값을 조건으로 조회하고 싶습니다.
select Tagvalue from T where record_dt between M0 ~ M3 중 0이 아닌값 and Y1 ~ Y2 중 0이 아닌 값 and tagname = 'kwh'
Comment 1
-
지영아빠
2022.11.23 09:26
Y1, Y2가 0이거나 M1,M2, M3가 0었을 때 어떻게 처리해야 할지가 없어 대략 해봤습니다.
nullif, coalesche를 쓰시면 될듯합니다.
with m as (
select '20221122010000' as record_dt, '0' as m1, '20221121000000' as m2, '0' as m3, '20221121120000' as y1, '0' as y2 union all
select '20221122020000', '20221122130000', '0', '0', '0', '0'
)
, t as (
select '20221121120000' as record_dt, 'kwh' as tagname, '100' as tagvalue union all
select '20221121123000' as record_dt, 'kwh' as tagname, '150' as tagvalue union all
select '20221121130000' as record_dt, 'kwh' as tagname, '200' as tagvalue
)
select
t.record_dt,
t.tagname,
t.tagvalue,
COALESCE(nullif(m1, '0'), nullif(m2, '0'), nullif(m3, '0')) as m,
COALESCE(nullif(y1, '0'), nullif(y2, '0'), replace(m.record_dt, '2022', '2033')) as y
From
t
left outer join m
on t.record_dt between COALESCE(nullif(m1, '0'), nullif(m2, '0'), nullif(m3, '0'))
and COALESCE(nullif(y1, '0'), nullif(y2, '0'), replace(m.record_dt, '2022', '2033'))
and tagname = 'kwh'