0

This is how my database table looks like:

cattle_id weight weight_date
10001 300 KG 01-01-2022
10002 350 KG 01-01-2022
10003 400 KG 01-01-2022
10001 310 KG 15-01-2022
10002 362 KG 15-01-2022
10003 415 KG 15-01-2022
10001 318 KG 30-01-2022
10002 375 KG 30-01-2022
10003 430 KG 30-01-2022

I need a query to get the result like below:

Cattle ID Last Weight 2nd Last Weight 3rd Last Weight Remarks Last Weight Date
10001 318 KG 310 KG 300 KG Not Satisfactory 30-01-2022
10002 375 KG 362 KG 350 KG Not Satisfactory 30-01-2022
10003 430 KG 415 KG 400 KG Satisfactory 30-01-2022

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?

Jahangir Hossain
  • 3
  • 2
  • Does this answer your question? [How can I return pivot table output in MySQL?](https://stackoverflow.com/questions/7674786/how-can-i-return-pivot-table-output-in-mysql) – Stu May 29 '22 at 12:54
  • *How can I acheive this?* ROW_NUMBER in CTE and conditional aggregation (or 3 table copies) in outer query. To check for Satisfaction use DATEDIFF. – Akina May 29 '22 at 13:11
  • Thanks @Stu! Lil bit close to my question but in ur recommended answer there is one action column which is already defined. But in my table I have only id and value. I have to fetch the data then display in a table and in one extra column I want to show my remarks. – Jahangir Hossain May 29 '22 at 13:12
  • Dear @Akina! I have basic knowledge in mysql but I m not an expert. So it will be helpful if u can elaborate ur answer more for my better understanding. Thanks – Jahangir Hossain May 29 '22 at 13:15
  • #1) Tell us what is your MySQL version (show the output for `SELECT VERSION();`). #2) Replace "This is how my database table looks like" with CREATE TABLE + INSERT INTO scripts. – Akina May 29 '22 at 13:33
  • "Last weight date" in expected output doesnot looks correct unless there is some other logic for it, not mentioned in the question. Last weight date for all cattle_id is 30-01-2022. – Pankaj May 29 '22 at 15:47
  • Yape that's a mistake @Pankaj. Last weight date for all cattle will be 30-01-2022 – Jahangir Hossain May 29 '22 at 16:14

1 Answers1

0

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.

Pankaj
  • 2,692
  • 2
  • 6
  • 18
  • That is great!! Thanks a lot @Pankaj. I have to make some changes as all my cattles are not weighted after 15 days and new ones are weighted 1 or 2 times but old ones are weighted many times as they stayed for long. I have to figure out to get only the last 3 weight of cattles. Thanks anyway. – Jahangir Hossain May 30 '22 at 09:43