Calculate Daily/Monthly Energy consumption in SQL Server

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
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

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
,   E_CC14
from
#Data )
select
mnth_year
,  sum(Nxt-E_CC14)
from
cte
group by
mnth_year
``````

2 Likes

GREAT.. thank's very much

1 Like