Monday, September 9, 2013

SQL : Stored Procedures : Next working date

Working on stored procedures is fun. For 1 functionality, wanted to get next working date which will exclude saturday sundays and dates from holiday list table.

I got really nice solution on mysql-stored-procedure-for-computing. But only draw back was Recursive limit. But tried the solution added in comments.

Checked and it worked fine. Only change done in this solution that if no date provided for function it should take current date. And parameter numDays removed.

So if current date is 2013-09-04. 2013-09-05 and 2013-09-06 in holiday list table. 2013-09-07 is Saturday and 2013-09-08 Sunday.








The output should be















And instead of both functions changed one to stored procedure as below.
Stored Procedure : sp_get_next_working_date
/**
Stored Procedure : sp_get_next_working_date
Parameter : p_date as date
Description : Returns next working date excluding Saturday/ Sunday OR Date in holiday list table.
Dependency : Function fn_check_date_if_in_holiday_list 
**/

BEGIN
  DECLARE next_working_date DATE;
  SET next_working_date = p_date;

  IF p_date = 0 THEN 
    SET next_working_date = DATE(NOW()); 
  END IF;
  SET next_working_date = DATE_ADD(next_working_date, INTERVAL 1 DAY);

  WHILE fn_check_date_if_in_holiday_list(next_working_date) DO 
    SET next_working_date = DATE_ADD(next_working_date, INTERVAL 1 DAY);
  END WHILE;

  SELECT next_working_date;
END
Function : fn_check_date_if_in_holiday_list
/**
Function : fn_check_date_if_in_holiday_list
Parameter : p_date as date
Description : Checks if date is Saturday/ Sunday OR in holiday list table.
Dependent : sp_get_next_working_date
**/

DETERMINISTIC
BEGIN
  DECLARE dayOfWeek INT DEFAULT 0;
  DECLARE result BIT DEFAULT false;
  DECLARE holidayCount INT DEFAULT 0;

  SET dayOfWeek = DAYOFWEEK(p_date);
  SELECT COUNT(*) from TBLholidays where holiday_date = p_date INTO holidayCount;

  IF dayOfWeek = 7 or dayOfWeek = 1 or holidayCount > 0 THEN 
    SET result = true;
  END IF;

  RETURN result;
END

No comments:

Post a Comment