Pipeline function to return Week Start Date and End Date of a particular month based on inputs parameter
Overview:
This document talks about how to return Week Start Date and End Date of a particular month by using pipeline function.
Technologies and Tools Used:
The following technologies has been used to achieve the expected output.
- PLSQL
- Oracle SQL Developer
Use Case:
Assume that there is a requirement to get all start date and end date of a week in a particular month. There is no predefined functions or features to achieve this in PLSQL or Oracle Apex, so we need to go for the customization with PLSQL.
Architecture:
Following steps explains in detail,
- As a first step, we need to create object and object type in PLSQL to store and display the output in SQL Developer.
Sample Code:
CREATE TYPE obj_weeks AS OBJECT (
week NUMBER,
week_start varchar2(100),
week_end varchar2(100)
);
CREATE TYPE ty_tb_obj_weeks IS TABLE OF obj_weeks;
- Then create a pipeline function with two input parameter. ,
Sample Code:
CREATE OR REPLACE FUNCTION get_weeks (
p_date IN VARCHAR2,
p_week_start IN NUMBER
) RETURN ty_tb_obj_weeks
PIPELINED
AS
BEGIN
FOR i IN (
SELECT
ROWNUM AS week,
a.*
FROM
(
SELECT
to_char(MIN(month_date), ‘Dy DD-MON-YYYY’) AS wsdate,
to_char(MAX(month_date), ‘Dy DD-MON-YYYY’) AS wedate
FROM
(
SELECT
trunc(to_date(p_date, ‘DD-MON-YYYY’), ‘MM’) + level – 1 month_date
FROM
dual
CONNECT BY
trunc(trunc(to_date(p_date, ‘DD-MON-YYYY’), ‘MM’) + level – 1, ‘MM’) = trunc(to_date(p_date, ‘DD-MON-YYYY’), ‘MM’) )
GROUP BY
month_date + ( 1 – to_char(month_date – p_week_start, ‘D’) ),
month_date + ( 7 – to_char(month_date – p_week_start, ‘D’) )
ORDER BY
MIN(month_date)
) a
) LOOP
PIPE ROW ( obj_weeks(i.week, i.wsdate, i.wedate) );
END LOOP;
Return;
END;
Output:
By calling the pipeline function with any date and 1 or 0 as input parameters, we can get the expected output. If you call the function with 1 for week start parameter then it will return Monday as week start date if call with 0 then it will return Sunday as week start date. This function will return month start date as 1st week’s start date and month last date as Last week’s end date.
Sample 1:
SELECT * FROM TABLE (get_weeks (’01-SEP-2020′, 1));
In this example, we passed 1 so it will return Monday as week start day and Sunday as week end at the same time month starts in Tuesday so it will return 01-SEP-2020(Tuesday) as 1st week start date and 30-SEP-2020(Wednesday) as week end date.
Sample 2:
SELECT * FROM TABLE (get_weeks (’01-SEP-2020′, 0));
In this example, we passed 0 so it will return Sunday as week start and Saturday as week end at the same time month starts in Tuesday so it will return 01-SEP-2020(Tuesday) as 1st week start date and 30-SEP-2020(Wednesday) as week end date.
