I have over 50 buildings with measured utility systems (electric, steam, water, etc.). I currently am using a Table Lookup to a OLEDB (Provider) query to determine monthly totals of each utility. I'd like to improve this method of retrieving data for past months... as time goes by, it's taking longer and longer to get the results because I'm querying monthly totals since my base year 2010. There is no need to query 2010, 2011, 2012, and even 2013 months every time I want to see the building(s) usage. How can I create a way to efficiently calculate monthly totals as time goes on, but keep from querying data that is long enough in the past to not have to query each and every time? I think I'm trying to ask is there a way to keep it so I have one OLEDB table for each building that returns for each type of meter monthly totals, but after a certain length in time have it so the attributes looking at long ago months not update every time you look at the attributes? I also don't want to get crazy with creating multiple tables for each building...
I'm providing what I'm querying in my table for a smaller building with one water meter:
select a1.time Month, a1.tag "Water Tag 1", a1.value * 1440 "Domestic Water Meter 1"
from (select tag, time, value from piarchive..pitotal where tag = 'DomWtrFlow' and time between '1/1/2010' and 't' and timestep = '1mo') a1
OPTION (FORCE ORDER)
Any help is appreciated!