×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • 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.

Students Click Here

Reset cumulative sum to zero on every year

thread436-1818310

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue
"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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue
"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.)

CODE --> PHP

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

CODE --> excerpt_from_query

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

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.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
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:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close