Exact Months/Years/Quarter etc.. between two dates by Stored Procedure
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. 🙂
Be the first to leave a comment
You must be Logged on to comment or reply to a post.