Reset cumulative sum to zero on every year
Reset cumulative sum to zero on every year
(OP)
I am trying to find on what date each year since 2009 when the GDD (growing degree days) is <= 1035. I can get the following to work for one year, but will not work for multiple years. I am trying to figure out how to reset the @cum = 0 for every year and then display my date result by year. Since this is a one time call, I tried to UNION ALL for each year needed, but produced the first year result and NULL in all other years.
My db PHP version is 5.6
Thank you
My db PHP version is 5.6
CODE --> MySQL
SELECT MaX(WxDate), MAX(CumulativeGDD) AS MaxGDD FROM( SELECT WxDate, GDD, CAST((@csum := @csum + GDD) AS decimal(5,1)) AS CumulativeGDD FROM( SELECT WxDate, CASE WHEN (Tmax+Tmin)/2 > 50 THEN (Tmax+Tmin)/2 - 50 ELSE 0 END AS GDD FROM( SELECT WxDate, MIN(`TempOutCur`) AS Tmin, MAX(`TempOutCur`) AS Tmax FROM `weatherbridge` WHERE WxDate >= '2009-01-01' AND WxDate < '2022-08-01' GROUP BY WxDate ) AS S1 ) AS S2 JOIN (SELECT @csum:=0) r ) AS S3 WHERE CumulativeGDD <= 1035
Thank you
RE: Reset cumulative sum to zero on every year
We have no idea of your table structure or of your table data.
What happens in vagueness, stay in vagueness!
Skip,
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!
RE: Reset cumulative sum to zero on every year
GDD = ((Max Temperature for Day + Min Temp for Day / 2) - 50(fifty is a constant value). If GDD is > 0 then the value is summed as a running total since the first day of the year.
I am interested on what Date before the GDD hits 1035.
The table structure is straight forward with a column for Date and a column for Current Temperature. I collect 288 temperatures a day (every 5 minutes). the following table only has 2 data points for a few days.
CODE --> Table
On 5/1/2022 the GDD = (40+62.1)/2 - 50 = 2
On 5/2/2022 the GDD = 6.5 and the CumulativeGDD = 8.5
On 5/3/2022 the GDD = -6.5 since this is less than 0 the GDD = 0 and the CumulativeGDD = 8.5
The running sum continues until the CumulativeGDD > 1035.
I need this number for each year going back to 2009. I did figure out the UNION issue, but still interested in an alternate solution.
If more information is required, I will do my best to provide.
Thank you
RE: Reset cumulative sum to zero on every year
Just as you should do to prepare to test any supplied code, please provide sample data and the results you expect to receive for each year.
Skip,
Just traded in my OLD subtlety...
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!
RE: Reset cumulative sum to zero on every year
If I change the WHERE statement to fit each year of interest, it works, but there are too many years to query individually.
CODE --> MySQL
CODE --> MySQL
The expected output would look like this, which is the date before the cumGDD goes over the threshold, in this example is set to 15.
CODE --> MySQL
RE: Reset cumulative sum to zero on every year
Time of day is not relevant. The sample data shows two temperatures (high/low) as two rows for each day.
There was a brief mention of PHP. Is this rendering in a web page via PHP? Couldn't you simply loop through the years with PHP, feeding the stepped year to multiple queries? (I'm sure there is still a more elegant way that it could be done exclusively in SQL, but you caught me at the end of a day when the brain is fried.)
CODE --> PHP
CODE --> excerpt_from_query
RE: Reset cumulative sum to zero on every year
CODE
and got this result:
CODE
I think this is the table where you can examine yearly GDD cumulatives
RE: Reset cumulative sum to zero on every year
CODE
the above statements deliver the result you expected
CODE
RE: Reset cumulative sum to zero on every year