Finding previous quarter's non null data of a particular column

user_11971592 0
Viewed 101

Actual vs Expected result

Actual:

Year Quarter Value prev_quarter_val Name Date
FY 2021 Q1 2456 NULL XYZ 01-01-2020
FY 2021 Q2 NULL 2456 XYZ 01-01-2020
FY 2021 Q3 2345 2456 XYZ 01-01-2020
FY 2021 Q3 NULL 2345 (which is wrong) XYZ 01-01-2020
FY 2021 Q4 3456 2345 XYZ 01-01-2020

Expected:

Year Quarter Value prev_quarter_val Name Date
FY 2021 Q1 2456 NULL XYZ 01-01-2020
FY 2021 Q2 NULL 2456 XYZ 01-01-2020
FY 2021 Q3 2345 2456 XYZ 01-01-2020
FY 2021 Q3 NULL 2456 XYZ 01-01-2020
FY 2021 Q4 3456 2345 XYZ 01-01-2020

In the above tables, I have the columns year, quarter, value, name,prev_quarter_val and date. I have to fill the value column with values from previous quarter. If the previous quarter's value is NULL, fetch the value from the latest quarter and year where it is not NULL.

What I have tried:

SELECT year,quarter,name,date,value,prev_quarter_val
FROM (SELECT year,quarter,name,date,value, LAG(value,1)
OVER (PARTITION BY date, name ORDER BY year,quarter) AS prev_quarter_val
FROM table t)

The problem with my code is it takes the value from the same quarter if the value is present twice

How can I fix the query such that it takes from previous quarter even if its present twice?

Edit: It has to take value from previous year and it is not restricted to year constraints.

postgresql sql
1 Answers

You can do it easily with subquery:

Schema and insert statements:

 create table testtable(Year varchar(20), Quarter varchar(20), Value int, prev_quarter_val int, Name varchar(20) ,Date date);
 
 insert into testtable values( 'FY 2021',  'Q1' , 2456 , NULL , 'XYZ' , '01-01-2020');
 insert into testtable values( 'FY 2021',  'Q2' , NULL , 2456 , 'XYZ' , '01-01-2020' );
 insert into testtable values( 'FY 2021',  'Q3' , 2345 , 2456 , 'XYZ' , '01-01-2020' );
 insert into testtable values( 'FY 2021',  'Q3' , NULL , 2345 , 'XYZ' , '01-01-2020'); 
 insert into testtable values( 'FY 2021',  'Q4' , 3456 , 2345 , 'XYZ' , '01-01-2020');

Query:

 SELECT year,quarter,name,date,value, 
 (select value from testtable tt where tt.year=t.year and tt.quarter<t.quarter and value is not null
 order by quarter desc limit 1)prev_quarter_val 
 
 from testtable t

Output:

year quarter name date value prev_quarter_val
FY 2021 Q1 XYZ 2020-01-01 2456 <emnull</em
FY 2021 Q2 XYZ 2020-01-01 <emnull</em 2456
FY 2021 Q3 XYZ 2020-01-01 2345 2456
FY 2021 Q3 XYZ 2020-01-01 <emnull</em 2456
FY 2021 Q4 XYZ 2020-01-01 3456 2345

db<fiddle here

user_86516010
Related