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.