#sql #oracle #datetime #count #recursive-query
#sql #Oracle #дата-время #подсчет #рекурсивный запрос
Вопрос:
У меня есть SQL-запрос, который содержит три столбца, как показано ниже
employee_id start_date end_date hours
123 09-01-2019 09-02-2019 8
123 09-28-2019 10-01-2019 32
Я хочу переписать запрос, поэтому вместо детализации я просто хочу знать сумму (часы), которую сотрудник имеет на уровне год -месяц, как показано ниже:
employee_id Year_Month hours
123 201909 32
123 201910 8
У сотрудника есть 4 дня в сентябре, так что 4 * 8 = 32 и один день в октябре, так что 8 часов за октябрь месяц. Моя проблема заключается в том, что даты начала и окончания пересекаются между соседними месяцами. Я не уверен, как написать запрос, чтобы получить желаемый результат, и я был бы очень признателен за любую помощь в этом
Комментарии:
1. Не могли бы вы предоставить образец исходных данных.
Ответ №1:
Возможно, было бы проще использовать рекурсивный запрос для генерации серии дней в каждом месяце, затем агрегировать по месяцам и подсчитывать:
with
data as (< your existing query here >),
cte (employee_id, dt, max_dt) as (
select employee_id, start_date, end_date from data
union all
select employee_id, dt 1, max_dt from cte where dt 1 < max_dt
)
select employee_id, to_char(dt, 'yyyymm') year_months, count(*) * 8 hours
from mytable
group by employee_id, to_char(dt, 'yyyymm')
Это предполагает 8 часов в день, как объяснено в вашем вопросе.
Комментарии:
1. В примере я дал 8 часов, но это может варьироваться. таким образом, в идеале это было бы количество дней между двумя датами, умноженное на столбец часов. В таком случае это сработает, если я заменю count( ) * 8 на count ( ) * hours?
2. @PrashanthChitrambalam: вероятно, вы имеете в виду
sum(hours)
тогда. Но в этом случае вам также нужен столбецcte
to returnhours
.