CODEWITHSUNDEEP
sqloracle-database
Dulguun
Dulguun

Reputation: 13

Get Quarters from date

When user gives you the date like 2019/02/03 my function should return date that should be like this : 2019/02/03, 2019/02/02, 2019/02/01, 2019/01/31, 2019/12/31, 2019/11/31, 2019/09/30, 2019/06/30, 2019/03/31, 2018/12/31

This function of mine has a compiling error and im not sure what it is.

CREATE OR REPLACE FUNCTION rep_datesTest(pdt_ref_date IN DATE)
    RETURN SYS_REFCURSOR IS
        pcr_result SYS_REFCURSOR;
    BEGIN
        OPEN pcr_result FOR
        SELECT  
            trunc(pdt_ref_date) as  pdt_ref_date,
            trunc(pdt_ref_date) - 1 pdt_ref_date_1,
            trunc(pdt_ref_date) - 2 pdt_ref_date_2,
            trunc(pdt_ref_date) - 3 pdt_ref_date_3,             
            add_months(Last_DAY(trunc(trunc(pdt_ref_date) - 3, 'MM')), -1) first_day_of_last_month,
            add_months(Last_DAY(trunc(trunc(pdt_ref_date) - 3, 'MM')), -2) first_day_of_2nd_last_month,
            add_months(Last_DAY(trunc(trunc(pdt_ref_date) - 3, 'MM')), -3) first_day_of_3nd_last_month,        
            add_months(Last_DAY(Case when EXTRACT(MONTH from first_day_of_3nd_last_month)>9 and EXTRACT(MONTH from first_day_of_3nd_last_month)<=12 
                                                        then to_date(EXTRACT(YEAR FROm first_day_of_3nd_last_month)||'-09-30','yyyy-mm-dd') 
                                     when EXTRACT(MONTH from first_day_of_3nd_last_month)<=9 and EXTRACT(MONTH from first_day_of_3nd_last_month)>6
                                                        then to_date(EXTRACT(YEAR FROm first_day_of_3nd_last_month)||'-06-30','yyyy-mm-dd') 
                                     when EXTRACT(MONTH from first_day_of_3nd_last_month)<=6 and EXTRACT(MONTH from first_day_of_3nd_last_month)>3
                                                        then to_date(EXTRACT(YEAR FROm first_day_of_3nd_last_month)||'-03-31','yyyy-mm-dd')  
                                      when EXTRACT(MONTH from first_day_of_3nd_last_month)<=3 and EXTRACT(MONTH from first_day_of_3nd_last_month)>12
                                                        then to_date(EXTRACT(YEAR FROm first_day_of_3nd_last_month)||'-12-31','yyyy-mm-dd')   END              
                                )) first_day_of_last_quarter_1,
            add_months(Last_DAY(trunc(first_day_of_last_quarter_1, 'Q')), -8) first_day_of_last_quarter_2,
            add_months(Last_DAY(trunc(first_day_of_last_quarter_1, 'Q')), -11) first_day_of_last_quarter_3,
            add_months(Last_DAY(trunc(first_day_of_last_quarter_1, 'Q')), -14) first_day_of_last_quarter_4,
            add_months(Last_DAY(trunc(first_day_of_last_quarter_1, 'Q')), -17) first_day_of_last_quarter_5
        FROM dual;

        RETURN pcr_result;
    END;

Upvotes: 0

Views: 54

Answers (2)

Boneist
Boneist

Reputation: 23588

Here is the solution using records (based on MT0's SQL answer):

CREATE OR REPLACE PACKAGE your_package
AS

  TYPE date_info_rec IS RECORD (pdt_ref_date DATE,
                                pdt_ref_date_1 DATE,
                                pdt_ref_date_2 DATE,
                                pdt_ref_date_3 DATE,
                                first_day_of_last_month DATE,
                                first_day_of_2nd_last_month DATE,
                                first_day_of_3rd_last_month DATE,
                                first_day_of_last_quarter_1 DATE,
                                first_day_of_last_quarter_2 DATE,
                                first_day_of_last_quarter_3 DATE,
                                first_day_of_last_quarter_4 DATE,
                                first_day_of_last_quarter_5 DATE);

  FUNCTION your_function (in_date IN DATE)
  RETURN date_info_rec;

END your_package;
/

CREATE OR REPLACE PACKAGE BODY your_package
AS

  FUNCTION your_function (in_date IN DATE)
  RETURN date_info_rec
  IS
    v_date_rec date_info_rec;
  BEGIN
    v_date_rec.pdt_ref_date                := trunc(in_date);
    v_date_rec.pdt_ref_date_1              := trunc(in_date) - 1;
    v_date_rec.pdt_ref_date_2              := trunc(in_date) - 2;
    v_date_rec.pdt_ref_date_3              := trunc(in_date) - 3;
    v_date_rec.first_day_of_last_month     := trunc(in_date, 'MM') - 1;
    v_date_rec.first_day_of_2nd_last_month := add_months(trunc(in_date, 'MM'), -0) - 1;
    v_date_rec.first_day_of_3rd_last_month := add_months(trunc(in_date, 'MM'), -0) - 1;
    v_date_rec.first_day_of_last_quarter_1 := trunc(in_date, 'Q') - 1;
    v_date_rec.first_day_of_last_quarter_2 := add_months(trunc(in_date, 'Q'), -3) - 1;
    v_date_rec.first_day_of_last_quarter_3 := add_months(trunc(in_date, 'Q'), -6) - 1;
    v_date_rec.first_day_of_last_quarter_4 := add_months(trunc(in_date, 'Q'), -9) - 1;
    v_date_rec.first_day_of_last_quarter_5 := add_months(trunc(in_date, 'Q'), -12) - 1;

    RETURN v_date_rec;
  END;

END your_package;
/

An example of how you might call the function:

DECLARE
  v_date DATE;
  v_pop_date_rec your_package.date_info_rec;
BEGIN
  v_date := SYSDATE;

  v_pop_date_rec := your_package.your_function(in_date => v_date);

  -- Demonstrate the output
  dbms_output.put_line('v_pop_date_rec.pdt_ref_date = '||to_char(v_pop_date_rec.pdt_ref_date, 'dd/mm/yyyy'));
  dbms_output.put_line('v_pop_date_rec.pdt_ref_date_1 = '||to_char(v_pop_date_rec.pdt_ref_date_1, 'dd/mm/yyyy'));
  dbms_output.put_line('v_pop_date_rec.pdt_ref_date_2 = '||to_char(v_pop_date_rec.pdt_ref_date_2, 'dd/mm/yyyy'));
  dbms_output.put_line('v_pop_date_rec.pdt_ref_date_3 = '||to_char(v_pop_date_rec.pdt_ref_date_3, 'dd/mm/yyyy'));
  dbms_output.put_line('v_pop_date_rec.first_day_of_last_month = '||to_char(v_pop_date_rec.first_day_of_last_month, 'dd/mm/yyyy'));
  dbms_output.put_line('v_pop_date_rec.first_day_of_2nd_last_month = '||to_char(v_pop_date_rec.first_day_of_2nd_last_month, 'dd/mm/yyyy'));
  dbms_output.put_line('v_pop_date_rec.first_day_of_3rd_last_month = '||to_char(v_pop_date_rec.first_day_of_3rd_last_month, 'dd/mm/yyyy'));
  dbms_output.put_line('v_pop_date_rec.first_day_of_last_quarter_1 = '||to_char(v_pop_date_rec.first_day_of_last_quarter_1, 'dd/mm/yyyy'));
  dbms_output.put_line('v_pop_date_rec.first_day_of_last_quarter_2 = '||to_char(v_pop_date_rec.first_day_of_last_quarter_2, 'dd/mm/yyyy'));
  dbms_output.put_line('v_pop_date_rec.first_day_of_last_quarter_3 = '||to_char(v_pop_date_rec.first_day_of_last_quarter_3, 'dd/mm/yyyy'));
  dbms_output.put_line('v_pop_date_rec.first_day_of_last_quarter_4 = '||to_char(v_pop_date_rec.first_day_of_last_quarter_4, 'dd/mm/yyyy'));
  dbms_output.put_line('v_pop_date_rec.first_day_of_last_quarter_5 = '||to_char(v_pop_date_rec.first_day_of_last_quarter_5, 'dd/mm/yyyy'));
END;
/

v_pop_date_rec.pdt_ref_date = 22/02/2019
v_pop_date_rec.pdt_ref_date_1 = 21/02/2019
v_pop_date_rec.pdt_ref_date_2 = 20/02/2019
v_pop_date_rec.pdt_ref_date_3 = 19/02/2019
v_pop_date_rec.first_day_of_last_month = 31/01/2019
v_pop_date_rec.first_day_of_2nd_last_month = 31/01/2019
v_pop_date_rec.first_day_of_3rd_last_month = 31/01/2019
v_pop_date_rec.first_day_of_last_quarter_1 = 31/12/2018
v_pop_date_rec.first_day_of_last_quarter_2 = 30/09/2018
v_pop_date_rec.first_day_of_last_quarter_3 = 30/06/2018
v_pop_date_rec.first_day_of_last_quarter_4 = 31/03/2018
v_pop_date_rec.first_day_of_last_quarter_5 = 31/12/2017

Upvotes: 0

MT0
MT0

Reputation: 168730

I believe you want:

SELECT trunc(pdt_ref_date) - 0 AS pdt_ref_date,
       trunc(pdt_ref_date) - 1 AS pdt_ref_date_1,
       trunc(pdt_ref_date) - 2 AS pdt_ref_date_2,
       trunc(pdt_ref_date) - 3 AS pdt_ref_date_3,             
       add_months(trunc(pdt_ref_date, 'MM'), -0)-1 AS first_day_of_last_month,
       add_months(trunc(pdt_ref_date, 'MM'), -1)-1 AS first_day_of_2nd_last_month,
       add_months(trunc(pdt_ref_date, 'MM'), -2)-1 AS first_day_of_3nd_last_month,
       add_months(trunc(pdt_ref_date, 'Q'), -0)-1  AS first_day_of_last_quarter_1,
       add_months(trunc(pdt_ref_date, 'Q'), -3)-1  AS first_day_of_last_quarter_2,
       add_months(trunc(pdt_ref_date, 'Q'), -6)-1  AS first_day_of_last_quarter_3,
       add_months(trunc(pdt_ref_date, 'Q'), -9)-1  AS first_day_of_last_quarter_4,
       add_months(trunc(pdt_ref_date, 'Q'), -12)-1 AS first_day_of_last_quarter_5
FROM   dual;

PDT_REF_DATE | PDT_REF_DATE_1 | PDT_REF_DATE_2 | PDT_REF_DATE_3 | FIRST_DAY_OF_LAST_MONTH | FIRST_DAY_OF_2ND_LAST_MONTH | FIRST_DAY_OF_3ND_LAST_MONTH | FIRST_DAY_OF_LAST_QUARTER_1 | FIRST_DAY_OF_LAST_QUARTER_2 | FIRST_DAY_OF_LAST_QUARTER_3 | FIRST_DAY_OF_LAST_QUARTER_4 | FIRST_DAY_OF_LAST_QUARTER_5
:----------- | :------------- | :------------- | :------------- | :---------------------- | :-------------------------- | :-------------------------- | :-------------------------- | :-------------------------- | :-------------------------- | :-------------------------- | :--------------------------
03-FEB-19    | 02-FEB-19      | 01-FEB-19      | 31-JAN-19      | 31-JAN-19               | 31-DEC-18                   | 30-NOV-18                   | 31-DEC-18                   | 30-SEP-18                   | 30-JUN-18                   | 31-MAR-18                   | 31-DEC-17

db<>fiddle here

Upvotes: 1

Related Questions