시작시간 | 종료시간 |
|
|
2016-05-09 09:00:00 | 2016-05-09 09:30:00 |
|
|
2016-05-09 23:00:00 | 2016-05-10 09:00:00 |
|
|
|
|
|
|
이와같은 데이터가 있을때 합산하는 시점은 오전 08시로 기준을 잡고
2016-05-09 : 570분 (9:00 ~ 09:30 = 30분 + 23:00 ~ 익일 08:00 = 540분)
2016-05-10 : 60분 (기준시간 08:00~ 09:00 = 60분)
이렇게 SELECT 를 하고싶은데요.. 너무헷갈립니다 고수분들 조언부탁 드립니다.
Comment 5
-
ilovejsp
2016.05.10 14:54
-
Terry
2016.05.11 12:11
날쿼리입니다 -,-;;;
단순무식하게 제시된 조건만으로 결과치 나오게끔 쿼리작성해보았어요..
참고만 하세요~~
---쿼리시작---
;With tblA(s_ymd,e_ymd) As
(
Select '2016-05-09 09:00:00','2016-05-09 09:30:00' Union All
Select '2016-05-09 23:00:00','2016-05-10 09:00:00'
)
,tblB(s_ymd,s_min,e_ymd,e_min) As
(
Select Left(a.s_ymd,10) As s_ymd
-- ,DATEDiff(minute,a.s_ymd,a.e_ymd)
,(
Case When DATEDIFF(day,a.s_ymd,a.e_ymd) = 0
Then DATEDiff(minute,a.s_ymd,a.e_ymd)
ELSE DATEDIFF(MINUTE,a.s_ymd,LEFT(a.e_ymd,10) + ' 08:00:00' )
End
)
,(
Case When DATEDIFF(day,a.s_ymd,a.e_ymd) = 0
Then ''
ELSE Left(a.e_ymd,10)--DATEDiff(minute,a.s_ymd,a.e_ymd)
End
)
,(
Case When DATEDIFF(day,a.s_ymd,a.e_ymd) = 0
Then 0
ELSE DATEDiff(minute,a.s_ymd,a.e_ymd)
- DATEDIFF(MINUTE,a.s_ymd,LEFT(a.e_ymd,10) + ' 08:00:00' )
End
)
From tblA a
)
,tblC (ymd,work_min) As
(
Select a.s_ymd
,a.s_min
From tblB a
Where a.e_ymd = ''
Union AllSelect a.s_ymd
,a.s_min
From tblB a
Where a.e_ymd <> ''Union All
Select a.e_ymd
,a.e_min
From tblB a
Where a.e_ymd <> '')
Select a.ymd
,SUM(a.work_min) As work_min
From tblC a
Group By a.ymd
---쿼리끝--- -
왕만두와우동
2016.06.01 14:56
성의있는 답변 감사합니다. 많은 도움되었네요.
-
minsouk
2016.05.12 00:57
참고만 하세요....USE TEMPDBGOIF OBJECT_ID ('TBLX') IS NOT NULLDROP TABLE TBLXGOCREATE TABLE TBLX(CSTARTDATE DATETIME,CENDDATE DATETIME)GOINSERT INTO TBLX VALUES('2016-05-09 09:00:00', '2016-05-09 09:30:00'),('2016-05-09 23:00:00', '2016-05-10 09:00:00'),('2016-05-11 23:00:00', '2016-05-13 09:00:00')GOSELECTCASEWHEN A.NUMDAY = 1 AND B.NUMDAY = 1 THEN CONVERT(CHAR(8), CSTARTDATE, 112)ELSE DATEADD(DAY, B.NUMDAY -1, CONVERT(CHAR(8), CSTARTDATE, 112))END AS RDATE, SUM(CASEWHEN A.NUMDAY = 1 AND B.NUMDAY = 1 THEN DATEDIFF(MINUTE, CSTARTDATE, CENDDATE)WHEN A.NUMDAY <> 1 AND B.NUMDAY = 1 THEN DATEDIFF(MINUTE, CSTARTDATE, DATEADD(DAY, 1, CONVERT(CHAR(8), CSTARTDATE, 112)))WHEN A.NUMDAY <> B.NUMDAY THEN 60 * 24WHEN A.NUMDAY > 1 AND A.NUMDAY = B.NUMDAY THEN DATEDIFF(MINUTE, CONVERT(CHAR(8), CENDDATE, 112),CENDDATE )END) AS SUMMINFROM(SELECT DATEADD(HOUR, -8, CSTARTDATE) AS CSTARTDATE, DATEADD(HOUR, -8, CENDDATE) AS CENDDATE, DATEDIFF(DAY, DATEADD(HOUR, -8, CSTARTDATE), DATEADD(HOUR, -8, CENDDATE)) + 1 AS NUMDAYFROM TBLX) AJOIN (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) NUMDAYFROM MASTER.DBO.SPT_VALUES) BON A.NUMDAY >= B.NUMDAYGROUP BYCASEWHEN A.NUMDAY = 1 AND B.NUMDAY = 1 THEN CONVERT(CHAR(8), CSTARTDATE, 112)ELSE DATEADD(DAY, B.NUMDAY -1, CONVERT(CHAR(8), CSTARTDATE, 112))ENDORDER BY 1 -
왕만두와우동
2016.06.01 14:57
감사합니다 ~ 참고하여 좋은 쿼리 작성하겠습니다.
어렵네요 ...