database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
PostgreSQL
Firebird
Oracle
Informix
Sybase
ㆍMS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
MS-SQL Q&A 7095 게시물 읽기
No. 7095
mssql 식사시간 구하기
작성자
김알지
작성일
2017-09-27 15:35
조회수
3,145

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

작업 시간 : 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
2632
7097날짜 변환 입니다. [3]
도와주세요.
2017-09-29
2774
7096다중키 사용시 다른 컬럼의 인덱스를 어떻게 생성해야.. [3]
김진호
2017-09-27
2651
7095mssql 식사시간 구하기 [5]
김알지
2017-09-27
3145
7094쿼리 부탁드려요 [2]
이수돈
2017-09-25
2325
7093행별 총합, sub sum, 열끝에는 sum을.. 출력 도움 부탁드립니다~ [2]
개발자
2017-09-18
2668
7092페이징 쿼리 입니다. 찾다 찾다 그냥 만들었습니다 [2]
박성재
2017-09-08
2604
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2023 DSN, All rights reserved.
작업시간: 0.053초, 이곳 서비스는
	PostgreSQL v16.1로 자료를 관리합니다