Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 5 of 5 total |
SQL Summing previous 12 months per record... |
Wed, Nov 15 2017 8:50 PM | Permanent Link |
Adam H. | 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. |
Thu, Nov 16 2017 4:29 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Thu, Nov 16 2017 2:35 PM | Permanent Link |
Richard Harding Wise Nutrition Coaching | 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 |
Thu, Nov 16 2017 3:03 PM | Permanent Link |
Richard Harding Wise Nutrition Coaching | 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 |
Thu, Nov 16 2017 8:31 PM | Permanent Link |
Adam H. | Thank you very much gentlemen! I'll give this a shot.
Not used to having subselects coming from DBISAM. |
This web page was last updated on Saturday, May 4, 2024 at 09:18 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |