计算两个细分之间的天数 [英] Count days between two segments

问题描述

我在下面有两个表格.我只想计算从Hire_dt到雇用日期所在的日历月末之间的星期一至星期五的天数.

I have two tables below. I want to count the number of days, Monday-Friday only between Hire_dt and end of calendar month the hire date falls under.

TableA

Hire_DT         Id
 09/26/2018     1

TableCalendar:

TableCalendar:

Date         WorkDay(M-F)      EOM        WorkDay
09/26/2018     Wednesday       9/30/2018    1
09/27/2018     Thursday        09/30/2018   1
09/28/2018     Friday          09/30/2018   1
09/29/2018     Saturday        09/30/2018   0
09/30/2018     Sunday          09/30/2018   0

预期结果

Hire_dt        WorkDaysEndMonth   WorkDaysEndMonth --counting hire_dt
 09/26/2018         2                 3

推荐答案

这里是进行计算的一种方法-无需使用日历表.唯一的输入数据是来自第一个表(ID和HIRE_DATE)的数据，我将其包含在WITH子句中(这不是回答您问题的查询的一部分！).其他所有内容均已计算.我展示了如何计算包括聘用日期在内的天数；如果不需要，请在末尾减去1.

Here is one way to do the calculation - WITHOUT using a calendar table. The only input data is what comes from your first table (ID and HIRE_DATE), which I included in a WITH clause (not part of the query that answers your question!). Everything else is calculated. I show how to compute the number of days INCLUDING the hire date; if you don't need that, subtract 1 at the end.

TRUNC(<date>, 'iw')是<date>所在星期的星期一.该查询计算从星期一到EOM的EOM周中有多少天，但不超过5天(以防EOM可能是星期六或星期日).它对HIRE_DATE进行了类似的计算，但是它计算了从星期一到HIRE_DATE 不包括 HIRE_DATE的天数.最后一部分是在HIRE_DATE星期一至EOM星期一之间的每整周增加5天.

TRUNC(<date>, 'iw') is the Monday of the week of <date>. The query computes how many days are in the EOM week, between Monday and EOM, but no more than 5 (in case EOM may be a Saturday or Sunday). It does a similar calculation for HIRE_DATE, but it counts the days from Monday to HIRE_DATE excluding HIRE_DATE. The last part is adding 5 days for each full week between the Monday of HIRE_DATE and the Monday of EOM.

with
  sample_data(id, hire_date) as (
    select 1, to_date('09/26/2018', 'mm/dd/yyyy') from dual union all
    select 2, to_date('07/10/2018', 'mm/dd/yyyy') from dual
  )
select id, to_char(hire_date, 'Dy mm/dd/yyyy')                      as hire_date,
       to_char(eom, 'Dy mm/dd/yyyy')                                as eom,
       least(5, eom - eom_mon + 1) - least(5, hire_date - hire_mon)
       + (eom_mon - hire_mon) * 5 / 7                               as workdays
from   (
         select id, hire_date, last_day(hire_date) as eom,
                trunc(hire_date, 'iw')             as hire_mon,
                trunc(last_day(hire_date), 'iw')   as eom_mon
         from   sample_data
       )
;

        ID HIRE_DATE               EOM                       WORKDAYS
---------- ----------------------- ----------------------- ----------
         1 Wed 09/26/2018          Sun 09/30/2018                   3
         2 Tue 07/10/2018          Tue 07/31/2018                  16

这篇关于计算两个细分之间的天数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！