Hi,

I was wondering what might be the best method of trying to achieve the following. I'll use Monthly temperatures as an example.

The source records will be:

Date  
Max Temperature

The result table I'm looking for will have:
Date
Max Temperature
Average Max Temperature for immediate past 12 months...




Source example:
DATE   MAX
...
1 Jan 17   78°
2 Jan 17   76°
3 Jan 17   77°
...




Result example:
...
DATE   MAX   12m AVG
1 Jan 17   78°   85°
2 Jan 17   76°   86°
3 Jan 17   77°   85°
...

I'm just wondering how best I can achieve this with EDB?

Thanks & Regards

Adam.

Adam


I think subselects would work. Can you post some example data (use export table with data) and I'll happily have a play.

Roy Lambert

Greetings Adam

How about this?

SELECT CalendarDate, MaxTemperature,
   (SELECT Max(MaxTemperature)
     FROM Temperatures
     WHERE CalendarDate BETWEEN CURRENT_DATE - INTERVAL '12' MONTH AND CURRENT_DATE) AS MaxPrev12Months
 FROM Temperatures

You will probably need to adjust the WHERE clause to get Maximum from the previous 12 months to be in the desired range.

Richard

You have probably worked it out already but you need AVG instead of MAX in the SELECT statement.

   (SELECT AVG(MaxTemperature)
     FROM Temperatures
     WHERE CalendarDate BETWEEN CURRENT_DATE - INTERVAL '12' MONTH AND CURRENT_DATE) AS MaxPrev12Months

Richard

Thank you very much gentlemen! I'll give this a shot.

Not used to having subselects coming from DBISAM.