SQL

[Tirnanah]

Finally making it back to doing something with all the data I've been collecting, and wrote this bit of SQL. I know SQL isn't the most fun place to do it, but as a DB programmer, it was bugging me that I could get it to work... The first query creates a temp table to summarize the data into, the second is a day long query to show the total usage. I checked against my smartenergygroups information, and I'm within about 6 to 10 wh's to them... Next step for me is to write something to summarize down based on how long the data has been there... Anyway, no warranty or anything, but I thought I would share in case it might help someone else. Note this was written for SQL server, so if you try to run it against something else, you'll probably have to modify...

Code: Select all

if object_id( 'tempdb..tempEnergyInfo' ) is not null
	drop table #tempEnergyInfo ;

select  createTimestamp as startTime,
		lead( createTimestamp ) over ( partition by measurementChannelID, measurementType
											order by createtimestamp ) as endTime,
		measurementChannelId,
		dateDiff( ss, createTimestamp, lead( createTimestamp ) over ( partition by measurementChannelID, measurementType
											order by createtimestamp ) ) as secDiff,
		lead( measurementValue ) over ( partition by measurementChannelID, measurementType
											order by createtimestamp ) - measurementValue as AWSDiff
into	#tempEnergyInfo
from	measurements
where /*measurementChannelID = '00E40940-9199-4ED8-8815-3DB3270C45A7' 
and */	measurementType = 'AWS'
and		createTimestamp between '10/16/2014' and '10/17/2014'
order by createTimestamp ;


select	tei.measurementChannelID,
		chan.channelPanel,
		isNull( chan.channelPanelNum, '0' ) as channelPanelNum,
		chan.channelName,
		sum( tei.secDiff ) as totalTime,
		sum( tei.awsDiff ) as totalEnergy,
		sum( tei.awsDiff ) / sum( tei.secDiff ) as watts,
		sum( tei.awsdiff ) / 3600 as wh
from #tempEnergyInfo tei
		inner join channels chan on tei.measurementChannelID = chan.channelID
--where measurementChannelID = '00E40940-9199-4ED8-8815-3DB3270C45A7' 
group by tei.measurementChannelID, chan.channelPanel, chan.channelPanelNum, chan.channelName 
order by channelPanel desc, channelPanelNum;

I can provide table definitions and things if someone is really interested... I use BTMon to push it into a local mysql DB, then use a job to pull it down into my SQL server every 10 minutes...

Thanks,
Doug