Forum Stats

  • 3,817,436 Users
  • 2,259,333 Discussions
  • 7,893,774 Comments

Discussions

Oracle Apex Modfiy Grid query to fetch previous quarter data if current not available

Vinipanda
Vinipanda Member Posts: 106 Red Ribbon

I have an apex application that shows department wise project data for every quarter.

So on home page we select department, quarter and year and then for the same data is shown in all pages.


For one page that shows KPI data in interactive grid ,I need to modify my query as per logic required.


It needs to fetch data quarterly, however if say data is not available for a particular quarter it'd take the data from previous quarter. 


So, if for Quarter 1,2021 data is not available, it should fetch from Quarter 4,2020.

If Quarter 2,2021 is not available, it should fetch Quarter 1,2021 data.


Tables with data for clarity:


The first tale is DEPT_QUARTER_DATA which stores id for each quarter of department.



So, each dept_id for each quarter has a particular quarter_pk.


Also 10 is First quarter, 11 is Second quarter,12 is Third quarter and 13 is fourth quarter.


DEPT_DATA table which has main data:



It has sample data for 2 departments.


So, ìn first row,dept_quarter_id =1 indicates Quarter 1,2020 data, for dept_id=1.


Also,

DEPT_QUARTER_DATA.QUARTER_PK = DEPT_DATA.DEPT_QUARTER_ID


Now the query should be, say there is no data for dept_id=1 in dept_quarter_id=2, then it should fetch data of dept_quarter_id=1.i.e. go backwards.


If there is no data for dept_quarter_id=5, i.e.Quarter 1,2021, it should fetch data from dept_quarter_id=4, i.e. Quarter 4,2020.


Current Query is and i understand it needs to be modified:


  SELECT DEPT_ID,DEPT_QUARTER_ID,

      ,case when KPI_1 is null then (Select KPI_1 from DEPT_DATA where DEPT_QUARTER_ID

  in(select quarter_pk from DEPT_QUARTER_DATA where DEPT_ID=:DEPT_ID and

  quarter = :QUARTER-1 and year = YEAR and QUARTER_PK= :DEPT_QUARTER_ID)and PROJECT = :P1_PROJECT) else KPI_1 end as KPI_1

  from DEPT_DATA

  WHERE  

    DEPT_QUARTER_ID=:DEPT_QUARTER_ID

  AND   PROJECT = :P1_PROJECT 


QUARTER, PROJECT,YEAR are all global items being passed based on DEPT_QUARTER_ID of the project which has been set as application item.


Apex: 20.2

Tagged:

Answers