# Given the current trend, when will I reach my ideal weight ?

I’ve been recording my weight for a while now, and trying to ge to my ideal weight. This is a slow process and takes a lot of time. In order to see the light at the end of the tunnel, I started trying to project given the current weight loss streak, when would I reach my ideal weight.

It’s a mater of performing a linear regression over the exiting data.

I record my weight multiple times a day, mostly because the Viva OMRON scale I have seems to be kind of flaky, and I average it over the day.

To graph the current streak data I would need the list of day averages relative to the start date:

SELECT avg(weight) AS weight, DATEDIFF(date(record_date), start_date) AS day
FROM weight_log WHERE record_date > @start_date GROUP BY date(record_date);


The slope formula is:

$slope = \frac{\sum{(y - \overline{y}) (x - \overline{x})}}{\sum{(x - \overline{x})^2}}$

The intercept formula is:

$intercept = \overline{y} - slope \cdot \overline{x}$

The formula for linear regression slope needs the average of both X and Y axis.

SELECT
avg(weight) AS weight_average,
avg(day) AS day_average
FROM (
SELECT
avg(weight) AS weight,
DATEDIFF(date(record_date), @start_date) AS day
FROM weight_log
WHERE record_date > @start_date GROUP BY date(record_date)
) AS A;


Since we will need the data in several places, let’s use stored procedure and save it in memory table for reuse.

DELIMITER //
DROP PROCEDURE IF EXISTS get_weight_slope //

CREATE PROCEDURE get_weight_slope(
IN start_date DATE
)
BEGIN
DECLARE weight_average DECIMAL(10,3);
DECLARE days_average DECIMAL(10,2);

SET @@session.sql_notes = 0;
DROP TEMPORARY TABLE IF EXISTS weight_slope_information;
CREATE TEMPORARY TABLE weight_slope_information engine=memory
SELECT
avg(weight) AS weight,
DATEDIFF(date(record_date), start_date) AS day
FROM weight_log
WHERE record_date > start_date GROUP BY date(record_date);

SELECT avg(weight), avg(day) INTO weight_average, days_average FROM weight_slope_information;

SELECT
sum((weight - weight_average)*(day - days_average)) /
sum((day-days_average)*(day - days_average)) AS slope,
weight_average -
sum((weight - weight_average)*(day - days_average)) /
sum((day-days_average)*(day - days_average)) * days_average AS intercept
FROM weight_slope_information;

END //



With the returned information you can compute the number of days from start that will be needed to reach a specific weight:

$days = target - \frac{intercept}{slope}$