Hello, everyone, I have a table like this.
with t as (
select 1 soz_no, 5 last_meter, null first_meter, 202103 read_period from dual union all
select 1 soz_no, 4 last_meter, null first_meter, 202102 read_period from dual union all
select 1 soz_no, 3 last_meter, 2 first_meter, 202101 read_period from dual union all
select 1 soz_no, 2 last_meter, 1 first_meter, 202012 read_period from dual union all
select 1 soz_no, 1 last_meter, null first_meter, 202011 read_period from dual union all
select 2 soz_no, 9 last_meter, null first_meter, 202103 read_period from dual union all
select 2 soz_no, 8 last_meter, null first_meter, 202102 read_period from dual union all
select 2 soz_no, 7 last_meter, null first_meter, 202101 read_period from dual union all
select 2 soz_no, 6 last_meter, null first_meter, 202012 read_period from dual union all
select 2 soz_no, 5 last_meter, 4 first_meter, 202011 read_period from dual union all
select 2 soz_no, 4 last_meter, 3 first_meter, 202010 read_period from dual
)
select * from t;
what I need is for a given period display soz_no, last_meter, first_meter and the difference between last_meter and first_meter.
As it seen from the table, sometimes first_meter values may be null. In this case I have to get first value as latest, not null value based on read_period desc.
select soz_no, last_meter, first_meter, (last_meter-first_meter) as diff_meter, read_period from t
...
where read_period = 202103
...
For example, for a 202103 period, my desired output will be like below
in the above example, diff_meter is the difference between last_meter and first_meter.
So let's consider the first row of our output. Last_meter is 5 for a given read_period(in our example it is 202103) but first_meter is null for this read_period so we are looking at it in descending order based on read_period it is 202102 but again first_meter is null, we continue to looking and we got not null in 202101. So we take it as first_meter --> it is 2. at the final we take last_meter from 202103 but first_meter is null for 202103, 202102 that's why we take 202101's first_meter. The diff_meter column is simply 5-2=3.
edit note:
I changed column names.