Re: SQL/PL-SQL Question
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