보기로 주어진 날짜가 어떤 경우에는 마침표(.)로 구분되고 어떤 경우에는 콜론(:)으로 구별되는 것을 그냥 마이너스(-)로 구별되어 있다고 가정했음.

 

 

-- 1차 조회용

SELECT 

--rngBgn AS [기준], 

evtDt

--, evtKind

, ll.data

FROM(

SELECT DATEADD(HOUR,DATEDIFF( HOUR, 0,CAST( '20' + 날짜 + ' ' + 시간 AS datetime ) ),0) AS rngBgn,

MIN( CAST( '20' + 날짜 + ' ' + 시간 AS datetime ) ) AS minDt,

MAX( CAST( '20' + 날짜 + ' ' + 시간 AS datetime ) ) AS maxDt

FROM tbl

GROUP BY DATEADD(HOUR,DATEDIFF( HOUR, 0,CAST( '20' + 날짜 + ' ' + 시간 AS datetime ) ),0)

) AS kk

UNPIVOT ( evtDt FOR evtKind IN ([minDT], [maxDT])) AS uu

INNER JOIN tbl AS ll ON uu.evtDt = CAST( '20' + ll.날짜 + ' ' + ll.시간 AS datetime )

ORDER BY evtDt

;

FROM(

SELECT DATEADD(HOUR,DATEDIFF( HOUR, 0,CAST( '20' + 날짜 + ' ' + 시간 AS datetime ) ),0) AS rngBgn,

MIN( CAST( '20' + 날짜 + ' ' + 시간 AS datetime ) ) AS minDt,

MAX( CAST( '20' + 날짜 + ' ' + 시간 AS datetime ) ) AS maxDt

FROM tbl

GROUP BY DATEADD(HOUR,DATEDIFF( HOUR, 0,CAST( '20' + 날짜 + ' ' + 시간 AS datetime ) ),0)

) AS kk

INNER JOIN tbl AS minData ON kk.minDt = CAST( '20' + minData.날짜 + ' ' + minData.시간 AS datetime )

INNER JOIN tbl AS maxData ON kk.maxDt = CAST( '20' + maxData.날짜 + ' ' + maxData.시간 AS datetime )

 

ORDER BY kk.rngBgn

;