I recently posted this question already related to my Mysql database which I am using locally on my laptop. Since I did not find a solution to the problem in Mysql and others did not seem to find one either I would like to post it again but now related to R. I use the database with the "RMysql" package. Here is the problem:

I have a database table that contain data of different farming pratices during several years on several fields. Farmers entered there information based on the dates when a certain activity was done. A simplified example would look like this, where ID is an auto-increment value.

```
ID|Field|Date      |Activity  |
 1|A    |2012/08/01|Tillage   |
 2|A    |2012/08/24|Seeding   |
 3|A    |2013/03/05|Spraying  |
 4|A    |2013/03/05|Fertilizer|
 5|A    |2013/07/25|Harvest   |
 6|B    |2012/09/01|Tillage   |
 7|B    |2012/09/05|Seeding   |
 8|B    |2013/04/05|Spraying  |
 9|B    |2013/07/28|Harvest   |
10|B    |2010/08/24|Tillage   |
11|B    |2010/09/29|Seeding   |
12|B    |2011/05/01|Fertilizer|
13|B    |2011/07/12|Harvest   |
14|A    |2011/09/01|Seeding   |
15|A    |2011/10/10|Spraying  |
16|A    |2012/04/10|Fertilizer|
17|A    |2012/08/02|Harvest   |

```

Now I would like to add a column called "Season" that contains only the year when a field was harvested but is automatically updated for all other activites between the last harvest and this harvest for individual fields. Note that harvest dates are different for different fields.Another point is that the seeding was maybe done in 2011 but the field was harvested in 2012 and all this should then be called season 2012. In this example the output would ideally look like:

```
ID|Field|Date      |Activity  |Season
 1|A    |2012/08/01|Tillage   |2013
 2|A    |2012/08/24|Seeding   |2013
 3|A    |2013/03/05|Spraying  |2013
 4|A    |2013/03/05|Fertilizer|2013
 5|A    |2013/07/25|Harvest   |2013
 6|B    |2012/09/01|Tillage   |2013
 7|B    |2012/09/05|Seeding   |2013
 8|B    |2013/04/05|Spraying  |2013
 9|B    |2013/07/28|Harvest   |2013
10|B    |2010/08/24|Tillage   |2011
11|B    |2010/09/29|Seeding   |2011
12|B    |2011/05/01|Fertilizer|2011
13|B    |2011/07/12|Harvest   |2011
14|A    |2011/09/01|Seeding   |2012
15|A    |2011/10/10|Spraying  |2012
16|A    |2012/04/10|Fertilizer|2012
17|A    |2012/08/02|Harvest   |2012

```

Can anyone help?

**EDIT:**

What would I need to change if I start recording more information and give all "Activities" a separate column?
I tried:

```
DF <- read.table(text="ID|Field|Date      |Tillage|Seeding|Fertilizer|Spraying|Harvest
 1|A    |2012/08/01|Plough   |NA|NA|NA|NA
 2|A    |2012/08/24|NA   |Wheat|NA|NA|NA
 3|A    |2013/03/05|NA  |NA|NA|ProduktA|NA
 4|A    |2013/03/05|NA|NA|TypeB|NA|NA
 5|A    |2013/07/25|NA   |NA|NA|NA|9t
 6|B    |2012/09/01|Plough   |NA|NA|NA|NA
 7|B    |2012/09/05|NA   |Barley|NA|NA|NA
 8|B    |2013/04/05|NA  |NA|NA|ProductB|NA
 9|B    |2013/07/28|NA   |NA|NA|NA|10t
10|B    |2010/08/24|Cultivator   |NA|NA|NA|NA
11|B    |2010/09/29|NA   |NA|NA|NA|NA
12|B    |2011/05/01|NA|NA|TypeB|NA|NA
13|B    |2011/07/12|NA   |NA|NA|NA|6t
14|A    |2011/09/01|NA   |Barley|NA|NA|NA
15|A    |2011/10/10|NA  |NA|NA|ProductC|NA
16|A    |2012/04/10|NA|NA|TypeA|NA|NA
17|A    |2012/08/02|NA   |NA|NA|NA|7t|", 
                 sep="|", header=TRUE, stringsAsFactors=FALSE)

DT <- data.table(DF)
DT[, Harvest:=gsub(" ", "", Harvest, fixed=TRUE)]
DT[, Date:=as.POSIXct(Date)]
setkeyv(DT, c("Field", "Date"))
DT[, Season:=cumsum(c("", !is.na(head(Harvest, -1)))), by=Field]
DT[, Season:=max(year(Date)), by=list(Field, Season)]

```

This does unfortunately not work. Can someone solve this,too?