How to join 3 queries in sql?

2

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.

    
asked by Molitaa 20.07.2017 в 17:02
source

3 answers

2

First of all, I'm committing you to do this:

and (MONTH(SdFecha)= month(@FechaDesde)-1  and YEAR(SdFecha)=YEAR(@FechaDesde))

to filter records from a month before the requested period is not going to work, imagine that @FechaDesde is 12/1/2017 then month(@FechaDesde)-1 = 0 and obviously there is no month 0 , you should also adjust the year in that case, because what you really want is to see the 12/2016. One way to solve it is the following:

MONTH(SdFecha) = MONTH(DATEADD(MONTH, -1, @FechaDesde) and YEAR(SdFecha) = YEAR(DATEADD(MONTH, -1, @FechaDesde)) 

That is, I move the date one month back and now I do look at the month and year.

Of course Ariel's solution of using variables is valid, but to offer you an alternative, we are going to use subqueries and joins like this:

select  
    R1.NumOperador, 
    R1.SdFecha,
    R1.Rendimiento            as 'RenMesActual', 
    ISNULL(R2.RenUltimoMes,0) as 'RenUltimoMes',
    ISNULL(R3.RentAnual,0)    as 'RentAnual'
    from trfRendimientos R1
    -- máximo rendimiento del período solicitado por operador
    INNER JOIN ( select NumOperador, MAX(SdFecha) AS 'MaxSdFechaMes'
        from trfRendimientos 
        where CONVERT(DATETIME,CONVERT(VARCHAR,SdFecha, 111)) BETWEEN @FechaDesde AND @FechaHasta)
        group by NumOperador
    ) A
       ON A.NumOperador = R1.NumOperador
       AND A.MaxSdFechaMes = R1.SdFecha
    -- mes anterior
    left join (select NumOperador,
            sum(Rendimiento)/count(Rendimiento) as RenUltimoMes
            from trfRendimientos 
            where   MONTH(SdFecha) = MONTH(DATEADD(MONTH, -1, @FechaDesde) and YEAR(SdFecha) = YEAR(DATEADD(MONTH, -1, @FechaDesde))  
            group by NumOperador
        ) R2
        ON R2.NumOperador = R1.NumOperador
    -- año actual
    left join (select NumOperador,
            sum(Rendimiento)/count(Rendimiento) as RentAnual
            from trfRendimientos 
            where   (month(SdFecha)>=month(1) and YEAR(SdFecha)=YEAR(@FechaDesde))
            group by NumOperador
        ) R3
        ON R3.NumOperador = R1.NumOperador
    where   (R1.NumOperador = @NumOperador OR @NumOperador = 0)  

Keep in mind, that I have neither the model nor any example defined, so there may be some error in the query, but I hope you understand the idea. Eventually it might be possible to solve everything with a single SELECT but I think it would be much more complex to understand.

EDITED

After the last comment of Molita, I redo the query modifying the WHERE by and CONVERT(DATETIME, CONVERT(VARCHAR,R1.SdFecha, 111)) = @FechaDesde , what is sought is that the main query returns the values of the consulted day regardless of the time.

    
answered by 20.07.2017 / 18:36
source
2

You can set the variables before and then add them to the query

declare @RenUltimoMes REAL,@RentAnual REAL



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


select Top 1 NumOperador, Rendimiento, SdFecha,@RenUltimoMes as RenUltimoMes,@RentAnual as  RenAnual
from trfRendimientos 
where (NumOperador = @NumOperador OR @NumOperador = 0)  
and (MONTH(SdFecha)= month(@FechaDesde)  and YEAR(SdFecha)=YEAR(@FechaDesde))
    
answered by 20.07.2017 в 17:08
0

I do not know if it works in SQLServer, but in Mysql I have tested it and you can do a select inside another, like this:

select resultado1, (select resultado 2
                    from tabla 2
                    where...), (select resultado 3
                                from tabla 3
                                where .....)
from tabla
where....;

I tried to do it with your code, but it looked bad ... I hope you could see the idea

    
answered by 20.07.2017 в 17:54