Exact Months/Years/Quarter etc.. between two dates by Stored Procedure

[chandan_praharaj]

We generally face issue while deriving Number of years/Months and Quarter etc. between 2 DATES.  Below is the code I have implemented for one of my application , which I am sharing.

We can use the "_SYS_BI"."M_TIME_DIMENSION" table in various other way, but this is one of its way of using and calling that where ever necessary.

/********* Begin Procedure Script ************/

BEGIN

IF UPPER(:IP_TYPE) = 'M'

 then

 var1 =  SELECT

 COUNT(*)-1 as VAL

FROM



 (



 SELECT DISTINCT YEAR, MONTH FROM "_SYS_BI"."M_TIME_DIMENSION"



 WHERE DATE_SQL BETWEEN :IP_DATE1 AND :IP_DATE2



 );

ELSEIF UPPER(:IP_TYPE) = 'Q'

 then

 var1 =  SELECT

 COUNT(*)-1 as VAL

FROM



 (



 SELECT DISTINCT YEAR,QUARTER FROM "_SYS_BI"."M_TIME_DIMENSION"



 WHERE DATE_SQL BETWEEN :IP_DATE1 AND :IP_DATE2



 );

ELSEIF UPPER(:IP_TYPE) = 'Y'

 then

 var1 =  SELECT

 COUNT(*)-1  as VAL

 FROM



 (



 SELECT DISTINCT YEAR FROM "_SYS_BI"."M_TIME_DIMENSION"



 WHERE DATE_SQL BETWEEN :IP_DATE1 AND :IP_DATE2



 );

END IF;

OUTPUT_TABLE = select * from :var1;

END;

 /********* End Procedure Script ************/

After activating the SP, we can call from SQL console and it will show the output as below.

call "_SYS_BIC"."users.Chandan/SP_TEST_TIME_DIM"('2015-03-01','2016-03-01','m',NULL);

call "_SYS_BIC"."users.Chandan/SP_TEST_TIME_DIM"('2015-03-01','2016-03-01','q',NULL);

call "_SYS_BIC"."users.Chandan/SP_TEST_TIME_DIM"('2015-03-01','2016-03-01','y',NULL);

call "_SYS_BIC"."users.Chandan/SP_TEST_TIME_DIM"('2015-03-01','2016-03-01','M',NULL);

call "_SYS_BIC"."users.Chandan/SP_TEST_TIME_DIM"('2015-03-01','2016-03-01','Q',NULL);

call "_SYS_BIC"."users.Chandan/SP_TEST_TIME_DIM"('2015-03-01','2016-03-01','Y',NULL);

The output we are going to get is as below.

Hope it will helps!

I welcome any upgradation on this. :smile: