By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table weight_calc(cattle_id integer, weight integer, weight_date date);
insert into weight_calc values (10001,300,STR_TO_DATE("01-01-2022","%e-%m-%Y"));
insert into weight_calc values (10002,350,STR_TO_DATE("01-01-2022","%e-%m-%Y"));
insert into weight_calc values (10003,400,STR_TO_DATE("01-01-2022","%e-%m-%Y"));
insert into weight_calc values (10001,310,STR_TO_DATE("15-01-2022","%e-%m-%Y"));
insert into weight_calc values (10002,362,STR_TO_DATE("15-01-2022","%e-%m-%Y"));
insert into weight_calc values (10003,415,STR_TO_DATE("15-01-2022","%e-%m-%Y"));
insert into weight_calc values (10001,318,STR_TO_DATE("30-01-2022","%e-%m-%Y"));
insert into weight_calc values (10002,375,STR_TO_DATE("30-01-2022","%e-%m-%Y"));
insert into weight_calc values (10003,430,STR_TO_DATE("30-01-2022","%e-%m-%Y"));
select * from weight_calc
| cattle_id | weight | weight_date |
|---|---|---|
| 10001 | 300 | 2022-01-01 |
| 10002 | 350 | 2022-01-01 |
| 10003 | 400 | 2022-01-01 |
| 10001 | 310 | 2022-01-15 |
| 10002 | 362 | 2022-01-15 |
| 10003 | 415 | 2022-01-15 |
| 10001 | 318 | 2022-01-30 |
| 10002 | 375 | 2022-01-30 |
| 10003 | 430 | 2022-01-30 |
select
cattle_id,weight,
row_number() over (partition by Cattle_ID order by weight_date asc) rn
from weight_calc
| cattle_id | weight | rn |
|---|---|---|
| 10001 | 300 | 1 |
| 10001 | 310 | 2 |
| 10001 | 318 | 3 |
| 10002 | 350 | 1 |
| 10002 | 362 | 2 |
| 10002 | 375 | 3 |
| 10003 | 400 | 1 |
| 10003 | 415 | 2 |
| 10003 | 430 | 3 |
with cte as(
select Cattle_ID,
case
when (row_number() over (partition by Cattle_ID order by weight_date asc)) = 3 then weight end last_weight,
case when (row_number() over (partition by Cattle_ID order by weight_date asc)) = 2 then weight end 2ndlast_weight,
case when (row_number() over (partition by Cattle_ID order by weight_date asc)) = 1 then weight end 3rdlast_weight,
weight_date
from weight_calc)
select cattle_id,max(last_weight) "Last weight",
max(2ndlast_weight) "2nd Last Weight",
max(3rdlast_weight) "3rd Last Weight" ,
max(weight_date) "Last Weight Date"
from cte
group by cattle_id
| cattle_id | Last weight | 2nd Last Weight | 3rd Last Weight | Last Weight Date |
|---|---|---|---|---|
| 10001 | 318 | 310 | 300 | 2022-01-30 |
| 10002 | 375 | 362 | 350 | 2022-01-30 |
| 10003 | 430 | 415 | 400 | 2022-01-30 |
with cte_1 as(
select Cattle_ID,
case
when (row_number() over (partition by Cattle_ID order by weight_date asc)) = 3 then weight end last_weight,
case when (row_number() over (partition by Cattle_ID order by weight_date asc)) = 2 then weight end 2ndlast_weight,
case when (row_number() over (partition by Cattle_ID order by weight_date asc)) = 1 then weight end 3rdlast_weight,
weight_date
from weight_calc
), cte_2 as (
select cattle_id,max(last_weight) lw,
max(2ndlast_weight) lw_2,
max(3rdlast_weight) lw_3 ,
max(weight_date) lwd
from cte_1
group by cattle_id)
select cte_2.cattle_id,
cte_2.lw as "Last Weight",
cte_2.lw_2 as "2nd Last Weight",
cte_2.lw_3 as "3rd Last Weight",
case when (lw - lw_3)>=30 then "Satisfactory" else "Not satisfactory" end remarks,
cte_2.lwd as "Last Weight"
from cte_2
| cattle_id | Last Weight | 2nd Last Weight | 3rd Last Weight | remarks | Last Weight |
|---|---|---|---|---|---|
| 10001 | 318 | 310 | 300 | Not satisfactory | 2022-01-30 |
| 10002 | 375 | 362 | 350 | Not satisfactory | 2022-01-30 |
| 10003 | 430 | 415 | 400 | Satisfactory | 2022-01-30 |