#sql #oracle
#sql #Oracle
Вопрос:
Я пытаюсь рассчитать часы обслуживания, прошедшие с момента регистрации заявки. Когда запрос регистрируется, вместе с ним сохраняется временная метка (date_logged). Когда он закрывается, сохраняется другая временная метка (date_closed).
Требуется
Что мне нужно, так это часы, прошедшие между date_logged и текущим datetime (для открытых билетов), или часы между date_logged и date_closed (для закрытых билетов) на основе часов обслуживания отдела, назначенного билету.
Должны быть включены праздничные дни.
Существующая таблица
Часы работы назначенного отдела сохраняются в той же таблице, что и билет. Таблица выглядит примерно так:
| incident_ref | отдел | date_logged | date_closed | sla_mon_start | sla_mon_end | sla_tue_start | sla_tue_end | сла_свадьба_старт | sla_wed_end | sla_thr_start | sla_thr_end | sla_fri_start | sla_fri_end | sla_sat_start | sla_sat_end | sla_sun_start | sla_sun_end |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1660565 | A | 06.01.21 11:30:52 | 01.01.01 07:30:00 | 01.01.01 16:45:00 | 01.01.01 07:30:00 | 01.01.01 16:45 | 01.01.01 07:30:00 | 01.01.01 16:45:00 | 01.01.01 07:30:00 | 01.01.01 16:45:00 | 01.01.01 07:30:00 | 01.01.01 13:00:00 | 01.01.01 00:00:00 | 01.01.01 00:00:00 | 01.01.01 00:00:00 | 01.01.01 00:00:00 | |
| 1660567 | B | 13.01.21 09:14:16 | 01.01.01 07:00:00 | 01.01.01 16:30:00 | 01.01.01 07:00:00 | 01.01.01 16:30:00 | 01.01.01 07:00:00 | 01.01.01 16:30:00 | 01.01.01 07:00:00 | 01.01.01 16:30:00 | 01.01.01 07:00:00 | 01.01.01 16:30:00 | 01.01.01 07:00:00 | 01.01.01 15:00:00 | 01.01.01 00:00:00 | 01.01.01 00:00:00 | |
| 1660558 | C | 31.12.20 07:04:46 | 31.12.20 07:36:59 | 01.01.01 07:00:00 | 01.01.01 16:30:00 | 01.01.01 07:00:00 | 01.01.01 16:30:00 | 01.01.01 07:00:00 | 01.01.01 16:30:00 | 01.01.01 07:00:00 | 01.01.01 16:30:00 | 01.01.01 07:00:00 | 01.01.01 16:30:00 | 01.01.01 07:00:00 | 01.01.01 15:00:00 | 01.01.01 00:00:00 | 01.01.01 00:00:00 |
| 3456789 | D | 01.01.21 09:41:00 | 04.01.21 08:21:00 | 01.01.01 08:00:00 | 01.01.01 15:00:00 | 01.01.01 08:00:00 | 01.01.01 15:00:00 | 01.01.01 08:00:00 | 01.01.01 15:00:00 | 01.01.01 08:00:00 | 01.01.01 15:00:00 | 01.01.01 08:00:00 | 01.01.01 13:00:00 | 01.01.01 00:00:00 | 01.01.01 00:00:00 | 01.01.01 00:00:00 | 01.01.01 00:00:00 |
| 0123456 | D | 02.01.21 13:12:00 | … | … | … | … | … |
У меня есть только разрешение на чтение в этой базе данных, поэтому мне не разрешено изменять существующие таблицы или создавать новые.
Что я пробовал
Основываясь на ответе, который я получил на свой предыдущий вопрос, я попытался решить его, как показано ниже, но таким образом я получаю только ошибку «дата дата не разрешена». Я еще не пробовал включать государственные праздники, потому что остальные уже не работали.
SELECT incident_ref,
date_logged,
Inc_close_date,
TO_CHAR( FLOOR( service_time_seconds / 60 / 60 ), 'FM99990' )
|| ':'
|| TO_CHAR( MOD( FLOOR( service_time_seconds / 60 ), 60 ), 'FM00' )
|| ':'
|| TO_CHAR( MOD( service_time_seconds, 60 ), 'FM00' )
AS "service time [hh:mm]"
FROM (
SELECT inc.incident_ref,
inc.date_logged,
inc.Inc_close_date,
ROUND(
(
-- Calculate the full weeks difference from the start of ISO weeks.
(
TRUNC( COALESCE( Inc_close_date, SYSDATE ), 'IW' )
- TRUNC( date_logged, 'IW' )
) * ( (24*(sla_mon_end-sla_mon_start))
(24*(sla_tue_end-sla_tue_start))
(24*(sla_wed_end-sla_wed_start))
(24*(sla_thr_end-sla_thr_start))
(24*(sla_fri_end-sla_fri_start))
(24*(sla_sat_end-sla_sat_start))
(24*(sla_sun_end-sla_sun_start) )) / (7*24)
-- Add the hours for the full days for the final week.
DECODE(
TRUNC( COALESCE( Inc_close_date, SYSDATE ) )
- TRUNC( COALESCE( Inc_close_date, SYSDATE ), 'IW' ),
0, 0.0,
1, (24*(sla_mon_end-sla_mon_start)),
2, (24*(sla_mon_end-sla_mon_start)) 24*(sla_wed_end-sla_tue_start),
3, (24*(sla_mon_end-sla_mon_start)) 24*(sla_wed_end-sla_tue_start) 24*(sla_wed_end-sla_wed_start),
4, (24*(sla_mon_end-sla_mon_start)) 24*(sla_wed_end-sla_tue_start) 24*(sla_wed_end-sla_wed_start) 24*(sla_thr_end-sla_thr_start),
5, (24*(sla_mon_end-sla_mon_start)) 24*(sla_wed_end-sla_tue_start) 24*(sla_wed_end-sla_wed_start) 24*(sla_thr_end-sla_thr_start) 24*(sla_fri_end-sla_fri_start),
6, (24*(sla_mon_end-sla_mon_start)) 24*(sla_wed_end-sla_tue_start) 24*(sla_wed_end-sla_wed_start) 24*(sla_thr_end-sla_thr_start) 24*(sla_fri_end-sla_fri_start) 24*(sla_sat_end-sla_sat_start)
) / 24
-- Subtract the hours for the full days from the days of the week
-- before the date logged.
- DECODE(
TRUNC( date_logged ) - TRUNC( date_logged, 'IW' ),
0, 0.0,
1, 24*(sla_mon_end-sla_mon_start),
2, 24*(sla_mon_end-sla_mon_start) 24*(sla_wed_end-sla_tue_start),
3, 24*(sla_mon_end-sla_mon_start) 24*(sla_wed_end-sla_tue_start) 24*(sla_wed_end-sla_wed_start),
4, 24*(sla_mon_end-sla_mon_start) 24*(sla_wed_end-sla_tue_start) 24*(sla_wed_end-sla_wed_start) 24*(sla_thr_end-sla_thr_start),
5, 24*(sla_mon_end-sla_mon_start) 24*(sla_wed_end-sla_tue_start) 24*(sla_wed_end-sla_wed_start) 24*(sla_thr_end-sla_thr_start) 24*(sla_fri_end-sla_fri_start),
6, 24*(sla_mon_end-sla_mon_start) 24*(sla_wed_end-sla_tue_start) 24*(sla_wed_end-sla_wed_start) 24*(sla_thr_end-sla_thr_start) 24*(sla_fri_end-sla_fri_start) 24*(sla_sat_end-sla_sat_start)
) / 24
-- Add the hours of the final day
COALESCE(
GREATEST(
LEAST(
COALESCE( Inc_close_date, SYSDATE ),
TRUNC( COALESCE( Inc_close_date, SYSDATE ) )
DECODE(
TRUNC( COALESCE( Inc_close_date, SYSDATE ) )
- TRUNC( COALESCE( Inc_close_date, SYSDATE ), 'IW' ),
0, sla_mon_end,
1, sla_tue_end,
2, sla_wed_end,
3, sla_thr_end,
4, sla_fri_end,
5, sla_sat_end,
6, sla_sun_end
)
)
-
(
TRUNC( COALESCE( Inc_close_date, SYSDATE ) )
DECODE(
TRUNC( COALESCE( Inc_close_date, SYSDATE ) )
- TRUNC( COALESCE( Inc_close_date, SYSDATE ), 'IW' ),
0, sla_mon_start,
1, sla_tue_start,
2, sla_wed_start,
3, sla_thr_start,
4, sla_fri_start,
5, sla_sat_start,
6, sla_sun_start
)
),
0
) / 24,
0
)
-- Subtract the hours of the day before the range starts.
COALESCE(
GREATEST(
LEAST(
date_logged,
date_logged
DECODE(
TRUNC( COALESCE( Inc_close_date, SYSDATE ) )
- TRUNC( COALESCE( Inc_close_date, SYSDATE ), 'IW' ),
0, sla_mon_end,
1, sla_tue_end,
2, sla_wed_end,
3, sla_thr_end,
4, sla_fri_end,
5, sla_sat_end,
6, sla_sun_end
)
)
-
(
date_logged
DECODE(
TRUNC( COALESCE( Inc_close_date, SYSDATE ) )
- TRUNC( COALESCE( Inc_close_date, SYSDATE ), 'IW' ),
0, sla_mon_start,
1, sla_tue_start,
2, sla_wed_start,
3, sla_thr_start,
4, sla_fri_start,
5, sla_sat_start,
6, sla_sun_start
)
),
0
) / 24,
0
)
)
-- Multiply to give seconds rather than fractions of full days.
* 24 * 60 * 60
) AS service_time_seconds
FROM incident inc
);
Ожидаемые результаты
(Заявки те же, что и в таблице выше, если вам интересно узнать об отделах. Текущий столбец datetime приведен только для справки, он не нужен в фактическом результате)
| incident_ref | date_logged | date_closed | время обслуживания [чч: мм] | текущая дата и время |
|---|---|---|---|---|
| 1660565 | 31.12.20 07:15:48 | 131:54 | 22.01.2021 10:09 | |
| 1660567 | 31.12.20 07:17:56 | 160:21 | 22.01.2021 10:09 | |
| 1660558 | 31.12.20 07:04:46 | 31.12.20 07:36:59 | 00:32 | 22.01.2021 10:09 |
| 3456789 | 01.01.21 09:41:00 | 04.01.21 08:21:00 | 00:21 | 22.01.2021 10:09 |
С последним тикетом вы можете видеть, что назначенный отдел не работал в новом году.
Ответ №1:
Может быть, это поможет:
WITH
sample AS -- sample data with DATE_CLOSED defined
(
SELECT DISTINCT
INCIDENT_REF,
DEPARTMENT, SLA_MON_START, SLA_MON_END, SLA_TUE_START, SLA_TUE_END, SLA_WED_START, SLA_WED_END, SLA_THR_START, SLA_THR_END, SLA_FRI_START, SLA_FRI_END, SLA_SAT_START, SLA_SAT_END, SLA_SUN_START, SLA_SUN_END,
DATE_LOGGED,
To_Char(DATE_LOGGED, 'hh:mi') "TIME_LOGGED",
Nvl(DATE_CLOSED, To_Date('01.22.2021 10:09', 'mm.dd.yyyy hh24:mi')) "DATE_CLOSED",
To_Char(Nvl(DATE_CLOSED, To_Date('01.22.2021 10:09', 'mm.dd.yyyy hh24:mi')), 'hh:mi') "TIME_CLOSED"
FROM
SAMPLEDATA s
),
days AS -- generate all the missing dates with the service hours depending on a day of week - calculate effective service time (hours) by date
(
SELECT DISTINCT
INCIDENT_REF "INCIDENT_REF",
DEPARTMENT "DEPARTMENT",
DATE_LOGGED "DATE_LOGGED",
DATE_LOGGED LEVEL - 1 "WRK_DATE",
CASE
WHEN To_Char(DATE_LOGGED LEVEL - 1, 'd') = '1' THEN To_Char(SLA_MON_START, 'hh24:mi') || '-' || To_Char(SLA_MON_END, 'hh24:mi')
WHEN To_Char(DATE_LOGGED LEVEL - 1, 'd') = '2' THEN To_Char(SLA_TUE_START, 'hh24:mi') || '-' || To_Char(SLA_TUE_END, 'hh24:mi')
WHEN To_Char(DATE_LOGGED LEVEL - 1, 'd') = '3' THEN To_Char(SLA_WED_START, 'hh24:mi') || '-' || To_Char(SLA_WED_END, 'hh24:mi')
WHEN To_Char(DATE_LOGGED LEVEL - 1, 'd') = '4' THEN To_Char(SLA_THR_START, 'hh24:mi') || '-' || To_Char(SLA_THR_END, 'hh24:mi')
WHEN To_Char(DATE_LOGGED LEVEL - 1, 'd') = '5' THEN To_Char(SLA_FRI_START, 'hh24:mi') || '-' || To_Char(SLA_FRI_END, 'hh24:mi')
WHEN To_Char(DATE_LOGGED LEVEL - 1, 'd') = '6' THEN To_Char(SLA_SAT_START, 'hh24:mi') || '-' || To_Char(SLA_SAT_END, 'hh24:mi')
WHEN To_Char(DATE_LOGGED LEVEL - 1, 'd') = '7' THEN To_Char(SLA_SUN_START, 'hh24:mi') || '-' || To_Char(SLA_SUN_END, 'hh24:mi')
END "SERVICE_HOURS_SPAN",
--
CASE
WHEN To_Char(DATE_LOGGED LEVEL - 1, 'd') = '1' THEN (SLA_MON_END - SLA_MON_START) * 24
WHEN To_Char(DATE_LOGGED LEVEL - 1, 'd') = '2' THEN (SLA_TUE_END - SLA_TUE_START) * 24
WHEN To_Char(DATE_LOGGED LEVEL - 1, 'd') = '3' THEN (SLA_WED_END - SLA_WED_START) * 24
WHEN To_Char(DATE_LOGGED LEVEL - 1, 'd') = '4' THEN (SLA_THR_END - SLA_THR_START) * 24
WHEN To_Char(DATE_LOGGED LEVEL - 1, 'd') = '5' THEN (SLA_FRI_END - SLA_FRI_START) * 24
WHEN To_Char(DATE_LOGGED LEVEL - 1, 'd') = '6' THEN (SLA_SAT_END - SLA_SAT_START) * 24
WHEN To_Char(DATE_LOGGED LEVEL - 1, 'd') = '7' THEN (SLA_SUN_END - SLA_SUN_START) * 24
END "SCHEDULED_SERVICE_HOURS",
--
CASE
WHEN TRUNC(DATE_LOGGED, 'dd') = TRUNC(DATE_LOGGED LEVEL - 1, 'dd') And To_Char(DATE_LOGGED LEVEL - 1, 'd') = '1' And TRUNC(DATE_LOGGED, 'dd') <> TRUNC(Nvl(DATE_CLOSED, To_Date('01.22.2021', 'mm.dd.yyyy')), 'dd') THEN (SLA_MON_END - To_Date(To_Char(SLA_MON_END, 'mm.dd.yyyy') || ' ' || TIME_LOGGED, 'mm.dd.yyyy hh24:mi')) * 24
WHEN TRUNC(DATE_LOGGED, 'dd') = TRUNC(DATE_LOGGED LEVEL - 1, 'dd') And To_Char(DATE_LOGGED LEVEL - 1, 'd') = '2' And TRUNC(DATE_LOGGED, 'dd') <> TRUNC(Nvl(DATE_CLOSED, To_Date('01.22.2021', 'mm.dd.yyyy')), 'dd') THEN (SLA_TUE_END - To_Date(To_Char(SLA_TUE_END, 'mm.dd.yyyy') || ' ' || TIME_LOGGED, 'mm.dd.yyyy hh24:mi')) * 24
WHEN TRUNC(DATE_LOGGED, 'dd') = TRUNC(DATE_LOGGED LEVEL - 1, 'dd') And To_Char(DATE_LOGGED LEVEL - 1, 'd') = '3' And TRUNC(DATE_LOGGED, 'dd') <> TRUNC(Nvl(DATE_CLOSED, To_Date('01.22.2021', 'mm.dd.yyyy')), 'dd') THEN (SLA_WED_END - To_Date(To_Char(SLA_WED_END, 'mm.dd.yyyy') || ' ' || TIME_LOGGED, 'mm.dd.yyyy hh24:mi')) * 24
WHEN TRUNC(DATE_LOGGED, 'dd') = TRUNC(DATE_LOGGED LEVEL - 1, 'dd') And To_Char(DATE_LOGGED LEVEL - 1, 'd') = '4' And TRUNC(DATE_LOGGED, 'dd') <> TRUNC(Nvl(DATE_CLOSED, To_Date('01.22.2021', 'mm.dd.yyyy')), 'dd') THEN (SLA_THR_END - To_Date(To_Char(SLA_THR_END, 'mm.dd.yyyy') || ' ' || TIME_LOGGED, 'mm.dd.yyyy hh24:mi')) * 24
WHEN TRUNC(DATE_LOGGED, 'dd') = TRUNC(DATE_LOGGED LEVEL - 1, 'dd') And To_Char(DATE_LOGGED LEVEL - 1, 'd') = '5' And TRUNC(DATE_LOGGED, 'dd') <> TRUNC(Nvl(DATE_CLOSED, To_Date('01.22.2021', 'mm.dd.yyyy')), 'dd') THEN (SLA_FRI_END - To_Date(To_Char(SLA_FRI_END, 'mm.dd.yyyy') || ' ' || TIME_LOGGED, 'mm.dd.yyyy hh24:mi')) * 24
WHEN TRUNC(DATE_LOGGED, 'dd') = TRUNC(DATE_LOGGED LEVEL - 1, 'dd') And To_Char(DATE_LOGGED LEVEL - 1, 'd') = '6' And TRUNC(DATE_LOGGED, 'dd') <> TRUNC(Nvl(DATE_CLOSED, To_Date('01.22.2021', 'mm.dd.yyyy')), 'dd') THEN (SLA_SAT_END - To_Date(To_Char(SLA_SAT_END, 'mm.dd.yyyy') || ' ' || TIME_LOGGED, 'mm.dd.yyyy hh24:mi')) * 24
WHEN TRUNC(DATE_LOGGED, 'dd') = TRUNC(DATE_LOGGED LEVEL - 1, 'dd') And To_Char(DATE_LOGGED LEVEL - 1, 'd') = '7' And TRUNC(DATE_LOGGED, 'dd') <> TRUNC(Nvl(DATE_CLOSED, To_Date('01.22.2021', 'mm.dd.yyyy')), 'dd') THEN (SLA_SUN_END - To_Date(To_Char(SLA_SUN_END, 'mm.dd.yyyy') || ' ' || TIME_LOGGED, 'mm.dd.yyyy hh24:mi')) * 24
--
WHEN TRUNC(DATE_LOGGED, 'dd') = TRUNC(Nvl(DATE_CLOSED, To_Date('01.22.2021', 'mm.dd.yyyy')), 'dd') THEN (DATE_CLOSED - DATE_LOGGED) * 24
ELSE
CASE
WHEN To_Char(DATE_LOGGED LEVEL - 1, 'd') = '1' THEN (SLA_MON_END - SLA_MON_START) * 24
WHEN To_Char(DATE_LOGGED LEVEL - 1, 'd') = '2' THEN (SLA_TUE_END - SLA_TUE_START) * 24
WHEN To_Char(DATE_LOGGED LEVEL - 1, 'd') = '3' THEN (SLA_WED_END - SLA_WED_START) * 24
WHEN To_Char(DATE_LOGGED LEVEL - 1, 'd') = '4' THEN (SLA_THR_END - SLA_THR_START) * 24
WHEN To_Char(DATE_LOGGED LEVEL - 1, 'd') = '5' THEN (SLA_FRI_END - SLA_FRI_START) * 24
WHEN To_Char(DATE_LOGGED LEVEL - 1, 'd') = '6' THEN (SLA_SAT_END - SLA_SAT_START) * 24
WHEN To_Char(DATE_LOGGED LEVEL - 1, 'd') = '7' THEN (SLA_SUN_END - SLA_SUN_START) * 24
END
END "EFF_SERVICE_HOURS",
--
DATE_CLOSED "DATE_CLOSED"
FROM
sample
WHERE
TRUNC(DATE_LOGGED, 'dd') <= TRUNC(Nvl(DATE_CLOSED, To_Date('01.22.2021 10:09', 'mm.dd.yyyy hh:mi')), 'dd')
CONNECT BY
DATE_LOGGED LEVEL - 1 <= DATE_CLOSED
ORDER BY
DEPARTMENT, DATE_LOGGED LEVEL - 1
)
SELECT -- Get total service time as hh24:mi
INCIDENT_REF "INCIDENT_REF",
DEPARTMENT "DEPARTMENT",
To_Char(DATE_LOGGED, 'MON-dd-yyyy hh24:mi') "DATE_LOGGED",
To_Char(DATE_CLOSED, 'MON-dd-yyyy hh24:mi') "DATE_CLOSED",
LPAD(FLOOR(Sum(EFF_SERVICE_HOURS * 60) / 60), 2, '0') || ':' || LPAD(Round((Sum(EFF_SERVICE_HOURS) - FLOOR(Sum(EFF_SERVICE_HOURS * 60) / 60)) * 60, 0), 2, '0') "TOTAL_SERVICE_TIME"
FROM
days
GROUP BY
INCIDENT_REF,
DEPARTMENT,
DATE_LOGGED,
DATE_CLOSED
ORDER BY
DEPARTMENT,
INCIDENT_REF