Comment 7
-
지영아빠
2020.07.15 10:33
-
톰슨
2020.07.15 11:59
감사합니다!!
쿼리가 이해가 안되서
분석 좀 해봐야겠네요.
-
톰슨
2020.07.15 12:52
위 예제에
SELECT '2020-07-14 00:00:00.000', '2020-07-16 00:00:00.000'
이걸 하나 추가하면 사실상 위 이력을 제외하고는 전부 중복시간이라
위 이력에 대한 시간만 차이만 나오면 되야하는데
이런건 안되네요..
방법없을까요?
-
약은 중
2020.07.16 16:22
WITH time_list AS(SELECT '2020-07-14 12:00:00.000' start_time, '2020-07-14 14:00:00.000'end_timeUNION ALLSELECT '2020-07-14 13:00:00.000', '2020-07-14 15:00:00.000'UNION ALLSELECT '2020-07-14 14:00:00.000', '2020-07-14 16:00:00.000'UNION ALLSELECT '2020-07-14 20:00:00.000', '2020-07-15 01:00:00.000'UNION ALLSELECT '2020-07-15 00:00:00.000', '2020-07-15 02:00:00.000'UNION ALLSELECT '2020-07-14 21:00:00.000', '2020-07-15 01:00:00.000'UNION ALLSELECT '2020-07-15 07:00:00.000', '2020-07-15 08:00:00.000'--UNION ALL--SELECT '2020-07-14 00:00:00.000', '2020-07-16 00:00:00.000'),V_GROUPING AS(SELECT ROW_NUMBER() OVER(ORDER BY A.START_TIME) RN,A.start_time,MAX(A.end_time) end_timeFROM time_list AGROUP BY A.start_time)SELECT SUM(DATEDIFF(SECOND,A.start_time,A.end_time)) / 60.0FROM (SELECT MIN(A.start_time) start_time,MAX(A.end_time) end_timeFROM (SELECT A.start_time,A.end_time,SUM(A.CONTINUE_YN) OVER(ORDER BY RN) CONTINUE_YNFROM (SELECT A.RN,A.start_time,A.end_time,CASE WHEN A.end_time <= C.lag_end_time THEN 1 ELSE 0 END SKIP_YN,CASE WHEN A.start_time <= C.lag_end_time THEN 0 ELSE 1 END CONTINUE_YNFROM V_GROUPING A OUTER APPLY (SELECT MAX(B.end_time) lag_end_timeFROM V_GROUPING BWHERE B.RN < A.RNAND A.start_time BETWEEN B.start_time AND B.end_time) C) AWHERE A.SKIP_YN = 0) AGROUP BY CONTINUE_YN) A -
톰슨
2020.07.17 10:27
감사합니다!! ㅠㅠ
분석해보겠습니다.
-
진윤호
2020.07.17 17:01
2012 이상 버전 사용하시면 LAG를 쓰는게 더 간단해 보입니다.
drop table if exists #t
create table #t
(
IDX INT IDENTITY(1,1),
START_TIME datetime,
END_TIME datetime
)
insert into #t(START_TIME, END_TIME)
SELECT '2020-07-14 12:00:00.000', '2020-07-14 14:00:00.000'
UNION ALL
SELECT '2020-07-14 13:00:00.000', '2020-07-14 15:00:00.000'
UNION ALL
SELECT '2020-07-14 14:00:00.000', '2020-07-14 16:00:00.000'
UNION ALL
SELECT '2020-07-14 20:00:00.000', '2020-07-15 01:00:00.000'
UNION ALL
SELECT '2020-07-15 00:00:00.000', '2020-07-15 02:00:00.000'
UNION ALL
SELECT '2020-07-14 21:00:00.000', '2020-07-15 01:00:00.000'
UNION ALL
SELECT '2020-07-15 07:00:00.000', '2020-07-15 08:00:00.000'
--UNION ALL
--SELECT '2020-07-14 00:00:00.000', '2020-07-16 00:00:00.000'
SELECT SUM(NOW_DIFF) + SUM(DUP_DIFF) AS TOTAL_TIME
FROM (
SELECT DATEDIFF(SECOND, START_TIME, END_TIME) AS NOW_DIFF
, CASE WHEN LAG(END_TIME, 1, NULL) OVER(ORDER BY START_TIME) >= START_TIME THEN DATEDIFF(SECOND, LAG(END_TIME, 1, NULL) OVER(ORDER BY START_TIME), START_TIME) ELSE 0 END AS DUP_DIFF
FROM (
SELECT A.IDX
, MAX(A.START_TIME) AS START_TIME
, MAX(A.END_TIME) AS END_TIME
, MAX(CASE WHEN A.START_TIME > B.START_TIME AND A.END_TIME < B.END_TIME THEN 1 ELSE 0 END) AS bitSkip
FROM #t AS A
CROSS APPLY #t AS B
GROUP BY A.IDX
) AS T
WHERE bitSkip = 0
) AS T2
-
톰슨
2020.07.21 17:42
감사합니다!
참고요
create table #t
(
IDX INT IDENTITY(1,1),
START_TIME datetime,
END_TIME datetime
)
insert into #t(START_TIME, END_TIME)
SELECT '2020-07-14 12:00:00.000', '2020-07-14 14:00:00.000'
UNION ALL
SELECT '2020-07-14 13:00:00.000', '2020-07-14 15:00:00.000'
UNION ALL
SELECT '2020-07-14 14:00:00.000', '2020-07-14 16:00:00.000'
UNION ALL
SELECT '2020-07-14 20:00:00.000', '2020-07-15 01:00:00.000'
UNION ALL
SELECT '2020-07-15 00:00:00.000', '2020-07-15 02:00:00.000'
UNION ALL
SELECT '2020-07-14 21:00:00.000', '2020-07-15 01:00:00.000'
UNION ALL
SELECT '2020-07-15 07:00:00.000', '2020-07-15 08:00:00.000'
;with t as (
select row_number() over(order by start_time asc) as rid, start_time, end_time from #t
)
select
sum(
DATEDIFF(mi,
a.start_time,
case when a.end_time > b.start_time then b.start_time else a.end_time end )
)
as diff_mi
from
t a
left outer join t b
on a.rid = b.rid -1