Oracle:两个日期之间的天数和不包括工作日的天数如何处理负数 [英] Oracle: Days between two date and Exclude weekdays how to handle negative numbers

问题描述

我有两个日期列，并尝试测量两个日期之间的天数(不包括周末).我得到一个负数，需要帮助解决.

I have two date columns and trying to measure days between the two dates excluding weekends. I'm getting a negative number and need help solving.

表

CalendarDate    DayNumber  FirstAssgn    FirstCnt   DayNumber2  Id  BusinessDays
5/21/2017        Sunday       5/21/17      5/21/17    Sunday    1     -1

查询:

 TRUNC(TO_DATE(A.FIRST_CONTACT_DT, 'DD/MM/YYYY')) - TRUNC(TO_DATE(A.FIRST_ASSGN_DT, 'DD/MM/YYYY'))
 - ((((TRUNC(A.FIRST_CONTACT_DT,'D'))-(TRUNC(A.FIRST_ASSGN_DT,'D')))/7)*2) 
  - (CASE WHEN TO_CHAR(A.FIRST_ASSGN_DT,'DY','nls_date_language=english') ='SUN' THEN 1 ELSE 0 END)
- (CASE WHEN TO_CHAR(A.FIRST_CONTACT_DT,'DY','nls_date_language=english')='SAT' THEN 1 ELSE 0 END) 
- (SELECT COUNT(1) FROM HUM.CALENDAR CAL
                   WHERE 1=1
                     AND CAL.CALENDAR_DATE >= A.FIRST_ASSGN_DT
                     AND CAL.CALENDAR_DATE < A.FIRST_CONTACT_DT
                     --BETWEEN A.FIRST_ASSGN_DT AND A.FIRST_CONTACT_DT                      
                     AND CAL.GRH_HOLIDAY_IND = 'Y'
                                                  ) AS Business_Days 

看起来像下面的一块需要编辑...

Looks like below piece needs editing...

- (CASE WHEN TO_CHAR(A.FIRST_ASSGN_DT,'DY','nls_date_language=english')='SUN' THEN 1 ELSE 0 END)

推荐答案

改编自在这里我的答案:

获取两个星期的星期一之间的天数(使用TRUNC( datevalue, 'IW' )作为查找星期的星期一的NLS_LANGUAGE独立方法)，然后加上星期几(星期一= 1，星期二= 2， (例如，最多5个，以忽略周末)作为结束日期，并减去开始日期的星期几.像这样:

Get the number of days between the Mondays of both weeks (using TRUNC( datevalue, 'IW' ) as an NLS_LANGUAGE independent method of finding the Monday of the week) then add the day of the week (Monday = 1, Tuesday = 2, etc., to a maximum of 5 to ignore weekends) for the end date and subtract the day of the week for the start date. Like this:

SELECT ( TRUNC( end_date, 'IW' ) - TRUNC( start_date, 'IW' ) ) * 5 / 7
       + LEAST( end_date - TRUNC( end_date, 'IW' ) + 1, 5 )
       - LEAST( start_date - TRUNC( start_date, 'IW' ) + 1, 5 )
          AS WeekDaysDifference
FROM   your_table

这篇关于Oracle:两个日期之间的天数和不包括工作日的天数如何处理负数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！