# SolvedQuery question (1 Viewer)

#### boerbende

##### Ben

A question I am dealing with for a long time now
As company we are controlling all kind of emission. Each year we are improving and each year the government decreases the limits to motivate companies to continuous improvement. So far so good
In reality we have a lot of data, but I have tried to summarize my problem in a very simple example

What do we have:

tblData
 TAG Datemeasured VAL 1​ 01-01-2017​ 4​ 1​ 01-01-2018​ 3​ 1​ 01-01-2019​ 2.5​ 1​ 01-01-2020​ 2​ 1​ 01-01-2021​ 1.5​

tblLimits (from government)

 TAG Datefrom DateTo MaxVAL 1​ 01-01-2019​ 31-12-2019​ 3​ 1​ 01-01-2020​ 31-12-2020​ 2.5​ 1​ 01-01-2020​ 2​

Question:

How do I get this result
 TAG Datemeasured VAL MAX 1​ 01-01-2017​ 4​ 1​ 01-01-2018​ 3​ 1​ 01-01-2019​ 2.5​ 3​ 1​ 01-01-2020​ 2​ 2.5​ 1​ 01-01-2021​ 1.5​ 2​

I tried 2 things
1)
Select Datemeasured, VAL, MAXVAL
FROM tblData left join tblLimits on tblData .TAG = tbllimits.TAG
WHERE TAG = 1 AND Datefrom <= Datemeasured AND (isnull(Dateto) or Dateto>= Datemeasured)

This gives me however results without 2017 and 2018 because there were no limits that time

2) Nested query
Select Datemeasured, VAL,
(
Select MAXVAL
FROM tblLimits WHERE TAG = 1 AND Datefrom <= Datemeasured and (isnull(Dateto) or Dateto>= Datemeasured)
) as MAXVAL
FROM tblData WHERE TAG = 1

This query gives me the requested table but has a very poor performance when dealing with large amount of measurements (which we have in reality)

What I usually do is to record MIN-MAX besides the value of measurement. But in this case not only a measurement has a MIN, MAX, but also an average over a timespan (which can be anything from 24hours / week to 1 quarter/year). We have not (yet) a system which is recording every possible MIN, MAX together with the measurement

I know I can solve the query with coding, but is there a way to do this with a query with an acceptable performance?

Many thanks

Ben

#### arnelgp

##### ..forever waiting... waiting for jellybean!
untested, and you will need to enter this in SQL view
the designer will complain about having <= and >= on
the Join:
Code:
``````SELECT T1.TAG, T1.DateMeasured, T1.VAL, T2.MAXVal AS MAX
FROM tblData AS T1
LEFT JOIN
tblLimits AS T2
ON T1.TAG = T2.TAG
AND T1.DateMeasured >= T2.Datefrom AND T1.DateMeasured <= T2.DateTo;``````

Last edited:

#### arnelgp

##### ..forever waiting... waiting for jellybean!
something is missing:
also on your data, the Last Limit record Overlap with the 2nd
to the last record date?
Code:
``````SELECT T1.TAG, T1.DateMeasured, T1.VAL, T2.MAXVal AS MAX
FROM tblData AS T1
LEFT JOIN
tblLimits AS T2
ON T1.TAG = T2.TAG
AND T1.DateMeasured >= T2.Datefrom AND T1.DateMeasured <= Nz(T2.DateTo, #12/31/9999#);``````

Last edited:

#### boerbende

##### Ben
Hi

thanks all for looking into this.
@arnelgp. Many thanks. I tried your solution but it gave me the same result as I already had: no records when no limit was found

I left joined the two tables and created the output in two steps
• FIRST without constraints on date but with all datefields in the same query
• Then I used the first query as input for the next query where I used the constraint below to get the real maximum
MAXVAL: IIf([datefrom] <=[datemeasured] AND (isnull([dateto] > [datemeasured]) OR [dateto]);[MAXVALUE];NULL)

#### arnelgp

##### ..forever waiting... waiting for jellybean!
post #3 sql will solve the missing limit.

Replies
9
Views
410
Replies
8
Views
1,292
Replies
3
Views
662
Replies
38
Views
470
Replies
5
Views
562