SQL Query to fetch data as of the year,quarter or month passed in parameter

Question

I have the following query-

  (SELECT DISTINCT accrual_period
     FROM   anc_per_accrual_entries a,
            anc_per_plan_enrollment b
     WHERE  a.per_plan_enrt_id = b.per_plan_enrt_id
            --AND a.accrual_period = b.last_accrual_run
            AND b.work_term_asg_id = paam.work_terms_assignment_id
            AND accrual_period = (SELECT Max(acrl2.accrual_period)
                                  FROM   anc_per_accrual_entries acrl2
                                  WHERE  person_id = paam.person_id
                                         AND acrl2.plan_id = appe.plan_id
                                         AND acrl2.accrual_period <= sysdate)
            AND b.plan_id = appe.plan_id
            AND b.person_id = paam.person_id
            AND sysdate BETWEEN b.enrt_st_dt AND b.enrt_end_dt) 
        
    
    

I have the following query to fetch accrual_period as of the sysdate or today. Now there are three parameters -

P_YEAR  - 2022
P_QUARTER - 2022 Q 4
P_MONTH - 2022 / 12

    

• If the year is passed - I want the query to fetch the data as of current month and date of that year. i.e. the sysdate in the query should be replaced by 2021/05/06 if i pass year as 2021.

• If i pass Year and quarter of that year then the sysdate in the query should be replaced by that quarter. i.e. if the quarter is 2021 Q 1, then it should be calculated as of the last date of that quarter i.e. 31 march 2021.

• If I pass month, then the last day of month

  How can i achieve it in the same query ?

Answer 1

To me, it looks like the following where clause (which is to be applied to query you already posted):

where accrual_period >=
  case when :p_year is not null then 
               to_date(:p_year || to_char(sysdate, 'mmdd'), 'yyyymmdd')
       when :p_quarter is not null then
               last_day(to_date(substr(:p_quarter, 1, 4) || case substr(:p_quarter, -1) 
                                                                   when '1' then '03'
                                                                   when '2' then '06'
                                                                   when '3' then '09'
                                                                   when '4' then '12
                                                            end, 'yyyymm'))
       when :p_month is not null then last_day(:p_month, 'yyyy / mm')
  end;

---

As of your comment regarding an "invalid argument" error and doubt about the last_day function: everything is just fine when I run it (I don't have your tables nor data, but such a dummy query works OK):

SQL> with test(col) as
  2    (select '2022 Q 4' from dual)
  3  select last_day(to_date(substr(col, 1, 4) || case substr(col, -1) when '1' then '03'
  4                                                           when '2' then '06'
  5                                                           when '3' then '09'
  6                                                           when '4' then '12' end, 'yyyymm')) result
  7  from test;

RESULT
----------
31.12.2022

SQL>