A better way to code this?

From: Frampton Steve R <3srf_at_qlink.queensu.ca>
Date: 1996/07/02
Message-ID: <4rbc9e$n05_at_knot.queensu.ca>


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:

IF (absn_start_date = day_before_non_working_start_range

   or day_after_non_working_start_range

        or day_before_non_working_end_range
            or day_after_non_working_end_range)
OR

   (absn_end_date = day_before_non_working_start_range    or day_after_non_working_start_range

        or day_before_non_working_end_range
            or day_after_non_working_end_date)
THEN
  It's a reportable day.

<whew!> We need to consider all cases here...of course, including discounting of weekends.

The way I accomplished this was to use a whole mess of code using UNION statements. As somebody interested in both improving performance, my style, and as an admirer of elegant code, I am not happy nor convinced that my solution is the best one. I have included the code below. I'm limited to working in straight SQL*Plus, as I need something workable in SQL, or RPT. PL/SQL is not an option.

/** begin **/

prompt Start Date (format YYMMDD):;
accept absn_start_date;

prompt End Date (format YYMMDD):;
accept absn_end_date;

select 'Yes'
from dual
where &absn_start_date in

      (select decode(rtrim(to_char(to_date(excpt_start_date,'YYMMDD')-1,'DAY')),

'SUNDAY',to_char(to_date(excpt_start_date,'YYMMDD')-3,'YYMMDD'),
'SATURDAY',to_char(to_date(excpt_start_date,'YYMMDD')-2,'YYMMDD'),
to_char(to_date(excpt_start_date,'YYMMDD')-1,'YYMMDD')) from exception_calendar union select decode(rtrim(to_char(to_date(excpt_start_date,'YYMMDD')+1,'DAY')),
'SATURDAY',to_char(to_date(excpt_start_date,'YYMMDD')+3,'YYMMDD'),
'SUNDAY',to_char(to_date(excpt_start_date,'YYMMDD')+2,'YYMMDD'),
to_char(to_date(excpt_start_date,'YYMMDD')+1,'YYMMDD')) from exception_calendar union select decode(rtrim(to_char(to_date(excpt_end_date,'YYMMDD')-1,'DAY')),
'SUNDAY',to_char(to_date(excpt_end_date,'YYMMDD')-3,'YYMMDD'),
'SATURDAY',to_char(to_date(excpt_end_date,'YYMMDD')-2,'YYMMDD'),
to_char(to_date(excpt_end_date,'YYMMDD')-1,'YYMMDD')) from exception_calendar union select decode(rtrim(to_char(to_date(excpt_end_date,'YYMMDD')+1,'DAY')),
'SATURDAY',to_char(to_date(excpt_end_date,'YYMMDD')+3,'YYMMDD'),
'SUNDAY',to_char(to_date(excpt_end_date,'YYMMDD')+2,'YYMMDD'),
to_char(to_date(excpt_end_date,'YYMMDD')+1,'YYMMDD')) from exception_calendar ) or &absn_end_date in (select decode(rtrim(to_char(to_date(excpt_start_date,'YYMMDD')-1,'DAY')),
'SUNDAY',to_char(to_date(excpt_start_date,'YYMMDD')-3,'YYMMDD'),
'SATURDAY',to_char(to_date(excpt_start_date,'YYMMDD')-2,'YYMMDD'),
to_char(to_date(excpt_start_date,'YYMMDD')-1,'YYMMDD')) from exception_calendar union select decode(rtrim(to_char(to_date(excpt_start_date,'YYMMDD')+1,'DAY')),
'SATURDAY',to_char(to_date(excpt_start_date,'YYMMDD')+3,'YYMMDD'),
'SUNDAY',to_char(to_date(excpt_start_date,'YYMMDD')+2,'YYMMDD'),
to_char(to_date(excpt_start_date,'YYMMDD')+1,'YYMMDD')) from exception_calendar union select decode(rtrim(to_char(to_date(excpt_end_date,'YYMMDD')-1,'DAY')),
'SUNDAY',to_char(to_date(excpt_end_date,'YYMMDD')-3,'YYMMDD'),
'SATURDAY',to_char(to_date(excpt_end_date,'YYMMDD')-2,'YYMMDD'),
to_char(to_date(excpt_end_date,'YYMMDD')-1,'YYMMDD')) from exception_calendar union select decode(rtrim(to_char(to_date(excpt_end_date,'YYMMDD')+1,'DAY')),
'SATURDAY',to_char(to_date(excpt_end_date,'YYMMDD')+3,'YYMMDD'),
'SUNDAY',to_char(to_date(excpt_end_date,'YYMMDD')+2,'YYMMDD'),
to_char(to_date(excpt_end_date,'YYMMDD')+1,'YYMMDD')) from exception_calendar );

/** end **/

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 Tue Jul 02 1996 - 00:00:00 CEST

Original text of this message