Working Day Calculation - Followup
From: Eric Keen <e_keen_at_access.digex.net>
Date: 22 Apr 1994 12:11:33 -0400
Message-ID: <e_keen.767030711_at_access3>
Shell Script:
echo $PWD/$0
echo 'Checking # of working days passed this month: ' \ `date +%m'/01/'%y` ' to ' `date +%D`
sqlplus -s user/user _at_check_work `date +%Y%m` echo 'This is working day: ' $?
rem - check_work.sql
Rem - start month format is YYYYMM
SET TERMOUT OFF DEFINE start_month = TO_DATE(&1,'YYYYMM')
Note: Thanks to Ashok Ganti for correcting some earlier problems. Received on Fri Apr 22 1994 - 18:11:33 CEST
Date: 22 Apr 1994 12:11:33 -0400
Message-ID: <e_keen.767030711_at_access3>
Current solution for calculating the current working day from Sql*Plus (including holidays).
Table: HOLIDAYS
HOLIDAY DATE , UNIQUE INDEX
Shell Script:
echo $PWD/$0
echo 'Checking # of working days passed this month: ' \ `date +%m'/01/'%y` ' to ' `date +%D`
sqlplus -s user/user _at_check_work `date +%Y%m` echo 'This is working day: ' $?
rem - check_work.sql
Rem - start month format is YYYYMM
SET TERMOUT OFF DEFINE start_month = TO_DATE(&1,'YYYYMM')
COLUMN holidays NEW_VALUE holidays
SELECT COUNT(*) holidays
FROM user.holidays
WHERE holiday BETWEEN &start_month AND SYSDATE
/
COLUMN work_day NEW_VALUE work_day FORMAT 999 SELECT &start_month begin_dt, SYSDATE end_date ,
((TRUNC(SYSDATE,'D')-TRUNC((&start_month) +6,'D'))*5/7
+ MOD(7-TO_NUMBER(TO_CHAR((&start_month),'D')),6)
+ LEAST(TO_NUMBER(TO_CHAR(SYSDATE,'D'))-1,5) - &holidays) work_day
FROM dual
/
EXIT work_day
Note: Thanks to Ashok Ganti for correcting some earlier problems. Received on Fri Apr 22 1994 - 18:11:33 CEST