How can I show the following 3 queries in a single selection?
select Top 1 NumOperador, Rendimiento, SdFecha
from trfRendimientos
where (NumOperador = @NumOperador OR @NumOperador = 0)
and (MONTH(SdFecha)= month(@FechaDesde) and YEAR(SdFecha)=YEAR(@FechaDesde))
select
RenUltimoMes=sum(Rendimiento)/count(Rendimiento)
from trfRendimientos
where (NumOperador = @NumOperador OR @NumOperador = 0)
and (MONTH(SdFecha)= month(@FechaDesde)-1 and YEAR(SdFecha)=YEAR(@FechaDesde))
select
RentAnual=sum(Rendimiento)/count(Rendimiento)
from trfRendimientos
where (NumOperador = @NumOperador OR @NumOperador = 0)
and (month(SdFecha)>=month(1) and YEAR(SdFecha)=YEAR(@FechaDesde))
I get it in the following way, I want the RenUltimoMes to appear next to SdFecha and Ren Annual also in the first consultation.
Updated
In the main query is to show the last performance entered in the table today 20 July 2017 showing the operator and the SdFecha
In the Second is to show the Performance per month, but it must be one month before the one entered in @FechaDesde.
In the Third is to Show the Annual Performance according to the year entered in @FechaDesde.
Taking into account the @numoperador already a numOperator in speculation or sending the number 0 to return me all who meet the dates entered.
I have placed the first Query that @Particio has indicated to me, the result is as follows:
The result is correct with NumOperator, Performance, SdFecha and Annual Performance, the Last Month Performance is the one that returns it to 0 and there are records so it should not be in 0.
I assemble the records that have the table of yields and which are those that should appear in the last performance of the month.
Even if I send in @ numOperator = 0 it returns me only 1 record that would be the last one and it does not return the others of the table, this would be for the Top 1 that I have in the first query there I have to change is correct?
Modified
With this new modification I am already returning the different registers with the last Registered Execution and the Annual Performance, only the performance per month is the one that does not give it to me, it gives me a 0, in the attached image you can see that there are 2 records for the month of June that are the ones that should give me the performance in the last month but it gives me 0.