(Oracle 11g DB) Calculate Number of buisiness days between current time and a date while excluding h-CodePudding

So I have this working SQL script that take a date and returns the age from current time to the given date excluding dates defined in a table called exclude dates

SELECT
    COUNT(*)
FROM
    (
        SELECT
            ROWNUM rnum
        FROM
            all_objects
        WHERE
            ROWNUM <= CAST(current_timestamp AS DATE) - to_date('&2')   1
    )
WHERE
    to_char(to_date('&2')   rnum - 1, 'DY') NOT IN ( 'SAT', 'SUN' )
    AND NOT EXISTS (
        SELECT
            NULL
        FROM
            exclude_dates
        WHERE
            no_work = trunc(to_date('&2')   rnum - 1)
    );

I have a table called

TICKETS

that contains columns named

ID, UPDATED_AT

I want to create a view that uses the above script to return

ID, AGE

where age is the output of the script above.

CodePudding user response：

You code has a few weaknesses.

There is no need for CAST(current_timestamp AS DATE).

If you need the current DATE then simply use TRUNC(SYSDATE)
You don't need to select from all_objects. Better use hierarchical query

SELECT LEVEL as rnum FROM dual CONNECT BY LEVEL <= ...
Using to_date('&2') without a format is usually bad. Either your input value is a string, then you should include the format, e.g. to_date('&2', 'YYYY-MM-DD') or your input value is a DATE, then simply use &2 - never use TO_DATE() on a value which is already a DATE!

Final query could be this one - assuming input parameter is a DATE value:

WITH t AS (
   SELECT LEVEL as d
   FROM dual
   CONNECT BY LEVEL <= TRUNC(SYSDATE) - the_day)
SELECT COUNT(*) AS buisiness_days
FROM t
WHERE TO_CHAR(the_day   d - 1, 'DY', 'NLS_DATE_LANGUAGE = american') NOT IN ('SAT', 'SUN')
   AND NOT EXISTS (
        SELECT 'x'
        FROM exclude_dates
        WHERE no_work = TRUNC(the_day   d - 1)
        )

However, for me it is not clear how you want to provide this as a view! You would need to create a separate view for each input date, or at least create a new view every day.

I would suggest to create a function:

CREATE OR REPLACE FUNCTION buisiness_days(the_date IN DATE) RETURN INTEGER AS
   ret INTEGER;
BEGIN

    WITH t AS (
       SELECT LEVEL as d
       FROM dual
       CONNECT BY LEVEL <= TRUNC(SYSDATE) - the_date)
    SELECT COUNT(*) AS buisiness_days
    INTO ret
    FROM t
    WHERE TO_CHAR(the_date   d - 1, 'DY', 'NLS_DATE_LANGUAGE = american') NOT IN ('SAT', 'SUN')
       AND NOT EXISTS (
            SELECT 'x'
            FROM exclude_dates
            WHERE no_work = TRUNC(the_date   d - 1)
            );
    RETURN ret;
END;

CodePudding user response：

The function will return a list of dates between the date range you provide so the dates don't have to be stored in a table.


CREATE OR REPLACE TYPE nt_date IS TABLE OF DATE;
/

CREATE OR REPLACE FUNCTION generate_dates_pipelined(
  p_from IN DATE,
  p_to   IN DATE
)
  RETURN nt_date PIPELINED DETERMINISTIC
IS
  v_start DATE := TRUNC(LEAST(p_from, p_to));
  v_end   DATE := TRUNC(GREATEST(p_from, p_to));
BEGIN
  LOOP
    PIPE ROW (v_start);
    EXIT WHEN v_start >= v_end;
    v_start := v_start   INTERVAL '1' DAY;
  END LOOP;
  RETURN;
END generate_dates_pipelined;
/

To exclude holidays you need to know what dates they fall on so there needs to be a holiday table.


create table holidays(
          holiday_date DATE not null,
          holiday_name VARCHAR2(20),
          constraint holidays_pk primary key (holiday_date),
          constraint is_midnight check ( holiday_date = trunc ( holiday_date ) )
        );

INSERT into holidays (HOLIDAY_DATE,HOLIDAY_NAME)
        WITH dts as (
          select to_date('25-NOV-2021 00:00:00','DD-MON-YYYY HH24:MI:SS'), 'Thanksgiving 2021' from dual union all
          select to_date('29-NOV-2021 00:00:00','DD-MON-YYYY HH24:MI:SS'), 'Hanukkah 2021' from dual
        )
        SELECT * from dts;

This query will provide the count of days between the range, number of working days and number of holidays in the range.


SELECT   COUNT (*)            AS total_days
,     COUNT ( CASE
              WHEN h.holiday_date IS NULL
              AND  d.column_value - TRUNC (d.column_value, 'IW') < 5
              THEN 'Business Day'
          END
        )            AS business_days
,      COUNT (h.holiday_date)    AS holidays
FROM      generate_dates_pipelined (DATE '2021-11-01', DATE '2021-11-30') d
LEFT JOIN holidays h ON h.holiday_date = d.column_value;

This query will provide a list of dates excluding sat, sun and holidays that fall between the range.


SELECT
        COLUMN_VALUE 
  FROM   
TABLE(generate_dates_pipelined(DATE '2021-11-01',
DATE '2021-11-30')) c
where 
to_char(COLUMN_VALUE, 'DY') NOT IN ('SAT', 'SUN') 
AND NOT EXISTS (
             SELECT 1
             FROM   holidays h
             WHERE  c.COLUMN_VALUE = h.holiday_date
           );

CodePudding user response：

You don't need a function or to use a row generator function and can calculate the number of business days:

CREATE VIEW business_day_ages (ID, AGE) AS
SELECT id,
       ( TRUNC( SYSDATE, 'IW' ) - TRUNC( updated_at, 'IW' ) ) * 5 / 7
       -- Number of full weeks.
         LEAST( SYSDATE - TRUNC( SYSDATE, 'IW' ), 5 )
       -- Add part weeks at the end.
       - LEAST( updated_at - TRUNC( updated_at, 'IW' ), 5 )
       -- Subtract part weeks at the start.
       - COALESCE(
           ( SELECT SUM(
                      LEAST(no_work   INTERVAL '1' DAY, SYSDATE)
                    - GREATEST(no_work, updated_at)
                    )
             FROM   exclude_dates
             WHERE  no_work BETWEEN TRUNC(updated_at) AND SYSDATE
           ),
           0
         )
         -- Subtract the holiday days.
FROM   tickets;

Or, if you are not calculating using part days then you can simplify it to:

CREATE OR REPLACE VIEW business_day_ages (ID, AGE) AS
SELECT id,
       ( TRUNC( SYSDATE, 'IW' ) - TRUNC( updated_at, 'IW' ) ) * 5 / 7
       -- Number of full weeks.
         LEAST( TRUNC(SYSDATE) - TRUNC( SYSDATE, 'IW' ), 5 )
       -- Add part weeks at the end.
       - LEAST( updated_at - TRUNC( updated_at, 'IW' ), 5 )
       -- Subtract part weeks at the start.
       - COALESCE(
           ( SELECT 1
             FROM   exclude_dates
             WHERE  no_work BETWEEN TRUNC(updated_at) AND TRUNC(SYSDATE)
           ),
           0
         )
         -- Subtract the holiday days.
FROM   tickets;

db<>fiddle here