CREATE OR REPLACE FUNCTION MIG_EXT.ADD_MONTHS
/* This function is used to add the number of months to the given date.
*/
	( i_startdate		IN		DATE
	, i_no_of_months	IN		INT
	) 
RETURNS DATE
IMMUTABLE
AS 
$$
DECLARE	v_thedate			DATE := i_startdate;                          
BEGIN 

   IF i_no_of_months = 0 THEN
      RETURN i_startdate;
   ELSE
      v_thedate := v_thedate + i_no_of_months * INTERVAL '1 MONTH';
      RETURN v_thedate; 
   END IF; 

END;
$$
LANGUAGE plpgsql;



CREATE OR REPLACE FUNCTION MIG_EXT.MONTHS_BETWEEN
/* This function is used to calculate the month difference.
   The day difference is also considered in the month difference calculatio.
   So, it might return the decimal value.
*/
	( i_date1 		IN		DATE
	, i_date2 		IN		DATE ) 
RETURNS NUMERIC(24, 6)
IMMUTABLE
AS
$$
DECLARE
     l_years_diff 			INT 			:= 0;
     l_months_diff_wo_day 	INT 			:= 0;
     l_months_diff_w_day		NUMERIC(24,6) 	:= 0;
	 l_date1_truc			DATE;
	 l_date2_truc			DATE;
	 l_date1_day			NUMBER(2);
	 l_date2_day			NUMBER(2);
BEGIN
	
	-- for calculating the year difference.
    l_years_diff  		  := DATE_PART('YEAR', i_date1) - DATE_PART('YEAR', i_date2);

	-- for calculating the month difference using the value of year difference.
    l_months_diff_wo_day := l_years_diff * 12 + (DATE_PART('MONTH', i_date1) - DATE_PART('MONTH', i_date2)); 

	-- truncate to date i.e., remove the time portion
	l_date1_truc	   := DATE_TRUNC('DAY', i_date1);
	l_date2_truc	   := DATE_TRUNC('DAY', i_date2);
	
	-- find the day of the month i.e., "21st January 2017" will return 21.
	l_date1_day		   := DATE_PART('DAY', i_date1);
	l_date2_day		   := DATE_PART('DAY', i_date2);
	
	-- check whether both the dates are last days of their months.
	IF ( l_date1_day	  = l_date2_day )
	   OR
	   ( l_date1_truc 	  = LAST_DAY(l_date1_truc) 
	     AND l_date2_truc = LAST_DAY(l_date2_truc) ) -- i.e., '31Jan2017' and '28Feb2017' are the last days of the months
	THEN
		l_months_diff_w_day	:= l_months_diff_wo_day;
	ELSE
		-- for calculating the month difference by considering the day difference also.
		l_months_diff_w_day	:= ROUND(l_months_diff_wo_day + (l_date1_day - l_date2_day)/31, 6);
	END IF;

	RETURN l_months_diff_w_day;
END;
$$ 
LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION MIG_EXT.LAST_DAY
/* This function is used to find the last day in the month of the given date.
*/
	( i_date  		IN	DATE )
RETURNS DATE 
IMMUTABLE
AS  
$$
DECLARE	v_thedate			DATE := i_date; 
BEGIN
	/* It finds the first day of the month and adds one month to the first day 
	   and substracts one day from the calculated date.
	*/
    v_thedate := (DATE_TRUNC('MONTH', i_date) + INTERVAL '1 MONTH - 1 DAY')::DATE;
	RETURN v_thedate;
END;
$$ 
LANGUAGE plpgsql;

