0
My problem is that I have a subquery that works perfectly, but for that I have to insert the id I want in the variables.
In the variable @mediaKM_mes I select to the table tblAbastecimento by the car id; in the @kmInicio variable I select to the table tblCarro by the car id; and in @kmFeitos do again select to table tblAbastecimento by car id;
In order for my query to be able to fill the table as I structured it I needed to pass the id_Carro that was inserted to a @id_carro variable to get the other variables to fetch the desired information.
What happens to me is that I have to put the number and only fill in either with id 15 or id 21
First I declared the variables this way:
@id_car which is where you wanted to store the value of the car id that appears on each line;
@mediaKM_mes is the average km the car makes per month and to tell which car would use the variable @id_car;
@kmInicio is the initial Km that the car had when it was registered;
@kmFides that is the sum of the km made by that same car to add with the @kmInicio and get the current km.
-- VARIÁVEIS
declare @id_carro as int;
declare @mediaKM_mes as decimal = ( select -- nv3
round( AVG( cast( kmMes as float)) ,0) as mediaKmMes from( select -- nv2
marca,
mes,
sum(kmfeitos) as kmMes,
sum(valorReal) as pagoMes,
round( cast( avg(media) as float),1)as mediaMes from( select -- nv1
*,
case when Litros = 0 or kmfeitos = 0 then null else
cast ((( Litros / kmfeitos) * 100) as decimal(18,1)) end as media from(
select c.marca as marca,
a.KmFeitos as kmfeitos,
MONTH(data) as mes,
valorPago - ValorDesconto as valorReal,
cast(valorPago / precoLitro as decimal(18,2)) as Litros from
tblAbastecimento a join tblCarro c on a.id_carro = c.id_carro where
c.id_carro = @id_carro) as nova1) as nova2 group by mes, marca) as nova3
group by marca);
declare @kmInicio as decimal;
set @kmInicio = (select km_inicio from tblCarro where id_carro = @id_carro)
declare @kmFeitos as decimal;
set @kmFeitos = (select sum(KmFeitos) from tblAbastecimento where id_carro =
@id_carro)
-- CÓDIGO
select -- nv3
id_Carro,
id_despesa,
marca,
descricao,
v,
km,
cast(v / y_Meses as decimal(18,2)) as valorMensal,
case when y_Meses > 12 then y_Meses - 12 end as mesPrevisto
from(
select -- nv2
*,
cast(Revisao / @mediaKM_mes as decimal(18,0)) as y_Meses
from( select -- nv1
*,
km - (@kmInicio + @kmFeitos) as Revisao from( select c.id_carro as
id_Carro,
d.id_despesa as id_despesa,
c.marca as marca,
d.descricao as descricao,
km as km,
valor as v from tblPrevistaCarro pc join tblCarro c
on pc.id_carro = c.id_carro join tblDespesa d on
pc.id_despesa = d.id_despesa where c.id_carro = @id_carro) as nv1) as
nv2)as nv3
You can detail the calculations you want to perform?
– bruno
@Diogosousa: To evaluate whether or not the use of cursor is necessary, let us know what is the purpose of the code you want to develop as well as the calculations you need to obtain.
– José Diz
@I edited my question in an attempt to be more enlightening. Imagine that I have three cars, each car had different kilometers, different averages, and what I wanted to do was in each line to know what car it was, put inside a variable so that the other variables could get the information needed to perform the calculations. Thank you
– Diogo Sousa
José Diz I have put more information, I hope not to be too confusing, but as I said, my logic is: imagining that I have 3 cars, each car with different kilometers, different averages etc, and what I wanted to do was in each line to know what car it is, put inside a variable so that the other variables could get the information needed to perform the calculations. Thank you
– Diogo Sousa
Diogo, why doesn’t my example cursor suit you? I copied from an advertising system of mine built in 2012 and that until today every day makes the closing box, therefore because I received vote against?
– Rafael Salomão
@Diogosousa: What I would like to know is: (a) what is the problem to be solved? (b) What result should the query return? // Other questions: (c) Which tables are involved? (d) Which columns of the tables contain useful information for the query to be mounted? // I could add this information in the text of the topic, but without making any mention of the code you posted?
– José Diz
@José Diz thanks for the help, I have already edited my problem... I have some difficulty in describing it, but I think it has improved a bit. Thank you so much for your help.
– Diogo Sousa
@Diogosousa: The questions I asked in the previous comment should be considered in a scenario where no single line of code has been written. That there is neither the code nor the variables. The beginning. That moment when there are only the tables. // The impression I have is that a new code will have to be written, from scratch.
– José Diz
@José Says what he thinks I should improve ?
– Diogo Sousa