데이터베이스를 사랑하는 사람들의 모임 데이터베이스 사랑넷

MS-SQL Q&A 7095 게시물 읽기

No. 7095

mssql 식사시간 구하기
작성자	김알지	작성일	2017-09-27 15:35	조회수	3,528

업무 시간 중 점심 저녁 시간을 지정 하여 해당 시간의 총 합을 구하는 쿼리를 문의 드립니다.

작업 시간 : 2017-09-27 08:00 변동data

작업 시간 : 2017-09-27 19:00 변동data

점심시간 : 12:30 ~ 13:10

저녁시간 : 17:10 ~ 17:40

업무 시간외 식사 시간 제외 쿼리를 짜려고 합니다.

dateDiff를 활용하여 data를 가져 오는 방법을 활용적인데 보다 효과적인 쿼리가 있다면 부탁 드립니다.

이 글에 대한 댓글이 총 5건 있습니다.

WITH work_t AS
(
SELECT '2017-09-27 08:00' sdt, '2017-09-27 12:00' edt
UNION ALL SELECT '2017-09-27 08:00', '2017-09-27 13:00'
UNION ALL SELECT '2017-09-27 08:00', '2017-09-27 17:00'
UNION ALL SELECT '2017-09-27 08:00', '2017-09-27 17:30'
UNION ALL SELECT '2017-09-27 08:00', '2017-09-27 17:30'
UNION ALL SELECT '2017-09-27 08:00', '2017-09-27 19:00'
UNION ALL SELECT '2017-09-27 13:00', '2017-09-27 19:00'
)
SELECT sdt, edt
     , DATEDIFF(mi, sdt, edt) tot_mi
     , CASE WHEN s1 > e1 THEN 0 ELSE DATEDIFF(mi, s1, e1) END lunch_mi
     , CASE WHEN s2 > e2 THEN 0 ELSE DATEDIFF(mi, s2, e2) END diner_mi
FROM (SELECT sdt, edt
             , CASE WHEN stm > s1 THEN sdt ELSE symd + ' ' + s1 END s1
             , CASE WHEN etm < e1 THEN edt ELSE eymd + ' ' + e1 END e1
             , CASE WHEN stm > s2 THEN sdt ELSE symd + ' ' + s2 END s2
             , CASE WHEN etm < e2 THEN edt ELSE eymd + ' ' + e2 END e2
          FROM (SELECT sdt, edt
                     , SUBSTRING(sdt, 12, 5) stm
                     , SUBSTRING(edt, 12, 5) etm
                     , SUBSTRING(sdt, 1, 10) symd
                     , SUBSTRING(edt, 1, 10) eymd
                  FROM work_t
                ) a
             , (SELECT '12:30' s1, '13:10' e1, '17:10's2, '17:40' e2) b
        ) a
;

마농(manon94)님이 2017-09-28 08:39에 작성한 댓글입니다.

SELECT '2017-09-27 08:00' sdt, '2017-09-29 12:00'

혹시 시작일자와 종료 일자가 다를 경우도 가능 하신지요?

김알지님이 2017-09-28 11:06에 작성한 댓글입니다.
이 댓글은 2017-09-28 11:24에 마지막으로 수정되었습니다. Edit Ｘ

WITH work_t AS
(
SELECT 1 pk, '2017-09-27 08:00' sdt, '2017-09-27 12:00' edt
UNION ALL SELECT 2, '2017-09-27 08:00', '2017-09-27 13:00'
UNION ALL SELECT 3, '2017-09-27 08:00', '2017-09-27 17:00'
UNION ALL SELECT 4, '2017-09-27 08:00', '2017-09-27 17:30'
UNION ALL SELECT 5, '2017-09-27 08:00', '2017-09-27 17:30'
UNION ALL SELECT 6, '2017-09-27 08:00', '2017-09-27 19:00'
UNION ALL SELECT 7, '2017-09-27 13:00', '2017-09-27 19:00'
UNION ALL SELECT 8, '2017-09-27 08:00', '2017-09-29 12:00'
)
, code_t AS
(
SELECT '12:30' s, '13:10' e
UNION ALL SELECT '17:10', '17:40'
)
, tmp AS
(
SELECT pk, sdt, edt
     , CAST(SUBSTRING(sdt, 1, 10) AS VARCHAR(10)) dt
     , CAST(sdt AS datetime) stm
     , CASE WHEN SUBSTRING(sdt, 1, 10) = SUBSTRING(edt, 1, 10)
            THEN CAST(edt AS datetime)
            ELSE DATEADD(d, 1, SUBSTRING(sdt, 1, 10))
            END etm
FROM work_t
UNION ALL
SELECT pk, sdt, edt
     , CAST(CONVERT(VARCHAR, DATEADD(d, 1, dt), 23) AS VARCHAR(10)) dt
     , etm stm
     , CASE WHEN DATEADD(d, 1, etm) < edt
            THEN DATEADD(d, 1, etm)
            ELSE CAST(edt AS datetime)
        END etm
FROM tmp
WHERE etm < edt
)
SELECT pk, sdt, edt
     , DATEDIFF(mi, sdt, edt) tot_mi
     , SUM(CASE WHEN x > 0 THEN x ELSE 0 END) eat_mi
FROM (SELECT pk, sdt, edt
             , DATEDIFF(mi
             , CASE WHEN CONCAT(dt, ' ', s) >    stm
                    THEN CONCAT(dt, ' ', s) ELSE stm END
             , CASE WHEN CONCAT(dt, ' ', e) <    etm
                    THEN CONCAT(dt, ' ', e) ELSE etm END
             ) x
          FROM tmp
          LEFT OUTER JOIN code_t
            ON stm < CONCAT(dt, ' ', e)
           AND etm > CONCAT(dt, ' ', s)
        ) a
GROUP BY pk, sdt, edt
;

마농(manon94)님이 2017-09-29 11:21에 작성한 댓글입니다.

답변 주신내역은 확인 하였으나 2008애서는 CONCAT 함수가 적용 되지 않습니다.

죄송하지만 2008에서는 어떠한 함수를 써야 하나요?

김알지님이 2017-10-10 09:49에 작성한 댓글입니다. Edit Ｘ

concat 는 문자열을 연결하는 함수이구요. + 를 사용해도 연결 가능합니다.

마농(manon94)님이 2017-10-10 10:12에 작성한 댓글입니다.

[Top]

No.	제목	작성자	작성일	조회
7100	조건절에 재귀쿼리는 쓸수 없나요? [1]	가을이	2017-10-19	2998
7097	날짜 변환 입니다. [3]	도와주세요.	2017-09-29	3139
7096	다중키 사용시 다른 컬럼의 인덱스를 어떻게 생성해야.. [3]	김진호	2017-09-27	3017
7095	mssql 식사시간 구하기 [5]	김알지	2017-09-27	3528
7094	쿼리 부탁드려요 [2]	이수돈	2017-09-25	2679
7093	행별 총합, sub sum, 열끝에는 sum을.. 출력 도움 부탁드립니다~ [2]	개발자	2017-09-18	3020
7092	페이징 쿼리 입니다. 찾다 찾다 그냥 만들었습니다 [2]	박성재	2017-09-08	2975