Obtain Maximum and minimum of an SQL Calculated query (2 columns). Server 2008

Question

Obtain Maximum and minimum of an SQL Calculated query (2 columns). Server 2008

Navigation

#1 by (1 votes)
#2 by (1 votes)

5

This query is the final part of a store procedure that manages to show me the results in this way:

final_comparativo as (
                    select s.CeEmplazamiento, avg(s.Totales) TotalGeneral , s2.TotGral, @Mes as Mes
                    from tmpSemanas s
                        cross join ( select avg(Totales) TotGral from tmpSemanas s2 where Totales >0) s2
                    group by s.CeEmplazamiento , s2.TotGral
            union all
                    select ss.CeEmplazamiento, avg(ss.Totales) TotalGeneral , ss2.TotGral, @Mes_comparar as Mes
                    from tmpSemanas_comparar ss
                        cross join ( select avg(Totales) TotGral from tmpSemanas_comparar ss2 where Totales >0) ss2
                    group by ss.CeEmplazamiento , ss2.TotGral
        ), maximo_minimo as (
            select CeEmplazamiento,
                max(case when Mes  = @Mes then TotalGeneral else 0 end) as Mes_actual,
                max(case when Mes  = @Mes_comparar then TotalGeneral else 0 end) as Mes_comparar
            from final_comparativo
            where TotalGeneral <> 0
            group by CeEmplazamiento    
            )

            select * from maximo_minimo
            drop table #tmpDatos
end

This is the result table currently:

CeEmplazamiento Mes_actual  Mes_comparar
Celaya          76.500000   75.600000
Coecillo        79.000000   79.800000
Irapuato        77.500000   75.400000
León Sur        85.750000   87.600000
Oriente León    86.250000   85.200000

The store procedure asks you for 2 arguments corresponding to current month (month of origin) month_to compare (against which month you want to compare the current month) therefore it is already a final calculation. But my problem lies in the following, I would like this table to take the maximum and minimum of the current month and in the same way for the column "Month_comparar", I hope to have explained. In a more summary way, I would like you to stay this way.

CeEmplazamiento      Mes_Actual  Mes_comparar
Oriente León         86.250000   NULL
Celaya               76.500000   NULL
León Sur                 NULL    87.600000
Irapuato                 NULL    75.400000

For what you may notice, it is not the same amount for all the CeSite, those that are not in the current Month, are not shown but are taken into account for a graph that is being done. It is a crossed table, I am trying it with a case, but it does not allow me to show nulls. I hope you can help me. Thanks.

sql sql-server-2008

asked by Ric_hc 06.06.2017 в 23:54

source

2 answers

Add php and SQL server MySQL - Detail with a query

score 1 · Answer 1

OK, this is in Oracle, not in SQL server, pq is not running my local version I do not know why, so after I update it, but a possible solution would be:

select ceemplazamiento , max(t_mes_actual), max(t_mes_comparar)
from(

select *
from(
select ceemplazamiento,
max(mes_actual) t_mes_actual,
null t_mes_comparar
from
temp
group by ceemplazamiento
order by max(mes_actual) desc)
where rownum < 2

union

select *
from(
select ceemplazamiento,
min(mes_actual) t_mes_actual,
null t_mes_comparar
from
temp
group by ceemplazamiento
order by min(mes_actual) asc)
where rownum < 2

union

select *
from(
select ceemplazamiento,
null t_mes_actual,
max(mes_comparar) t_mes_comparar
from
temp
group by ceemplazamiento
order by max(mes_comparar) desc)
where rownum < 2

union

select *
from(
select ceemplazamiento,
null t_mes_actual,
min(mes_comparar) t_mes_comparar
from
temp
group by ceemplazamiento
order by min(mes_comparar) asc)
where rownum < 2
)
group by ceemplazamiento

in sqlserver instead of using rownum you use top, the rest should be the same. see if with this you already find a way to solve the problem.

score 1 · Answer 2

Another solution with Outer Joins could be this: (Note: If there is more than one location with values equal to the maximum / minimum, it should also work)

    select #tmpDatos.CeEmplazamiento, mmActual.MinMax as Mes_Actual,                 
    mmCompara.MinMax as Mes_Compara
    from
    #tmpDatos 
    left outer join
    (select  min(mes_actual) as MinMax from #tmpDatos
     union
     select max(mes_actual) from #tmpDatos
    ) mmActual
    on #tmpDatos.Mes_Actual = mmActual.MinMax
    left outer join
    (select  min(Mes_comparar) as MinMax from #tmpDatos
     union
     select max(Mes_comparar) from #tmpDatos) mmCompara
    on #tmpDatos.Mes_Comparar= mmCompara.MinMax
    order by ceEmplazamiento