Query to estimate fish growth 1

[andrecabrita]

Hi

I work in an aquaculture company and I was asked to create a query that could estimate the growth of the fish based on the water temperature, my problem is how can I use the fish weight that is calculated on the 1st month to calculate the 2nd month weight and so on?

Example
Pond Month WaterTemp Weight
1 1 22 120
1 2 24 125
1 3 22 135
.
....

Thank you very much.

André

 

[gmmastros]

You can perform a self join. There isn't really a join type named self join. This is really just a concept where a table joins to itself. There are some tricks and requirements that you need to use to accomplish this.

Declare @Temp Table(Pond Int, Month Int, WaterTemp int, Weight int)

Insert Into @Temp Values(1, 1, 22, 120)
Insert Into @Temp Values(1, 2, 24, 125)
Insert Into @Temp Values(1, 3, 22, 135)

Select  ThisMonth.Pond,
        ThisMonth.Month As ThisMonth,
        NextMonth.Month As NextMonth,
        ThisMonth.WaterTemp As ThisWaterTemp,
        NextMonth.WaterTemp As NextWaterTemp,
        ThisMonth.Weight As ThisWeight,
        NextMonth.Weight As NextWeight
From    @Temp As ThisMonth
        Left Join @Temp As NextMonth
          On  ThisMonth.Pond = NextMonth.Pond
          And ThisMonth.Month = NextMonth.Month - 1

Ignore the stuff at the top of the code block. It really just hard codes data in to a table structure similar to what you describe.

The important part to note is that the table is inner joined with itself. The alias part (As ThisMonth and As NextMonth) are required so that SQL Server knows which version of the table to refer to.

Also note the join condition. It has ThisMonth.Month = NextMonth.Month - 1. This is important so that the proper rows are joined together.

This is a somewhat complicated subject. If there is anything you are unsure of, please let me know.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLBill]

No. You don't have enough information to figure out growth by temperature based on one month of data. You would need to gather enough data (temperature and size) to first come up with a growth table showing temperature and growth. Then you would join to that table to determine the size of a fish based on the temperature.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[andrecabrita]

Hi,

Thank you gmmastros, it was helpfull your insights.

Best Regards.

Andre

 

[Olaf Doschke]

What you'd need is data about fishes already one month older with the same history of temperatures and weights to make an estimation via the already made experience.

But there are so many more factors like feeding frequency, food used to feed and it's weight and price, water quality, I think it's quite impossible.

I can think the history is quite the same, if the aquaculture company keeps their standards. But keeping all other parameters the same and only varying the temperature in experiments, to see at which temperature the fish grows best, is just observing one condition, in the end the ideal temperature found by just making measurements at different temperature and keeping other parameters the same might result in an ideal temperature, that's not ideal, when other parameters differ.

So in the end with just three parameters like eg food used, temperature and oxygen level you already have a multitude of conditions to observe. Then there are algae, salt and other minerals, light conditions, noise, other fish, room the fishes have, etc.

It's hard to optimize several parameters of growth at once and it takes long and much data to find the optimum for all the parameters. This is really a big data problem and even with data from several decades you can't predict something for conditions beyond what the company has done so far.

Bye, Olaf.