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:
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