计算两个细分之间的天数

本文介绍了计算两个细分之间的天数的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我在下面有两个表格.我只想计算从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