Reset cumulative sum to zero on every year

[waubain]

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

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

 

[SkipVought]

What happens in vagueness, stay in vagueness!

We have no idea of your table structure or of your table data.

What happens in vagueness, stay in vagueness!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"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!

 

[waubain]

Sorry for the vagueness. In agriculture the cumulative Growing Degree Days (GDD) has correlation with crop growth development and emergence of certain pests. GDD can be calculated multiple ways, but the simplest is

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.

WxDate                  TempOutCur
2022-05-01 06:00	40.0
2022-05-01 13:05	64.1
2022-05-02 05:00	43.0
2022-05-02 12:10	70.3
2022-05-03 05:25	38.1
2022-05-03 12:15	49.2
2021-04-26 05:45	33.0
2021-04-26 12:05	45.1
2021-04-27 05:55	41.4
2021-04-27 12:25	68.2
2021-04-28 05:15	38.1
2021-04-28 12:30	69.5

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

 

[SkipVought]

So you want each member who decides to try to help you, to work to mock up data for multiple years while you sit at home? I don't think so.

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"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!

 

[waubain]

I am not sure what you are asking, but will try again, Here is sample data.

If I change the WHERE statement to fit each year of interest, it works, but there are too many years to query individually.

 WHERE WxDate >= '2022-01-01' ANd  WxDate < '2022-08-01'

DROP TEMPORARY TABLE IF EXISTS tempTable ;
CREATE TEMPORARY TABLE tempTable(WxDate date, TempOutCur float);
INSERT INTO tempTable VALUES 
			('2022-05-18', 40)
                        ,('2022-05-18', 52)
			,('2022-05-18', 70)
                        ,('2022-05-19', 45)
			,('2022-05-19', 51)
			,('2022-05-19', 65)
			,('2022-05-20', 44)
                        ,('2022-05-20', 52)
			,('2022-05-20', 71)
			,('2021-05-26', 51)
			,('2021-05-26', 58)
                        ,('2021-05-26', 65)
		        ,('2021-05-27', 56)
			,('2021-05-27', 61)
			,('2021-05-27', 74)
			,('2021-05-28', 51)
                        ,('2021-05-28', 58)
                        ,('2021-05-28', 65)
			,('2020-05-01', 51)
                        ,('2020-05-01', 66)
			,('2020-05-01', 75)
                        ,('2020-05-02', 44)
			,('2020-05-02', 50)
                        ,('2020-05-02', 69)
			,('2020-05-03', 52)
			,('2020-05-03', 59)
			,('2020-05-03', 68)
			;

SELECT
	MaX(WxDate),
	MAX(CumulativeGDD) AS cumGDD
FROM(
    SELECT
        WxDate,
        GDD,
        CAST((@asum := @asum + 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 `tempTable` 
            WHERE WxDate >= '2022-01-01' ANd  WxDate < '2022-08-01'
            GROUP BY WxDate
            ) AS S1
        ) AS S2 
    JOIN (SELECT @asum:=0) a
    ) AS S3
WHERE CumulativeGDD <= 15

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.

MaX(WxDate) 	cumGDD 	
2022-05-19      10.0
2021-05-26       8.0
2020-05-01 	13.0

 

[spamjim]

However, the data you supplied baked into your code rather than just simply a table, fails to include TIME in order to get MIN & MAX for each day.

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.)

for ($year = 2009; $year <= 2022; $year++) {
  // run your query
}

...WHERE WxDate >= '" . $year . "-01-01' AND  WxDate < '" . $year . "-12-31'...

 

[mikrom]

I tried this

create or replace table TEMPTABLE(
  WxDate date, 
  TempOutCur float
)
;

insert into TEMPTABLE values 
  ('2022-05-18', 40),
  ('2022-05-18', 52),
  ('2022-05-18', 70),
  ('2022-05-19', 45),
  ('2022-05-19', 51),
  ('2022-05-19', 65),
  ('2022-05-20', 44),
  ('2022-05-20', 52),
  ('2022-05-20', 71),
  ('2021-05-26', 51),
  ('2021-05-26', 58),
  ('2021-05-26', 65),
  ('2021-05-27', 56),
  ('2021-05-27', 61),
  ('2021-05-27', 74),
  ('2021-05-28', 51),
  ('2021-05-28', 58),
  ('2021-05-28', 65),
  ('2020-05-01', 51),
  ('2020-05-01', 66),
  ('2020-05-01', 75),
  ('2020-05-02', 44),
  ('2020-05-02', 50),
  ('2020-05-02', 69),
  ('2020-05-03', 52),
  ('2020-05-03', 59),
  ('2020-05-03', 68)
;

select * from TEMPTABLE
;

with 
TEMPERATURES1(WXYEAR, WXDATE, MINTEMP, MAXTEMP) as (
  select
    year(WXDATE),
    WXDATE,
    min(TEMPOUTCUR), 
    max(TEMPOUTCUR) 
  from
    TEMPTABLE
  group by WXDATE
  order by WXDATE
),
TEMPERATURES2(WXYEAR, WXDATE, MINTEMP, MAXTEMP, GDD) as (
  select
    WXYEAR, 
    WXDATE, 
    MINTEMP, 
    MAXTEMP,
    case 
      when(MINTEMP + MAXTEMP)/2 > 50 then (MINTEMP + MAXTEMP)/2 - 50 
      else 0
    end as GDD
  from 
    TEMPERATURES1
),
TEMPERATURES3(WXYEAR, WXDATE, MINTEMP, MAXTEMP, GDD, CUMGDD) as (
select
  t1.WXYEAR,
  t1.WXDATE,
  t1.MINTEMP,
  t1.MAXTEMP, 
  t1.GDD,
  sum(t2.GDD) as CUMGDD
from 
  TEMPERATURES2 t1
inner join 
  TEMPERATURES2 t2 on t1.WXDATE >= t2.WXDATE and t1.WXYEAR = t2.WXYEAR
group by t1.WXYEAR, t1.WXDATE, t1.MINTEMP, t1.MAXTEMP, t1.GDD
)
select * 
from TEMPERATURES3
order by WXYEAR, WXDATE
;

and got this result:

WXYEAR  WXDATE       MINTEMP MAXTEMP     GDD  CUMGDD
2020	2020-05-01	51.0	75.0	13.0	13.0
2020	2020-05-02	44.0	69.0	6.5	19.5
2020	2020-05-03	52.0	68.0	10.0	29.5
2021	2021-05-26	51.0	65.0	8.0	8.0
2021	2021-05-27	56.0	74.0	15.0	23.0
2021	2021-05-28	51.0	65.0	8.0	31.0
2022	2022-05-18	40.0	70.0	5.0	5.0
2022	2022-05-19	45.0	65.0	5.0	10.0
2022	2022-05-20	44.0	71.0	7.5	17.5

I think this is the table where you can examine yearly GDD cumulatives

 

[mikrom]

if you want to go further to the end result, then this provides the result you desired

with 
TEMPERATURES1(WXYEAR, WXDATE, MINTEMP, MAXTEMP) as (
  select
    year(WXDATE),
    WXDATE,
    min(TEMPOUTCUR), 
    max(TEMPOUTCUR) 
  from
    TEMPTABLE
  group by WXDATE
  order by WXDATE
),
TEMPERATURES2(WXYEAR, WXDATE, MINTEMP, MAXTEMP, GDD) as (
  select
    WXYEAR, 
    WXDATE, 
    MINTEMP, 
    MAXTEMP,
    case 
      when(MINTEMP + MAXTEMP)/2 > 50 then (MINTEMP + MAXTEMP)/2 - 50 
      else 0
    end as GDD
  from 
    TEMPERATURES1
),
TEMPERATURES3(WXYEAR, WXDATE, MINTEMP, MAXTEMP, GDD, CUMGDD) as (
select
  t1.WXYEAR,
  t1.WXDATE,
  t1.MINTEMP,
  t1.MAXTEMP, 
  t1.GDD,
  sum(t2.GDD) as CUMGDD
from 
  TEMPERATURES2 t1
inner join 
  TEMPERATURES2 t2 on t1.WXDATE >= t2.WXDATE and t1.WXYEAR = t2.WXYEAR
group by t1.WXYEAR, t1.WXDATE, t1.MINTEMP, t1.MAXTEMP, t1.GDD
),
TEMPERATURES4(WXYEAR, WXDATE, CUMGDD) as ( 
  select WXYEAR, WXDATE, CUMGDD 
  from TEMPERATURES3
  where CUMGDD <= 15
),
TEMPERATURES5(MAX_WXDATE) as (
select
  max(WXDATE) 
from 
  TEMPERATURES4
group by WXYEAR
),
TEMPERATURES6(MAX_WXDATE, CUMGDD) as (
select 
  t1.MAX_WXDATE,
  t2.CUMGDD
from 
  TEMPERATURES5 t1 inner join
  TEMPERATURES4 t2 on t1.MAX_WXDATE = t2.WXDATE
)
select * from TEMPERATURES6
;

the above statements deliver the result you expected

MAX_WXDATE    CUMGDD
2022-05-19	10.0
2021-05-26	8.0
2020-05-01	13.0

 

[waubain]

Thanks everyone for responding. I will give these options a try.