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

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.

asked by Ric_hc 06.06.2017 в 23:54
source

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.

1

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