Subscribe to this thread
Home - General / All posts - Query to Subtract Values From Different Years
eolsonAG22 post(s)
#12-Mar-21 17:20

I am trying to write a query to use that will accomplish the task of subtracting pH values from a database based on the year that the record has associated with it.

I work for an agricultural company and we are working with soil sampled data. A field may have its soil sampled every 1-5 years, for example, and therefore a query just saying subtract 2018 from 2021 values won't work because we don't know which year was the last time.

Therefore, I am trying to write a query that access the first time a field had a sample taken, and then subtracting that from the most recent sample that was taken for it. This could end up with one field having a change of just 2 years between samples while another has 9.

To reiterate and simplify as much as possible:

I want to subtract two values that will be in the same column (Soil_PH) based on their value in the GrowingYear column.

I will attach a CSV of the type of data that I am working with. Additionally, I will attach the query I currently use to pull in data for a specific year, or I can alter it a bit to pull in all records from a few years.

Attachments:
Lab Data Query.txt
PH2020 2.csv

eolsonAG22 post(s)
#12-Mar-21 18:37

In plain text the query is below in case the attachment presents issues:

SELECT COMPANY_ID, GROWINGYEAR, SAMPLEID, FIELD, SECTION, SOIL_PH,

CEC_MEQ, LTRIM(RTRIM(FIELD)) + RTRIM(LTRIM(SECTION)) AS FieldSect, dateOFREPORT, AVAILP

FROM dbo.LABVALUE AS A

WHERE (COMPANY_ID = '013771') AND (GROWINGYEAR = 2020)

Mike Pelletier

2,122 post(s)
#12-Mar-21 19:49

Look in the manual for SQL expressions to extract the year out of a date and convert data types so you can do the subtraction. Also you may need to use #xxxx# to bracket dates

kdgunther3 post(s)
#13-Mar-21 18:56

Try this query to get you going in the right direction:

SELECT [COMPANY_ID],

[FIELD],

MIN(DateTimeYear([dateOFREPORT])) AS [Least Recent],

MAX(DateTimeYear([dateOFREPORT])) AS [Most Recent],

MAX(DateTimeYear([dateOFREPORT]))-MIN(DateTimeYear([dateOFREPORT])) AS [Years Difference]

FROM [PH2020 2]

WHERE [COMPANY_ID] = '013771' AND [GROWINGYEAR] = 2020

GROUP BY [COMPANY_ID],[Field];

Manifold User Community Use Agreement Copyright (C) 2007-2021 Manifold Software Limited. All rights reserved.