Technology Blogs by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
cancel
Showing results for 
Search instead for 
Did you mean: 
chandan_praharaj
Contributor

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:

Labels in this area