SQL: Calculate Workday Hours Between 2 Dates

Kevin,

that works! Thank you very much.

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

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

I tried prefacing it with SITE.

DevBob wrote:

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).

DevBob wrote:

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;

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.)

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()?

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.

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:

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?

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.

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

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

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