计算给定开始日期和工作/午餐时间的截止日期PL/SQL [英] Due Date Calculated Given Start Date and Working/Lunch Hours PL/SQL

问题描述

我需要一些PL/SQL帮助.鉴于最长时间为8个工作小时，并且此时间中不包括从12 pm-1pm开始的午餐，因此我正在尝试计算给定任务的截止日期. 例如，如果任务在上午8点进来，则应在下午5点到期.如果任务在上午11点进来，则应在第二天上午11点到期.我以为这会很容易，但是我是PL/SQL的新手，并且刚刚开始.我的想法是使用sysdate，但是那行不通.请为不发布代码提供帮助和歉意，但是我没有任何帮助的地方.

I need some PL/SQL help. I am trying to calculate a due date for a given task, given that the max time is 8 business hours and lunch from 12pm-1pm is not included in this time. For example, If a task comes in at 8am, it is due 5pm. If a task comes in at 11am, it is due 11am next day. I thought this would be fairly easy but I am new to PL/SQL and have just started. My thought process was to use sysdate but that is not going to work. Please help and apologies for not posting code, but I have not got anything that would help.

推荐答案

请记住以下内容，对于您来说，以下内容应该是一个不错的起点，请根据需要进行调整.

Messing around with this, the following should be a good starting point for you, pls tweak as you see fit.

它需要一个开始日期和工作时间，然后返回截止日期.请注意，它四舍五入到最接近的小时数(因此，如果开始日期/时间是今天的下午3:45，那么它将使用4pm作为实际开始日期/时间):

It takes a starting date and number of work hours, and returns the due date. Note that it rounds to the nearest hour (so if start date/time is today at 3:45pm, then it uses 4pm as the actual start date/time):

create or replace function get_due_date(i_start_date in date default sysdate, i_hours in number default 8)
return date
as
    l_dte date;
    l_hours number;
begin

    if (i_hours < 1 OR i_hours > 24) then
        raise_application_error(-20001, 'Hours must be between 1 and 24');
    end if;

    l_hours := i_hours + 1;

    select dte 
    into l_dte
    from 
    (
        select dte, to_char(dte, 'HH24') hr, levl, row_number() over(order by levl) rnum
        from (
            -- rounding to nearest hour
            select round(i_start_date, 'HH24') + ((level-1)/24) as dte, level levl
            from dual
            connect by level <= 72
        )
        where to_char(dte, 'HH24') between '08' and '17'
        -- skip lunch hour
        and NOT(to_char(dte, 'HH24') = '12')
    ) x
    where rnum = l_hours;

    return l_dte;

end;

例如:

select sysdate, get_due_date(sysdate, 8) from dual;

输出:

4/10/2015 3:45:21 PM    4/11/2015 3:00:00 PM

这篇关于计算给定开始日期和工作/午餐时间的截止日期PL/SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！