MSSQL 질문드립니다. (고수분들 도와주세요)

본문 바로가기

MSSQL 질문드립니다. (고수분들 도와주세요)

작성일 2023.03.18댓글 1건
    게시물 수정 , 삭제는 로그인 필요

WORK_TIME 이라는 근무시간 테이블이 있습니다.
이 테이블에는 START_TIME 컬럼과 END_TIME 컬럼이 있고, ROW는 5개의 행이 있습니다.
START_TIME END_TIME 
08:30          10:30
10:45          12:30
13:30          15:45
16:00          18:15
18:45          20:30


저는 여기서 WORK_TIME 테이블을 이용해서 각 시간대별 초 단위 근무시간 합계는 아래가 되겠죠.

08시 : 08:30:00 ~ 08:59:59 -> 1799
09시 : 09:00:00 ~ 09:59:59 -> 3599
10시 : 10:00:00 ~ 10:30:00 + 10:45:00 ~ 10:59:59 -> 2699
11시 : 11:00:00 ~ 11:59:59 ->  3599
12시: 12:00:00 ~ 12:30:00 -> 1800
13시 : 13:30:00 ~ 13:59:59 -> 3599
14시 : 14:00:00 ~ 14:59:59 -> 3599
15시 : 15:00:00 ~ 15:45:00 -> 2700
16시 : 16:00:00 ~ 16:59:59 -> 3599
17시 : 17:00:00 ~ 17:59:59 -> 3599
18시 : 18:00:00 ~ 18:15:00 + 18:45:00 ~ 18:59:59 -> 1799
19시 : 19:00:00 ~ 19:59:59 -> 3599
20시 : 20:00:00 ~ 20:30:00 -> 1800





(식은 이해를 돕게.)
여기서 저는, 현재시간이 08시 40분이면
08시 : 08:30:00 ~ 08:40 -> 600


또는 현재시간이 10시 55분 이라고 하면 이전 근무시간과  같이 아래처럼 

08시 : 08:30:00 ~ 08:59:59 -> 1799
09시 : 09:00:00 ~ 09:59:59 -> 3599
10시 : 10:00:00 ~ 10:30:00 + 10:45:00 ~ 10:55:00 -> 2400



또는 현재시간이 18:20분일 경우

08시 : 08:30:00 ~ 08:59:59 -> 1799
09시 : 09:00:00 ~ 09:59:59 -> 3599
10시 : 10:00:00 ~ 10:30:00 + 10:45:00 ~ 10:59:59 -> 2699
11시 : 11:00:00 ~ 11:59:59 ->  3599
12시: 12:00:00 ~ 12:30:00 -> 1800
13시 : 13:30:00 ~ 13:59:59 -> 1799
14시 : 14:00:00 ~ 14:59:59 -> 3599
15시 : 15:00:00 ~ 15:45:00 -> 2700
16시 : 16:00:00 ~ 16:59:59 -> 3599
17시 : 17:00:00 ~ 17:59:59 -> 3599
18시 : 18:00:00 ~ 18:15:00 -> 900


ㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡ
아래는 제가 만들었던 쿼리입니다.
도저히 못해먹겠어서 도움 요청드리니 참고하셔서 만들어주시면 감사드리겠습니다.

결과값으로는 아래와 같이 나오게 되고, 여기서 임의의 시간을 변수로 받아서 위에처럼 계산되었으면 합니다.

08시 1799
09시 3599
10시 2699
11시 3599
12시 1800
13시 1799
14시 3599
15시 2700
16시 3599
17시 3599
18시 1799
19시 3599
20시 1800



WITH time_slots (slot_start, slot_end) AS (
  SELECT '08:00:00', '08:59:59'
  UNION ALL
  SELECT '09:00:00', '09:59:59'
  UNION ALL
  SELECT '10:00:00', '10:59:59'
  UNION ALL
  SELECT '11:00:00', '11:59:59'
  UNION ALL
  SELECT '12:00:00', '12:59:59'
  UNION ALL
  SELECT '13:00:00', '13:59:59'
  UNION ALL
  SELECT '14:00:00', '14:59:59'
  UNION ALL
  SELECT '15:00:00', '15:59:59'
  UNION ALL
  SELECT '16:00:00', '16:59:59'
  UNION ALL
  SELECT '17:00:00', '17:59:59'
  UNION ALL
  SELECT '18:00:00', '18:59:59'
  UNION ALL
  SELECT '19:00:00', '19:59:59'
  UNION ALL
  SELECT '20:00:00', '20:59:59'
),
work_hours AS (
  SELECT 
    CAST(START_TIME AS DATETIME) AS start_time, 
    CAST(END_TIME AS DATETIME) AS end_time,
    DATEDIFF(SECOND, START_TIME, END_TIME) AS diff
  FROM WORK_TIME
)
SELECT 
  CONCAT(LEFT(time_slots.slot_start, 2), '시') AS time_slot,
  COALESCE(
    CONVERT(VARCHAR, SUM(
      CASE 
        WHEN work_hours.start_time < time_slots.slot_start AND work_hours.end_time > time_slots.slot_end THEN DATEDIFF(SECOND, time_slots.slot_start, time_slots.slot_end)
        WHEN work_hours.start_time >= time_slots.slot_start AND work_hours.end_time <= time_slots.slot_end THEN DATEDIFF(SECOND, work_hours.start_time, work_hours.end_time)
        WHEN work_hours.start_time < time_slots.slot_start AND work_hours.end_time > time_slots.slot_start THEN DATEDIFF(SECOND, time_slots.slot_start, work_hours.end_time)
        WHEN work_hours.start_time < time_slots.slot_end AND work_hours.end_time > time_slots.slot_end THEN DATEDIFF(SECOND, work_hours.start_time, time_slots.slot_end)
      END
    )), '00:00:00'
  ) AS total_work_time
FROM time_slots
LEFT JOIN work_hours
ON 1=1
GROUP BY time_slots.slot_start, time_slots.slot_end
ORDER BY time_slots.slot_start;



profile_image 익명 작성일 -

아래와 같이 쿼리를 수정하여 구하실 수 있습니다.

​

DECLARE @currentTime DATETIME = '2022-06-14 13:00:00';

​

WITH time_slots (slot_start, slot_end) AS (

SELECT '08:00:00', '08:59:59'

UNION ALL

SELECT '09:00:00', '09:59:59'

UNION ALL

SELECT '10:00:00', '10:59:59'

UNION ALL

SELECT '11:00:00', '11:59:59'

UNION ALL

SELECT '12:00:00', '12:59:59'

UNION ALL

SELECT '13:00:00', '13:59:59'

UNION ALL

SELECT '14:00:00', '14:59:59'

UNION ALL

SELECT '15:00:00', '15:59:59'

UNION ALL

SELECT '16:00:00', '16:59:59'

UNION ALL

SELECT '17:00:00', '17:59:59'

UNION ALL

SELECT '18:00:00', '18:59:59'

UNION ALL

SELECT '19:00:00', '19:59:59'

UNION ALL

SELECT '20:00:00', '20:59:59'

),

work_hours AS (

SELECT

CAST(START_TIME AS DATETIME) AS start_time,

CAST(END_TIME AS DATETIME) AS end_time,

DATEDIFF(SECOND, START_TIME, END_TIME) AS diff

FROM WORK_TIME

)

SELECT

CONCAT(LEFT(time_slots.slot_start, 2), '시') AS time_slot,

COALESCE(

CONVERT(VARCHAR, SUM(

CASE

WHEN work_hours.start_time < time_slots.slot_start AND work_hours.end_time > time_slots.slot_end THEN DATEDIFF(SECOND, time_slots.slot_start, time_slots.slot_end)

WHEN work_hours.start_time >= time_slots.slot_start AND work_hours.end_time <= time_slots.slot_end THEN DATEDIFF(SECOND, work_hours.start_time, work_hours.end_time)

WHEN work_hours.start_time < time_slots.slot_start AND work_hours.end_time > time_slots.slot_start THEN DATEDIFF(SECOND, time_slots.slot_start, work_hours.end_time)

WHEN work_hours.start_time < time_slots.slot_end AND work_hours.end_time > time_slots.slot_end THEN DATEDIFF(SECOND, work_hours.start_time, time_slots.slot_end)

END

)), '00:00:00'

) AS total_work_time

FROM time_slots

LEFT JOIN work_hours

ON 1=1

WHERE time_slots.slot_start <= DATEADD(SECOND, DATEDIFF(SECOND, '00:00:00', @currentTime), '1900-01-01')

GROUP BY time_slots.slot_start, time_slots.slot_end

ORDER BY time_slots.slot_start;

​

쿼리에서는 @currentTime 변수를 통해 현재 시간을 입력받아 계산합니다. 따라서 @currentTime 변수를 선언하고 해당 시간을 입력하시면 원하시는 결과를 얻을 수 있습니다.

연관된 질문 답변 리스트

ㅠ 고수님들 도와주세요... 내공100

테이블에 SERIAL_NO 가 다음같이 있다면 옆에 COUNT 라는 컬럼을 만들어서 중복된 숫자를 표현하고 싶습니다. 예를들어 SERIAL_NO COUNT DD1855 1 1 XD1200 2...

C# 에서 MS-SQL 연동 도와주세요

... 몰라서 질문 올립니다. ㅠ Windows 인증으로는 MS-SQL 연동이 불가능하다고... 부탁드립니다 C# 고수님들 도와주세요 일단 윈도우 계정으로 로그인 하시고...

mssql 쿼리 제발 도와주세요

고수님들 도와주세요.ㅠ mssql 쿼리 질문드립니다. mssql server 2008 사용하고있습니다. 아래와 같은... 간단하게 MS-SQL 에서 지원하는 함수로 가능합니다....

ㅠㅠ 고수님들 도와주세요.

SELECT 문에서 CASE WHEN 을 쓸때 PAYAMT 가 NULL이면 0으로 AMT값을 받고싶습니다 . GROUP BY를 써서 그런지 원인은 모르겠으나 아래 문법처럼 ISNULL을...

mssql

MSSQL 고수님들 답변 부탁드립니다 .

... 여기서 질문 드리자면 1. 데이터베이스 복사... 드립니다 . 일단 에러를 수정하는 방법 ------------------------------------------------------------------- MS-SQL 2000...

ASP 고수님들 도와주세요. mssql...

... 자세한 답변 꼭좀 부탁드립니다. <% Set db = Server.CreateObject("ADODB.Connection") db.Open("WebDB") SQL = "Select Top 5 * From notice Order By writeday Desc...