Hello people of the internet.
Once again I seek your advice.
I have multiple meter readers collecting power consumption every 5 minutes and storing to MS SQL Database. I execute SQL query to get a days worth of data from all meter readers and check how much power it has used every five minutes and returning as ‘
Consumed
‘ result. This is SQL syntax that I use to get data:
SELECT Dep_Name, DoD, Convert(FLOAT,T_Energy_A) - lag(T_Energy_A) OVER(PARTITION BY Meter_ID ORDER BY DoD) AS Consumed FROM Meter_Data WHERE DoD>= DATEADD(HOUR,-1, GETDATE()) ORDER BY dateOfData DESC
The result I Get is this:
Dep_Name DoD Consumed Bath 6 2020-04-23 17:50:05.253 181 RPi Bath 5 2020-04-23 17:50:04.863 89 RPi Bath 4 2020-04-23 17:50:04.497 136 RPi Bath 3 2020-04-23 17:50:04.113 14 Solder Bath 6 2020-04-23 17:50:03.727 220 Solder Bath 5 2020-04-23 17:50:03.367 1261 Solder Bath 4 2020-04-23 17:50:02.997 409 Solder Bath 3 2020-04-23 17:50:02.590 0 Reflow 10 2020-04-23 17:50:02.287 440 Reflow 9 2020-04-23 17:50:02.197 244 Reflow 8 2020-04-23 17:50:02.020 361 Cam2 2020-04-23 17:50:01.787 180 Reflow 7 2020-04-23 17:50:01.457 366 Reflow 6 2020-04-23 17:50:01.030 506 Reflow 5 2020-04-23 17:50:00.837 453 Reflow 4 2020-04-23 17:50:00.770 280 Reflow 3 2020-04-23 17:50:00.697 243 Reflow 2 2020-04-23 17:50:00.630 389 Bath 6 2020-04-23 17:45:05.907 180 RPi Bath 5 2020-04-23 17:45:05.627 89 RPi Bath 4 2020-04-23 17:45:05.213 137 RPi Bath 3 2020-04-23 17:45:04.967 13 Solder Bath 6 2020-04-23 17:45:04.580 240 Solder Bath 5 2020-04-23 17:45:04.203 1254 Solder Bath 4 2020-04-23 17:45:03.967 413 Solder Bath 3 2020-04-23 17:45:03.807 0 Reflow 10 2020-04-23 17:45:03.560 493 Reflow 9 2020-04-23 17:45:03.163 245 Reflow 8 2020-04-23 17:45:02.807 360 Cam2 2020-04-23 17:45:02.433 179 Reflow 7 2020-04-23 17:45:02.063 379 Reflow 6 2020-04-23 17:45:01.473 505 Reflow 5 2020-04-23 17:45:01.383 450 Reflow 4 2020-04-23 17:45:01.157 279 Reflow 3 2020-04-23 17:45:00.780 242 Reflow 2 2020-04-23 17:45:00.657 387 Bath 6 2020-04-23 17:40:06.247 181 RPi Bath 5 2020-04-23 17:40:05.853 89 RPi Bath 4 2020-04-23 17:40:05.477 137 RPi Bath 3 2020-04-23 17:40:05.060 14 Solder Bath 6 2020-04-23 17:40:04.667 197 Solder Bath 5 2020-04-23 17:40:04.283 1212 Solder Bath 4 2020-04-23 17:40:03.887 385 Solder Bath 3 2020-04-23 17:40:03.547 0 Reflow 10 2020-04-23 17:40:03.280 495 Reflow 9 2020-04-23 17:40:03.063 244 Reflow 8 2020-04-23 17:40:02.600 361 Cam2 2020-04-23 17:40:02.303 178 Reflow 7 2020-04-23 17:40:01.943 353 Reflow 6 2020-04-23 17:40:01.500 508 Reflow 5 2020-04-23 17:40:01.280 449 Reflow 4 2020-04-23 17:40:01.040 280 Reflow 3 2020-04-23 17:40:00.687 241 Reflow 2 2020-04-23 17:40:00.620 387 Bath 6 2020-04-23 17:35:06.150 181 RPi Bath 5 2020-04-23 17:35:05.760 89 RPi Bath 4 2020-04-23 17:35:05.303 137 RPi Bath 3 2020-04-23 17:35:04.960 14 Solder Bath 6 2020-04-23 17:35:04.597 213 Solder Bath 5 2020-04-23 17:35:04.360 1178
Now I want to sum all ‘Consumed’ column data to as totalPower for each department, and group by Dep_Name in 1 day interval? Something Like this:
Dep_Name totalPower Bath 6 123 RPi Bath 5 321 RPi Bath 4 1566 RPi Bath 3 1134 Solder Bath 6 ... Solder Bath 5 ... Solder Bath 4 ... ... ...
Is it possible to do so? if Yes, how can I do it?
I am new to all this, so I don’t know.
Advertisement
Answer
If T_Energy_A is always increasing, you can just do:
select dep_name, max(t_energy_a) - min(t_energy_a) totalpower from meter_data where dod >= dateadd(hour,-1, getdate()) group by dep_name
If you want this on a day by day basis:
select cast(dod as date) dod_day, dep_name, max(t_energy_a) - min(t_energy_a) totalpower from meter_data group by cast(dod as date), dep_name order by dod_day, dep_name
Otherwise, you can just add one level of aggregation to your existing query:
select dep_name, cast(dod as date) dod_day, sum(consumed) totalpower
from (
select
dep_name,
dod,
t_energy_a - lag(t_energy_a) over(partition by meter_id order by dod) as consumed
from meter_data
) t
group by cast(dod as date), dep_name
order by dod_day, dep_name