Comments

• Navin_C Vertica Customer ✭

  October 2013

  #May,
  
  I think this can help you
  
  A User defined SQL function
  
  CREATE FUNCTION ADD_DAYS(x VARCHAR, y INT) RETURN TIMESTAMP
  AS BEGIN
  RETURN ( CAST (x AS TIMESTAMP) + y );
  END;
  
  
  Usage example :
  
  nnani=> select ADD_DAYS('2013-01-20',1);
        ADD_DAYS
  ---------------------
   2013-01-21 00:00:00
  (1 row)

  nnani=> select ADD_DAYS('2013-01-20',60);
        ADD_DAYS
  ---------------------
   2013-03-21 00:00:00
  (1 row)

   

  Let me know, if this matches your requirement

  Hope this helps.

  0
• May_1

  October 2013

  That works, thank you, Navin! 
  
  I wish there is a built-in function ADD_DAYS in Vertica, especially when there is ADD_MONTHS. I'd make a feature request.
  

  0
• jay_1

  October 2013

  Hi,
  I would like to calculate working days between two dates. This also should exclude official holidays. Is there a function in vertica ? If not how to implement this ?
  Thanks
  Jayvertica
  

  0
• Navin_C Vertica Customer ✭

  October 2013

  Hi Jay,
  
  Defining a function like this is a bit tricky, this can be done using the UDF feature in Vertica.
  The usage of SELECT,FROM,AGGREGTES,ANALYTIC Functions is restricted while create SQL function with the current releases.
  
  A possible workaround for this
  
  You need to have to temporary tables / Views
  1. Bank_holidays
  2. Weekend_holidays
  
  For weekend Holidays table you can use this
  
  create local temporary table weekend_holidays  on commit preserve rows as
  (SELECT * FROM(SELECTts::DATE
  calendar_date,year(ts::DATE)    
   year_of_calendar,month(ts::DATE)    
   month_of_year,
   dayofweek_iso(ts::DATE) day_of_week
  FROM (SELECT '01-01-2013'::TIMESTAMP as tmUNIONSELECT '12-31-2013'::TIMESTAMP as tm) as t TIMESERIES ts as '1 Day' OVER (ORDER BY tm)) sub1where sub1.day_of_week = 7);
  
  Once this table is created, you can create another table Bank_holidays according to country required for.
  
  CREATE TABLE bank_holidays
  (   
  holiday_date date,   
  holiday_description varchar(50),   
  holiday_year int,   
  holiday_day_of_week int DEFAULT dayofweek_iso(bank_holidays.holiday_date));
  
  Populate this table with exact data
  
  
  Then you can use both these table to query and get the working days like this
  
  select (datediff('day',cast('01-05-2013' as date), cast('02-05-2013' as date)) - holidays) as working_days
  from(select count(*) as holidays from(select calendar_date from weekend_holidays wh
  where wh.calendar_date between '01-05-2013' and '02-05-2013 '
  UNION
   select holiday_date from bank_holidays bh
  where bh.holiday_date between  '01-05-2013' and '02-05-2013' and holiday_day_of_week <> 7) sub order by 1) sub1;
  
  This should give you working days.
  
  
  Hope this helps.

  0
• jay_1

  October 2013

  Thanks Navin. I have tested the above work around for UK bank holidays. It worked fine. However I wanted to use it in  filter condition ( Date diff <=10).  I had earlier created oracle function which I could use easily in the filter condition. I tried  a bit of UDF, but seems to be difficult task.

  0
• jay_1

  October 2013

  Oracle Function :
  create or replace
  FUNCTION f_working_days (p_start_date DATE, p_end_date DATE)
     RETURN NUMBER
  IS
     v_holidays     NUMBER;
     v_start_date   DATE   := TRUNC (p_start_date);
     v_end_date     DATE   := TRUNC (p_end_date);
  BEGIN
     IF v_end_date >= v_start_date
     THEN
        SELECT COUNT (holiday)
          INTO v_holidays
          FROM HOLIDAY_DAYS
         WHERE holiday BETWEEN v_start_date AND v_end_date;

        RETURN   GREATEST (NEXT_DAY (v_start_date, 'MON') - v_start_date - 2, 0)
               +   (  (  NEXT_DAY (v_end_date, 'MON')
                       - NEXT_DAY (v_start_date, 'MON')
                      )
                    / 7
                   )
                 * 5
               - GREATEST (NEXT_DAY (v_end_date, 'MON') - v_end_date - 3, 0)
               - v_holidays;
     ELSE
        RETURN NULL;
     END IF;
  END f_working_days;

  0
• Navin_C Vertica Customer ✭

  October 2013

  Hi Jay,
  
  A possible workaround,
  
  Create a local temporary table with the last query as follows
  
  
  CREATE LOCAL TEMPORARY TABLE WORKING_DAYS_TEMP ON COMMIT PRESERVE ROWS
  AS
  select (datediff('day',cast('01-05-2013' as date), cast('02-05-2013' as date)) - holidays) as working_days,
  current_timestamp(0) as timestamp_for_working_days_extracted from
  (select count(*) as holidays from
  (select calendar_date from weekend_holidays wh
  where wh.calendar_date between '01-05-2013' and '02-05-2013 '
  UNION
   select holiday_date from bank_holidays bh
  where bh.holiday_date between  '01-05-2013' and '02-05-2013' and holiday_day_of_week <> 7) sub order by 1) sub1;
  
  The you can use a viable to store working_days value like this
  
  \set F_WORKING_DAYS 'select working_days from WORKING_DAYS_TEMP'
  
  To display the working days value stored in variable
  
  nnani=> :F_WORKING_DAYS;
   working_days
  --------------
             22
  (1 row)
  
  You can use this variable in your filter condition.
  
  Hope this helps
  
  
  
  
  

  0