This is how my database table looks like:

I need a query to get the result like below:

Note: Weight measured after every 15 days & at least 1 KG growth per day is Satisfactory.

The data is inserted to database properly. I just want to retrieve data like above. How can I acheive this?

CodePudding user response：

Following query can be used -

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

Here is the db fiddle.