Solved Query question (1 Viewer)

boerbende

Ben
Local time
Today, 08:38
Joined
Feb 10, 2013
Messages
339
Dear readers,

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

TAGDatefromDateToMaxVAL
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
TAGDatemeasuredVALMAX
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!
Local time
Today, 15:38
Joined
May 7, 2009
Messages
19,169
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!
Local time
Today, 15:38
Joined
May 7, 2009
Messages
19,169
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
Local time
Today, 08:38
Joined
Feb 10, 2013
Messages
339
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 thought about a workaround
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!
Local time
Today, 15:38
Joined
May 7, 2009
Messages
19,169
post #3 sql will solve the missing limit.
 

Users who are viewing this thread

Top Bottom