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;
Thanks,
Doug