×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# 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

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

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,

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

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

#### CODE --> Table

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

### RE: Reset cumulative sum to zero on every year

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,

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

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

#### CODE --> MySQL

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

#### CODE --> MySQL

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.

#### CODE --> MySQL

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

### RE: Reset cumulative sum to zero on every year

#### Quote (SkipVought)

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

### RE: Reset cumulative sum to zero on every year

I tried this

#### CODE

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:

#### CODE

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

### RE: Reset cumulative sum to zero on every year

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

#### CODE

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

#### CODE

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

### RE: Reset cumulative sum to zero on every year

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

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Close Box

# Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!