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
tblLimits (from government)
Question:
How do I get this result
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
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