Re: SQL/PL-SQL Question

From: David Hermann <dhe_at_phcs.phcs.com>
Date: 1995/07/13
Message-ID: <3u3ccm$2kk_at_palm.phcs.com>#1/1


In article <3tuhjo$dpc_at_knot.queensu.ca>, 3srf_at_qlink.queensu.ca (Frampton Steve R) writes:
|> Suppose I am given a start date and an end date of an employee absence.
|> For example:
|>
|> Start: 950101 End: 950331
|>
|> With this information, I calculate the total number of working days. In
|> my example, 64 days.
|>
|> I have a table with date ranges, as in the example:
|>
|> Start End Loss
|> 950103 950106 0.0
|> 950120 950127 0.0
|> 950313 950317 0.0
|>
|> The table indicates, that, if an absence spans any of the dates in the
|> table, they should lose "0.0" absence credits (instead of 1.0)...therefore,
|> in my example, insteading of losing 64 absence credits, the employee would
|> only lose 49 credits (64 less 15 non-credit loss days). Please note that
|> my "non-credit loss days" may span non-working days in the table (the
|> January entries wrap over a Saturday/Sunday) and they should *not* be
|> deducted.
|>
|> I would like to know how to find out, given the start and end date, the
|> total number of *working* days the range spans in the non-credit loss
|> table.

I didn't test the following, but I think it conveys the idea.

create or replace function COUNT_WORKING_DAYS   ( p_start date,
    p_end date ) return integer as

--
          -- the index variable to increment from P_START to P_END
test_date     date    not null := p_start;
          -- the count to return
working_days  integer not null := 0;
--
          -- the list of date ranges that somehow overlap
          --   the employee absence
          -- (I assume your date ranges are all well-formed
          --   and non-overlapping)
cursor c  is
  select   start, end
    from   <date table>
    where  (   start   between p_start and p_end
            or end     between p_start and p_end
            or p_end   between start   and end
            or p_start between start   and end  )
  order by  1;
--
BEGIN
          -- outer loop: the relevant date ranges
for r in c  loop
          -- inner loop: increment and test TEST_DATE from its
          --   current value up to the end of the current date range
  loop
    if (    to_char(test_date,'DAY') not in ('SAT','SUN')
        and test_date between r.start and r.end
       )  then
          -- count weekdays in the range
      working_days := working_days + 1;
    end if;
          -- we're done with the current date range when we reach
          --   either its ending date or the end of the employee
          --   absence
    exit when test_date >= least( r.end, p_end );
          -- increment the index variable
    test_date := test_date + 1;
  end loop;
end loop;
--
return( working_days );
END count_working_days;

Hope this helps.
Received on Thu Jul 13 1995 - 00:00:00 CEST

Original text of this message