고수님들 한번 봐주십시요.
rownum cor_cd plant_cd pline_cd device_id stop_dt flag
1 100 100 050 13 2022-06-09 07:03:36 RUN
2 100 100 050 13 2022-06-09 11:23:50 IDLE
3 100 100 050 13 2022-06-09 12:28:29 RUN
4 100 100 050 13 2022-06-09 17:20:50 IDLE
1 100 100 050 13 2022-06-10 07:04:52 RUN
2 100 100 050 13 2022-06-10 07:31:37 IDLE
3 100 100 050 13 2022-06-10 07:54:20 RUN
4 100 100 050 13 2022-06-10 11:08:11 IDLE
5 100 100 050 13 2022-06-10 19:21:39 RUN
6 100 100 050 13 2022-06-10 20:33:07 IDLE
위와 같은 테이블에서 원하는 결과값은
rownum cor_cd plant_cd pline_cd device_id stop_dt flag
1 100 100 050 13 2022-06-09 07:03:36 RUN
4 100 100 050 13 2022-06-09 17:20:50 IDLE
1 100 100 050 13 2022-06-10 07:04:52 RUN
6 100 100 050 13 2022-06-10 20:33:07 IDLE
이렇게 결과값을 구하고 싶은데, 잘 안되네요.
select *
from #temp200t a
where 1=1
and a.flag = 'RUN'
and a.rownum in (select min(a.rownum)
from #temp200t a
group by convert(varchar(10), a.stop_dt, 23)
)
union
select *
from #temp200t a
where 1=1
and a.flag = 'IDLE'
and a.rownum in (select max(a.rownum)
from #temp200t a
group by convert(varchar(10), a.stop_dt, 23)
)
이렇게 하면 아래와 같이 나옵니다.
rownum cor_cd plant_cd pline_cd device_id stop_dt flag
1 100 100 050 13 2022-06-09 07:03:36 RUN
4 100 100 050 13 2022-06-09 17:20:50 IDLE
1 100 100 050 13 2022-06-10 07:04:52 RUN
4 100 100 050 13 2022-06-10 11:08:11 IDLE
6 100 100 050 13 2022-06-10 20:33:07 IDLE
Comment 2
-
지영아빠
2022.06.15 09:04
참고요
with tmp as (
select 1 as rownum, 100 as cor_cd, 100 as plant_cd, '050' as pline_cd, 13 as device_id, '2022-06-09 07:03:36' as stop_dt, 'RUN' as flag union all
select 2 as rownum, 100 as cor_cd, 100 as plant_cd, '050' as pline_cd, 13 as device_id, '2022-06-09 11:23:50' as stop_dt, 'IDLE' as flag union all
select 3 as rownum, 100 as cor_cd, 100 as plant_cd, '050' as pline_cd, 13 as device_id, '2022-06-09 12:28:29' as stop_dt, 'RUN' as flag union all
select 4 as rownum, 100 as cor_cd, 100 as plant_cd, '050' as pline_cd, 13 as device_id, '2022-06-09 17:20:50' as stop_dt, 'IDLE' as flag union all
select 1 as rownum, 100 as cor_cd, 100 as plant_cd, '050' as pline_cd, 13 as device_id, '2022-06-10 07:04:52' as stop_dt, 'RUN' as flag union all
select 2 as rownum, 100 as cor_cd, 100 as plant_cd, '050' as pline_cd, 13 as device_id, '2022-06-10 07:31:37' as stop_dt, 'IDLE' as flag union all
select 3 as rownum, 100 as cor_cd, 100 as plant_cd, '050' as pline_cd, 13 as device_id, '2022-06-10 07:54:20' as stop_dt, 'RUN' as flag union all
select 4 as rownum, 100 as cor_cd, 100 as plant_cd, '050' as pline_cd, 13 as device_id, '2022-06-10 11:08:11' as stop_dt, 'IDLE' as flag union all
select 5 as rownum, 100 as cor_cd, 100 as plant_cd, '050' as pline_cd, 13 as device_id, '2022-06-10 19:21:39' as stop_dt, 'RUN' as flag union all
select 6 as rownum, 100 as cor_cd, 100 as plant_cd, '050' as pline_cd, 13 as device_id, '2022-06-10 20:33:07' as stop_dt, 'IDLE' as flag
)
select a.*
from tmp a
inner join (
select
convert(varchar(10), stop_dt, 23) dt , min(stop_dt) as min_dt, max(stop_dt) as max_dt
from tmp
group by convert(varchar(10), stop_dt, 23)
) b
on convert(varchar(10), a.stop_dt, 23) = b.dt
and (a.stop_dt = b.min_dt or a.stop_dt = b.max_dt)
order by stop_dt asc
해결해서 공유합니다.
select b.*
from
(
select convert(varchar(10), a.stop_dt, 23) as run_dt, min(a.rownum) as rownum
from #temp200t a
where 1=1
and a.flag = 'RUN'
group by convert(varchar(10), a.stop_dt, 23)
union
select convert(varchar(10), a.stop_dt, 23) as idle_dt, max(a.rownum) as rownum
from #temp200t a
where 1=1
and a.flag = 'IDLE'
group by convert(varchar(10), a.stop_dt, 23)
) a
left outer join
(
select *
from #temp200t a
) b on (a.rownum = b.rownum and a.run_dt=convert(varchar(10), b.stop_dt, 23))
더 좋은 방법이 있으면 답변 부탁드립니다.