Re: A better way to code this?

Steve,

I haven't got an answer for you, but I do recall reading a chapter in Joe Celko's new book 'SQL For Smarties' that covered this topic (Between/Overlaps that show either 'a value within a date range', or 'if two time periods overlap in time').

The chapter uses some syntax that may not be 100% compatible with Oracle (i.e. it's only for SQL-92 standards), but it does use *only* SQL (no PL/SQL at all), and the author does go through the thought process in getting to an answer. If you are serious about finding an optimal solution, you may want to check it out. The publisher is Morgan Kaufmann Publishers, Inc.

Gary Wong
Systems By Design, Inc.
Victoria, BC
Canada

>
> Hello:
>
> I am writing a report that will produce a list of employees who
> are ill at either end of a stat holiday or other non-working day.
> Such days are recorded in a table (exception_calendar) and consist
> of a start date and end date (could be the same for one day, or
> span several days).
>
> My algorithm to detect if a given absence date span (again, could
> be one day or several) is at either end of the recorded non-working
> days is:
>

 < snip: >
> Any advice would be appreciated...
>
> --------------< LINUX: The choice of a GNU generation. >---------------
> Steve Frampton http://qlink.queensu.ca/~3srf
> Work: <frampton_at_mail.flarc.edu.on.ca> School: <3srf_at_qlink.queensu.ca>
Received on Thu Jul 04 1996 - 00:00:00 CEST