asked on

Bank Holiday Check

I am trying to populate a field called RF_BANK_DATE which is derived element and the business what this field to be populated based on RF_Post_date..(please review the below)
However there is an issue with the below logic if the RF_Post_Date happens to on a Friday then we are going to set RF_Bank_Date to either Saturday/Sunday or if it posted on a working day then if the following day is a bank holiday then the RF_BANK_DATE is not valid because there will not any exchange of funds on a sat or Sunday or on a holiday.

So I have to modify the logic so that I can exclude Bank Holidays ? How do I do that ? Please help me ASAP
DBMS NAME - Sybase Adaptive Server Anywhere
DBMS Version- 12.70

Update itsrf
SET RF_Bank_dt =
CASE
WHEN Home_Host_Code = 'Home' AND itsrf.RF_Post_Date = today()-1 THEN itsrf.RF_Post_Date + 2
WHEN Home_Host_Code = 'Home' AND itsrf.RF_Post_Date = today() THEN itsrf.RF_Post_Date + 1
WHEN Home_Host_Code = 'Host' AND itsrf.ITS_Central_Financial_Agency_Disp_Date = today()-1 THEN
itsrf.ITS_Central_Financial_Agency_Disp_Date + 2
WHEN Home_Host_Code = 'Host' AND itsrf.ITS_Central_Financial_Agency_Disp_Date = today() THEN
itsrf.ITS_Central_Financial_Agency_Disp_Date + 1
ELSE '1800-01-01'
END

Thank you,

wilcoxon

This code should work to handle weekends correctly. However, there is no easy way to handle weekday bank holidays as there is no uniformity to them.

set @@datefirst 2 -- do this so sat = 6 and sun = 7
declare @post_dt datetime, @dow tinyint
select @post_dt =
         CASE
             WHEN Home_Host_Code = 'Home' AND itsrf.RF_Post_Date = today()-1 THEN itsrf.RF_Post_Date + 2
             WHEN Home_Host_Code = 'Home' AND itsrf.RF_Post_Date = today() THEN itsrf.RF_Post_Date + 1
             WHEN Home_Host_Code = 'Host' AND itsrf.ITS_Central_Financial_Agency_Disp_Date = today()-1 THEN
                  itsrf.ITS_Central_Financial_Agency_Disp_Date + 2
             WHEN Home_Host_Code = 'Host' AND itsrf.ITS_Central_Financial_Agency_Disp_Date = today() THEN
                  itsrf.ITS_Central_Financial_Agency_Disp_Date + 1
             ELSE '1800-01-01'
         END 
from itsrf

-- if day-of-week is weekend (6-7), invert (-6 or -7) and add 8 to get +1 or +2
select @dow = datepart('weekday', @post_dt)
if (@dow > 5)
    select @post_dt = dateadd(day, @dow * -1 + 8, @post_dt)

Update itsrf set RF_Bank_dt = @post_dt

Open in new window

Joe Woodhouse

I've only seen bank holidays handled ad-hoc for each state or province. You can have a table just of bank holidays, or a table of all days in the year, with a flag for which days are business/bank days and which are not. The SQL expressions become pretty simple then.

ASKER CERTIFIED SOLUTION

wilcoxon

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ritesh_Garg

ASKER

Holidays was handled in independent functions.