Discussions
Categories
- 196.9K All Categories
- 2.2K Data
- 239 Big Data Appliance
- 1.9K Data Science
- 450.3K Databases
- 221.7K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 550 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 545 SQLcl
- 4K SQL Developer Data Modeler
- 187K SQL & PL/SQL
- 21.3K SQL Developer
- 295.9K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.6K Development Tools
- 107 DevOps
- 3.1K QA/Testing
- 646K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 155 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 18 Java Essentials
- 160 Java 8 Questions
- 86K Java Programming
- 80 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 440 LiveLabs
- 38 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 232 Portuguese
Oracle Apex Modfiy Grid query to fetch previous quarter data if current not available

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
Answers
-
Hi, @Vinipanda
Whenever you have a question, please post a little sample data in a usable form (such as CREATE TABLE and INSERT statements), so the people who want to help you car re-create the problem and test their ideas. Also post the exact results you want from the given data, and an explanation (in general terms) of you get those results from that data.
Always say which version of Oracle you're using (e.g. 12.2.0.1.0).
So, if the quarter you want isn't available, then you want the quarter before that. That sounds like a job for a Top-N Query. In a sub-query, fetch data from the quarter you want and all previous quarters (or just the one previous, depending on your requirements), and use the analytic DENSE_RANK function to number them 1, 2, 3, .. with 1 being the latest one actually present. Then, in the super-query, include only the rows numbered 1.
-
Hi,
Since you're always displaying only one quarter, you could also start by finding out which is the last quarter actually present on or before the quarter you want, and then including only that quarter.
-
@Vinipanda a better query for your scenario is:
select DEPT_ID,DEPT_QUARTER_ID,nvl(KPI_1 ,previous_QUARTER_data) as KPI_1
from (
SELECT DEPT_ID,DEPT_QUARTER_ID,KPI_1,
lag(KPI_1) over (order by DEPT_QUARTER_ID) as previous_QUARTER_data
from DEPT_DATA
WHERE
DEPT_QUARTER_ID=:DEPT_QUARTER_ID
AND PROJECT = :P1_PROJECT );