Solved

SQL: Calculate Workday Hours Between 2 Dates


Userlevel 2
Badge +8

We have need to calculate the number of hours between 2 dates, minus weekends. We’re using the OrderHistory tab’s rows.

I have it working for just the number of days between 2 dates, and then did it for the number of hours (I simply removed TRUNC() around the 2 dates).

But that included weekends and now the team wants just business days. I was able find a solution for 2 dates, but it doesn’t include the hours.

I’m thinking the solution is to add HH24:MM:SS to the to_date() function but am receiving errors.

How do I format to_date() to include hours in the calculation?

 

SELECT
Site,
OrderNo,
status,
lag_status,
dt,
lag_dt,
TO_CHAR(to_date(dt,'YYYY/MM/DD'),'DY','nls_date_language=english') as DT_DAY,
TO_CHAR(to_date(lag_dt,'YYYY/MM/DD'),'DY','nls_date_language=english') as LAG_DT_DAY,

(to_date(dt,'YYYY/MM/DD') - to_date(lag_dt,'YYYY/MM/DD') ) -
((((TRUNC(to_date(dt,'YYYY/MM/DD'),'D'))-(TRUNC(to_date(lag_dt,'YYYY/MM/DD'),'D')))/7)*2) -

(CASE WHEN TO_CHAR(to_date(lag_dt,'YYYY/MM/DD'),'DY','nls_date_language=english')='SUN' THEN 1 ELSE 0 END) -

(CASE WHEN TO_CHAR(to_date(dt,'YYYY/MM/DD'),'DY','nls_date_language=english')='SAT' THEN 1 ELSE 0 END) as Bus_Days_Diff

from ifsinfo.RW_OUT_ORDERS_HIST_ONE_LAG

 

icon

Best answer by durette 9 January 2021, 01:00

Your should have a calendar set up under Site from which you can exclude non-working days.

 

Here’s how to use the distribution calendar to achieve this. (On our system, this example shows that 5 working days separate last Wednesday and this Wednesday.)

SELECT work_time_calendar_api.get_work_days_between(
calendar_id_ => site_api.get_dist_calendar_id('YOUR SITE'),
from_date_ => TO_DATE('2021-01-06', 'YYYY-MM-DD'),
to_date_ => TO_DATE('2021-01-13', 'YYYY-MM-DD'))
FROM DUAL;

 

View original

14 replies

Userlevel 6
Badge +14

Your should have a calendar set up under Site from which you can exclude non-working days.

 

Here’s how to use the distribution calendar to achieve this. (On our system, this example shows that 5 working days separate last Wednesday and this Wednesday.)

SELECT work_time_calendar_api.get_work_days_between(
calendar_id_ => site_api.get_dist_calendar_id('YOUR SITE'),
from_date_ => TO_DATE('2021-01-06', 'YYYY-MM-DD'),
to_date_ => TO_DATE('2021-01-13', 'YYYY-MM-DD'))
FROM DUAL;

 

Userlevel 2
Badge +8

Kevin,

that works! Thank you very much.

 

Userlevel 2
Badge +8

I then tried this to also include the number of hours in addition to the number of days, however that fails.

work_time_calendar_api.get_work_days_between(site_api.get_dist_calendar_id(site),
TO_DATE(lag_dt, 'YYYY-MM-DD HH:MM:SS'),TO_DATE(dt, 'YYYY-MM-DD HH:MM:SS')) as BusDays_API

 

Userlevel 2
Badge +8

I also get an error when I try to put that API in an ILA:

 

I tried prefacing it with SITE.

 

 

Userlevel 7

I also get an error when I try to put that API in an ILA:

 

I tried prefacing it with SITE.

 

 

You need to prefix the function calls in the IAL with &AO.. or &IAL.. (like it says in the instructions on the left side of the screen).

I then tried this to also include the number of hours in addition to the number of days, however that fails.

work_time_calendar_api.get_work_days_between(site_api.get_dist_calendar_id(site),
TO_DATE(lag_dt, 'YYYY-MM-DD HH:MM:SS'),TO_DATE(dt, 'YYYY-MM-DD HH:MM:SS')) as BusDays_API

 

You could use work_time_calendar_api.Get_Work_Minutes_Between to get the minutes between two dates in the calendar.

SELECT work_time_calendar_api.Get_Work_Minutes_Between (
calendar_id_ => site_api.get_dist_calendar_id('AE010'),
from_time_ => TO_DATE('2021-01-06', 'YYYY-MM-DD'),
to_time_ => TO_DATE('2021-01-13', 'YYYY-MM-DD'))
FROM DUAL;

 

Userlevel 4
Badge +6

To solve the problem you have with adding HH24:MI:SS to your SQL-statements we need to know the data type of columns DT and LAG_DT in the IAL RW_OUT_ORDERS_HIST_ONE_LAG.

If it is VARCHAR2 or any other string data type we need to know the format of the data. (If data type is DATE there is no specific format.)

Userlevel 2
Badge +8

here’s the IAL:

SELECT 
Site,
OrderNo,
OrderStatus,
Status,

TO_CHAR(StatusDate,'yyyy-mm-dd') dt,

LAG(Status) OVER(PARTITION BY OrderNo ORDER BY OrderNo, StatusDate) lag_status,

TO_CHAR(LAG(StatusDate) OVER(PARTITION BY OrderNo ORDER BY OrderNo, StatusDate),'yyyy-mm-dd') lag_dt,

StatusDate - LAG(StatusDate) OVER(PARTITION BY OrderNo ORDER BY OrderNo, StatusDate) diff

FROM ifsinfo.RW_OUT_ORDERS_HIST_ONE_STS coh


order by Site, OrderNo, Status

here’s some resulting data:

 

So are you saying in order to get HH:MM:SS we should use VARCHAR2()  instead of TO_CHAR()?

Userlevel 2
Badge +8

Thanks for the minutes API, that could be helpful.

The example doesn’t have HH:MM:SS included in the example so it’s not useful; we need # of hours between two full dates YYYY-MM-DD HH:MM:SS, excluding weekends.

Userlevel 4
Badge +6

Good information, now I see the problem.

The columns DT and LAG_DT in the IAL are already strings on format YYYY-MM-DD so they do not contain the timestamp.

The cleanest way seems to be to remove the to_char in the IAL to get the two columns as data type DATE.

SELECT  Site,  OrderNo,  OrderStatus,  Status,
StatusDate dt,
LAG(Status) OVER(PARTITION BY OrderNo ORDER BY OrderNo, StatusDate) lag_status,
LAG(StatusDate) OVER(PARTITION BY OrderNo ORDER BY OrderNo, StatusDate) lag_dt,
StatusDate - LAG(StatusDate) OVER(PARTITION BY OrderNo ORDER BY OrderNo, StatusDate) diff
FROM ifsinfo.RW_OUT_ORDERS_HIST_ONE_STS coh
ORDER BY Site, OrderNo, Status

 

Then you do not need the to_date in your SQL and can send in the columns directly into “Work_Time_Calendar_API.Get_Work_Minutes_Between”.

 

SELECT &AO.Work_Time_Calendar_API.Get_Work_Minutes_Between(&AO.Site_API.Get_Dist_Calendar_Id(site), lag_dt, dt)

FROM IFSINFO.RW_OUT_ORDERS_HIST_ONE_LAG

 

Side note:

What I meant with VARCHAR2 was an example of a string data type. All columns in your IAL view has a data type and if you do a to_char on a column with data type DATE the data type will be VARCHAR2. 

Example:

 

 

 

Userlevel 2
Badge +8

awesome, thx!

I tried it and it works, however it looks like the minutes are low. Look at the first row. There are 17.6 days (including weekends); then 13 work days w/o weekends, yet the Hours (I took the minutes / 60) is only 111.18, when it should be around 13.6 * 24 = 326.4 hours.

What am I missing?

 

Userlevel 4
Badge +6

The problem is that the calendar you have as Distribution Calendar in your site does only have work time for 8 hours a day. 

You could check and see on the site which calendar you have and then go to this window to see the generated work minutes per day:

 

You must use another calendar which has 24 hours per day.

Userlevel 2
Badge +8

great, thx. Turns out we do have a calendar like that- named ‘5-24’. I plugged that in and now it works. Thx so much!

ROUND(work_time_calendar_api.get_work_minutes_between('5-24',lag_dt,dt) / 60, 2) as Bus_Hrs_API

 

 

 

Userlevel 2
Badge +8

When I try to put the API call in the IAL I get this error. I do have it prefaced with &A)..

 

 

Userlevel 2
Badge +8

I figured it out; I need two periods, not one, after &AO.

Reply