Calculate Daily/Monthly Energy consumption in SQL Server

Transact-SQL
1

I have a SQL Server table Energy_CC with two columns: time [int] (Epoch time) and E_CC14 [float]. Every 30 minutes, the total amount of my energy (kWh) is appended to the data table - something like this:

time E_CC14
1670990400 5469.00223
1670992200 5469.02791
1670994000 5469.056295
1670995800 5469.082706
1670997600 5469.10558
1670999400 5469.128534

I tried this SQL statement:

SELECT 
    MONTH(DATEADD(SS, i.time, '1970-01-01')),
    i.E_CC14 AS mwh, 
    iprev.E_CC14 AS prevmwh,
    (i.E_CC14 - iprev.E_CC14) AS diff
FROM   
    Energy_CC i 
LEFT OUTER JOIN
    Energy_CC iprev ON MONTH(iprev.time) = MONTH(i.time) - MONTH(DATEADD(month, -1, i.time)) 
                    AND DAY(iprev.time) = 1
WHERE
    DAY(i.time) = 1
GROUP BY
    MONTH(i.time), i.E_CC14, iprev.E_CC14;

I expect the result for monthly like this :

time E_CC14
DECEMBER-22 10223
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
3

GREAT.. thank's very much

1 Like