입력된 기간들의 중복을 제외한 총합을 구하고 싶습니다.

하늘민 2016.11.10 15:11 Views : 2503

번호 시작시간 종료시간

1000    2016-11-10 09:00    2016-11-10 09:40
1000    2016-11-10 09:30    2016-11-10 09:50
1000    2016-11-10 10:00    2016-11-10 10:10
1000    2016-11-10 10:05    2016-11-10 10:10
1000    2016-11-10 10:05    2016-11-10 10:15

원하는 결과

번호 총합

1000 65

검색해서 찾은 쿼리문으로 짜봤는데

결과값이 60이 나오네요 ㅠ 도와주세요

SELECT NO, SUM(DATEDIFF(MINUTE, Date1, Date2)) AS WorkTime
FROM
(
       SELECT A.NO, A.Date1,
       CASE WHEN A.Date2 < B.Date1 OR B.Date1 IS NULL THEN A.Date2 ELSE B.Date1 END AS Date2
       FROM
       (
             SELECT NO, Date1, Date2, ROW_NUMBER() OVER (PARTITION BY NO ORDER BY Date1) AS NUM1
             FROM DateTest
             GROUP BY NO, Date1, Date2
       ) AS A
       LEFT OUTER JOIN

       (
             SELECT NO, Date1, ROW_NUMBER() OVER (PARTITION BY NO ORDER BY Date1) AS NUM1
             FROM DateTest
             GROUP BY NO, Date1
       ) AS B
       ON A.NUM1 = B.NUM1 - 1
) AS A
GROUP BY NO

I want to

cmd_document_vote_user Update Log List

Comment 6

건우아빠

2016.11.10 19:27

with data
as
(

select 1000 [번호], '2016-11-10 09:00' [시작시간] , '2016-11-10 09:40' [종료시간] union all
select 1000 ,'2016-11-10 09:30', '2016-11-10 09:50' union all
select 1000 ,'2016-11-10 10:00', '2016-11-10 10:10' union all
select 1000 ,'2016-11-10 10:05', '2016-11-10 10:10' union all
select 1000 ,'2016-11-10 10:05', '2016-11-10 10:15'
) , res as
(
select * , ROW_NUMBER() over( partition by [번호] order by [시작시간] , [종료시간] ) no
from data
) ,

result as

(

select a.[번호] , case when a.[시작시간] < b.[종료시간] then b.[종료시간] else a.[시작시간] end [시작시간] , a.[종료시간]
from res a left join res b on a.[번호] = b.[번호] and a.no = b.no + 1

)
select * , DATEDIFF (mi,[시작시간] , [종료시간] )
from result

sum 내시면 될듯 합니다.

cmd_comment_vote_user Upvote0 Downvote0
하늘민

2016.11.11 09:13

답변 감사합니다.
위의 데이터에선 잘 작동하는데.
마지막 행의 시작시간이 10:05분이 아니고, 예를 들어 10:04분이다. 이러면 또 계산이 어긋나네요
사용자가 어떻게 입력할지가 안정해져있어서... ㅠ

cmd_comment_vote_user Upvote0 Downvote0

항해자™

2016.11.11 18:33

아래처럼 하고, 합계만 count(*)로 구하면 되지 않을까,, 테스트 해 보았습니다,,,

그리고 67로 나오네요,,

각각 시작 시간인 09:00 과 10:00 을 제외해야 한다면, 65가 맞겠네요,,

create table #tmpTimes
(	cTime datetime
)
go
truncate table #tmpTimes
insert into #tmpTimes
select	dateadd(minute,cRows,'2016-11-01')
from	(	select	top 100000
					row_number() over(order by (select 1)) as cRows
			from	syscolumns as a
				,	syscolumns as b
				,	syscolumns as c
				,	syscolumns as d
		) as a
go
;with cteSample (cIdx, cStart, cLimit) as (
	select 1000, '2016-11-10 09:00', '2016-11-10 09:40' union all
	select 1000, '2016-11-10 09:30', '2016-11-10 09:50' union all
	select 1000, '2016-11-10 10:00', '2016-11-10 10:10' union all
	select 1000, '2016-11-10 10:05', '2016-11-10 10:10' union all
	select 1000, '2016-11-10 10:05', '2016-11-10 10:15'
)
select	b.cTime
from	cteSample as a
		cross apply (
			select	cTime
			from	#tmpTimes
			where	cTime between a.cStart and a.cLimit
		) as b
group by cTime
order by cTime
go

cmd_comment_vote_user Upvote0 Downvote0

하늘민

2016.11.12 13:52

이해가 잘안가기는 하는데 ㅠ
이런 방법도 있군요.
중간의 syscolumns에서 뽑아오는건 어떤 값이고 무슨 이유인가요

cmd_comment_vote_user Upvote0 Downvote0
항해자™

2016.11.14 09:42

분 단위로 구성된 날짜 테이블입니다,,
실제로는 임시 테이블이 아닌, 미리 만들어 놓고 사용하는게 좋을 것 같구요,,,
날짜 컬럼에 인덱스가 있으면 더 좋을 수 있겠네요,,

누가 그랬던거 같은데 한 100년치 미리 만들어 놓으면 걱정 읎지 않을까요ㅎㅎ
더 좋은 방법은 다른분께서, 또 답변 달아 주시겠죠ㅎㅎ

cmd_comment_vote_user Upvote0 Downvote0
jude

2017.01.05 08:55

sql server 2012는 LAG() , LEAD() 함수가 있어서 다행입니다.
조금은 간단하게 쿼리를 만들수 있습니다.

위에서 말씀하신것 처럼 10:04분으로 바꾸더라도 총 시간은 65분 이어야 합니다.
아래 쿼리 참고해 보세요.

with data
as
(
select 1000 [번호], '2016-11-10 09:00' [시작시간] , '2016-11-10 09:40' [종료시간] union all
select 1000 ,'2016-11-10 09:30', '2016-11-10 09:50' union all
select 1000 ,'2016-11-10 10:00', '2016-11-10 10:10' union all
select 1000 ,'2016-11-10 10:05', '2016-11-10 10:10' union all
select 1000 ,'2016-11-10 10:04', '2016-11-10 10:15' /* 05분을 04분으로 바꿔봤습니다.*/
)
SELECT NO,
SUM(DATEDIFF(MINUTE,A.START_TIME,A.END_TIME)) SUM_MINUTE
FROM (
SELECT NO,
START_TIME ORG_START_TIME,
END_TIME ORG_END_TIME,
BEF_END_TIME,
CASE WHEN BEF_END_TIME IS NULL THEN START_TIME
ELSE CASE WHEN START_TIME < BEF_END_TIME THEN BEF_END_TIME
ELSE START_TIME
END
END START_TIME,
CASE WHEN BEF_END_TIME IS NULL THEN END_TIME
ELSE CASE WHEN END_TIME < BEF_END_TIME THEN BEF_END_TIME
ELSE END_TIME
END
END END_TIME
FROM (
SELECT [번호] NO,
[시작시간] START_TIME,
[종료시간] END_TIME,
LAG([종료시간]) OVER(PARTITION BY [번호] ORDER BY [시작시간]) BEF_END_TIME
FROM data
) A
) A
GROUP BY NO

cmd_comment_vote_user Upvote0 Downvote0

No.	Subject	Author	Date	Views
Notice	SQL강좌: 챗GPT와 함께 배우는 SQL Server 무료 강좌 목차와 소개 (2023년 9월 업데이트)	코난(김대우)	2023.08.18	36180
Notice	Python 무료 강좌 - 기초, 중급, 머신러닝(2023년 6월 업데이트)	코난(김대우)	2021.01.01	18697
9603	sql 서버 관리 관련 질문 [1]	오늘도난	2016.11.17	3315
9602	쿼리좀 풀어주세요-2 [2]	메아리	2016.11.16	2285
9601	복구후 예전으로 다시 복원하는법이 있나요. [2]	큘러	2016.11.16	3236
9600	쿼리좀 풀어주세요 ㅠ [12]	메아리	2016.11.16	2004
9599	스칼라 함수 안에서 업데이트문..? [5]	후알유?	2016.11.15	2447
9598	문자 + 숫자 채번(시퀀스).. [2]	후알유?	2016.11.14	4235
9597	쿼리 쪼깨지? [2]	문상연	2016.11.14	2572
9596	mysql query 질문드립니다! [3]	rhqnffksdl	2016.11.12	2446
9595	select 속도 궁금증.. [2]	후알유?	2016.11.10	2071
»	입력된 기간들의 중복을 제외한 총합을 구하고 싶습니다. [6]	하늘민	2016.11.10	2503
9593	outofmemory 문제 [2]	에스디	2016.11.10	3532
9592	sp_execute 11 문의 [1]	aspa77	2016.11.09	2136
9591	트리거내의 update문 문의 [1]	aspa77	2016.11.09	2687
9590	sql2012 정품인증 관련하여 문의드립니다.	아메리카노	2016.11.08	1959
9589	오라클 sql 질문입니다...	hanks	2016.11.08	3760
9588	간단한 where 조건 질문드립니다 [2]	MSSQL초보입문자	2016.11.08	1916
9587	sql 교재 추천 [1]	쌑틀러	2016.11.07	2481
9586	간단한 쿼리 질문드립니다. [2]	후알유?	2016.11.07	2049
9585	디비압축과 교착이 관계가있을까요?	큘러	2016.11.04	3222
9584	프로시저 내에서 쿼리 중간에 IF문 쓰기...? [4]	문상연	2016.11.04	3378

Write Tag

First Page 64 65 66 67 68 69 70 71 72 73 Last Page

입력된 기간들의 중복을 제외한 총합을 구하고 싶습니다.

Comment 6

건우아빠

하늘민

항해자™

하늘민

항해자™

jude

커뮤니티

데이터베이스 개발

클라우드 개발

머신러닝/AI개발

파이썬&오픈소스 개발