Dumbrica Dumbrica - 5 months ago 9
SQL Question

Need Help Optimizing SQL query

First I would like to say that I'm fairly new to SQL so this might seem like a stupid question.
So in this code I receive a Date as a parameter, add 61 minutes to it and check the values in between.
Then I Sum the values from each column and store it in another table.
The Code is Working just fine, what I would like to know is if there is a better way to do it and how to do it.(Without using so many lines or repetitive code)

Thanks in advance.

alter procedure Contagem
@date datetime

as
begin
declare
@Sala1 float,
@Sala2 float,
@Sala3 float,
...
@Sala26 float,
@Sala27 float,
@Sala28 float,
@dateplus datetime

set @Teste = 1
set @dateplus = (select DATEADD(MINUTE,61,@date))

set @Sala1 = (select sum(Sala_1_Energia) from Energia_15min where Time_Stamp between @date and @dateplus)
set @Sala2 = (select sum(Sala_2_Energia) from Energia_15min where Time_Stamp between @date and @dateplus)
set @Sala3 = (select sum(Sala_3_Energia) from Energia_15min where Time_Stamp between @date and @dateplus)
...
set @Sala26 = (select sum(Sala_26_Energia) from Energia_15min where Time_Stamp between @date and @dateplus)
set @Sala27 = (select sum(Sala_27_Energia) from Energia_15min where Time_Stamp between @date and @dateplus)
set @Sala28 = (select sum(Sala_28_Energia) from Energia_15min where Time_Stamp between @date and @dateplus)

Insert into Custos_hora values (@date,@Sala1,@Sala2,@Sala3,@Sala4,@Sala5,@Sala6,@Sala7,@Sala8,@Sala9,@Sala10,@Sala11,@Sala12,@Sala13,@Sala14,@Sala15,@Sala16,@Sala17,@Sala18,@Sala19,@Sala20,@Sala21,@Sala22,@Sala23,@Sala24,@Sala25,@Sala26,@Sala27,@Sala28)
end

Answer

You could just hit that table the once rather than 28 times as you are currently doing.

INSERT INTO Custos_hora
SELECT
SUM(Sala_1_Energia)
,SUM(Sala_2_Energia)
,SUM(Sala_3_Energia)
,SUM(Sala_4_Energia)

FROM Energia_15min

WHERE Time_Stamp between @date and @dateplus

It's considered best practice to declare the fields that you're inserting into like this;

INSERT INTO Custos_hora (Field1, Field2, Field3, Field4)
SELECT
SUM(Sala_1_Energia)
,SUM(Sala_2_Energia)
,SUM(Sala_3_Energia)
,SUM(Sala_4_Energia)

FROM Energia_15min

WHERE Time_Stamp between @date and @dateplus

Also, what's the variable @Teste for? it doesn't appear to be being used anywhere. And you don't seem to be declaring @date either.