A better way to code this?
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