Calculate Daily/Monthly Energy consumption in SQL Server

Transact-SQL
2

hope this helps

create data script

drop table #Data

create table #Data (time int , E_CC14 float)

insert into #Data select 1670990400, 5469.00223
insert into #Data select 1670992200, 5469.02791
insert into #Data select 1670994000, 5469.056295
insert into #Data select 1670995800, 5469.082706
insert into #Data select 1670997600, 5469.10558
insert into #Data select 1670999400, 5469.128534

; with cte as 
	( select 
			datename(month,(DATEADD(SS, time, '1970-01-01')))+'-'+cast(year(DATEADD(SS, time, '1970-01-01')) as varchar) as mnth_year
		,   lead(E_CC14) over( order by DATEADD(SS, time, '1970-01-01')) as Nxt
		,   E_CC14 
	  from 
		 #Data ) 
 select 
     mnth_year 
  ,  sum(Nxt-E_CC14) 
 from 
   cte 
 group by 
   mnth_year

image

2 Likes