mostrar ultimo valor de cada columna

[hicolu]

hola amigos,

me pidieron que haga una consulta que me muestre el ultimo valor de cada columna

ANO ID MES ene feb mar abr may jun jul ago set oct
2010 5 1 125 125 125 125 125 125 125 125 125 125
2010 5 1 null null 100 100 100 100 100 null null null
2010 5 1 null null null null 300 350 400 100 220 333


2010 5 1 125 125 100 100 300 350 400 100 220 333


el resultado es la ultima linea sombreada, ojo que no es sumatoria

gracias

[flaviovich]

Creo que si agrupas por ANO ID MES y obtienes el MAX de cada columna puedes lograr ese resultado.

[hicolu]

gracias flaviovich por responderme, si lo que tu indicas ya lo habia hecho, pero no sale, resulta que yo quiero el ultimo valor de cada columna.

select ano,meta, mes, max(m1),max(m2),max(m3),max(m4),max(m5),max(m5),ma x(m7),max(m8),max(m9),max(m10),max(m11),max(m12)
from resumen_metas
group by ano,meta,mes

[flaviovich]

Si tienes SQL 2005, prueba esto:

Código SQL:

SELECT T1.*
FROM (
    SELECT *, ROW_NUMBER() OVER (partition BY ano,meta,mes ORDER BY ano,meta,mes) x
    FROM resumen_metas
) T1
INNER JOIN (
    SELECT ano,meta,mes,MAX(x) x
    FROM (
        SELECT *, ROW_NUMBER() OVER (partition BY ano,meta,mes ORDER BY ano,meta,mes) x
        FROM resumen_metas
    ) T
    GROUP BY ano,meta,mes
) T2 ON T1.ano = T2.ano AND T1.meta = T2.meta AND T1.mes = T2.mes AND T1.x = T2.x
 

[hicolu]

gracias Flaviovich, pero no funca, dime como leo el ultimo valor de una columna que no sea un valor null

ejemplo:

m1
-----------
123
456
234
10
null
null
null

como sacar el valor 10???

gracias totales

[flaviovich]

No funca porque no tienes SQL 2005 o te sale error?
Si sale error, cual?

[hicolu]

toy trabajando con el sqlserver 2008, si funciona, pero saca el ultimo registro de la tabla, lo que yo quiero es el ultimo valor de cada columna y que ese valor no sea nulo pero si >=0

saludos cordiales

[flaviovich]

Ahi como va?

Código SQL:

SELECT *, ROW_NUMBER() OVER (partition BY ano,meta,mes ORDER BY ano,meta,mes) x
INTO #rm
FROM resumen_metas
 
SELECT T0.ano,T0.meta,T0.mes,T1.m1,T2.m2
FROM #rm T0
INNER JOIN (
    SELECT ano,meta,mes,MAX(x) x FROM #rm T WHERE m1 >= 0 GROUP BY ano,meta,mes
) T1 ON T0.ano = T1.ano AND T0.meta = T1.meta AND T0.mes = T1.mes AND T0.x = T1.x
INNER JOIN (
    SELECT ano,meta,mes,MAX(x) x FROM #rm T WHERE m2 >= 0 GROUP BY ano,meta,mes
) T2 ON T0.ano = T2.ano AND T0.meta = T2.meta AND T0.mes = T2.mes AND T0.x = T2.x
 

[hicolu]

hola flaviovich,
veo que esta bien el querys, pero me vota este error

gracias por tu colaboracion

>[Error] Líneas de script: 49-56 --------------------
Invalid column name 'm1'.
Mensaje: 207, Nivel: 16, Estado: 1, Procedimiento: , Línea: 1 

>[Error] Líneas de script: 49-56 --------------------
Invalid column name 'm2'.
Mensaje: 207, Nivel: 16, Estado: 1, Procedimiento: , Línea: 1 

[Ejecutado: 05/11/10 06:34:24 PM COT] [Ejecución: 2ms]

[hicolu]

amix, hice algo asi: me sale de resultado la primera fila, mmmm donde puedo estar fallando?

SELECT T0.ano,T0.meta,T0.mes,T1.m1,T2.m2, T3.m3, T4.m4, T5.m5 FROM #rm T0
INNER JOIN (
SELECT ano,meta,mes,m1, max(x) x FROM #rm T1 WHERE m1 >= 0 GROUP BY ano,meta,mes,m1
) T1 ON T0.ano = T1.ano AND T0.meta = T1.meta AND T0.mes = T1.mes AND T0.x = T1.x
INNER JOIN (
SELECT ano,meta,mes,m2,max(x) x FROM #rm T2 WHERE m2 >= 0 GROUP BY ano,meta,mes,m2
) T2 ON T0.ano = T2.ano AND T0.meta = T2.meta AND T0.mes = T2.mes AND T0.x = T2.x
INNER JOIN (
SELECT ano,meta,mes,m3,max(x) x FROM #rm T3 WHERE m3 >= 0 GROUP BY ano,meta,mes,m3
) T3 ON T0.ano = T3.ano AND T0.meta = T3.meta AND T0.mes = T3.mes AND T0.x = T3.x
INNER JOIN (
SELECT ano,meta,mes,m4,max(x) x FROM #rm T4 WHERE m4 >= 0 GROUP BY ano,meta,mes,m4
) T4 ON T0.ano = T4.ano AND T0.meta = T4.meta AND T0.mes = T4.mes AND T0.x = T4.x
INNER JOIN (
SELECT ano,meta,mes,m5, max(x) x FROM #rm T5 WHERE m5 >= 0 GROUP BY ano,meta,mes,m5
) T5 ON T0.ano = T5.ano AND T0.meta = T5.meta AND T0.mes = T5.mes AND T0.x = T5.x

[flaviovich]

Cambia esta parte:

Código SQL:

SELECT T0.ano,T0.meta,T0.mes,T0.m1,T0.m2
 

Tienes que agregar un INNER JOIN por cada mes.

[hicolu]

hola flaviovich, hice lo que me indicaste y no sale ni un registro, pero si pruebo hasta el t2 si sale un registro

SELECT T0.ano,T0.meta,T0.mes, m1,T0.m2, T0.m3, T0.m4, T0.m5 FROM #rm T0
inner JOIN (
SELECT ano,meta,mes,max(x) x FROM #rm T1 WHERE m1 >= 0 GROUP BY ano,meta,mes
) T1 ON T0.ano = T1.ano AND T0.meta = T1.meta AND T0.mes = T1.mes AND T0.x = T1.x
inner JOIN (
SELECT ano,meta,mes,max(x) x FROM #rm T2 WHERE m2 >= 0 GROUP BY ano,meta,mes
) T2 ON T0.ano = T2.ano AND T0.meta = T2.meta AND T0.mes = T2.mes AND T0.x = T2.x
inner JOIN (
SELECT ano,meta,mes,max(x) x FROM #rm T3 WHERE m3 >= 0 GROUP BY ano,meta,mes
) T3 ON T0.ano = T3.ano AND T0.meta = T3.meta AND T0.mes = T3.mes AND T0.x = T3.x
INNER JOIN (
SELECT ano,meta,mes, max(x) x FROM #rm T4 WHERE m4 >= 0 GROUP BY ano,meta,mes
) T4 ON T0.ano = T4.ano AND T0.meta = T4.meta AND T0.mes = T4.mes AND T0.x = T4.x
INNER JOIN (
SELECT ano,meta,mes, max(x) x FROM #rm T5 WHERE m5 >= 0 GROUP BY ano,meta,mes
) T5 ON T0.ano = T5.ano AND T0.meta = T5.meta AND T0.mes = T5.mes AND T0.x = T5.x

[flaviovich]

Prueba asi:

Código SQL:

SELECT *, ROW_NUMBER() OVER (partition BY ano,meta,mes ORDER BY ano,meta,mes) x
INTO #rm
FROM resumen_metas
 
SELECT T0.ano,T0.meta,T0.mes,T1.m1,T2.m2
FROM #rm T0
LEFT JOIN (
    SELECT ano,meta,mes,MAX(x) x FROM (SELECT ano,meta,mes,x,m1 FROM #rm) T WHERE m1 >= 0 GROUP BY ano,meta,mes
) T1 ON T0.ano = T1.ano AND T0.meta = T1.meta AND T0.mes = T1.mes AND T0.x = T1.x
LEFT JOIN (
    SELECT ano,meta,mes,MAX(x) x FROM (SELECT ano,meta,mes,x,m2 FROM #rm) T WHERE m2 >= 0 GROUP BY ano,meta,mes
) T2 ON T0.ano = T2.ano AND T0.meta = T2.meta AND T0.mes = T2.mes AND T0.x = T2.x
 

[hicolu]

GRACIAS FLAVIOVICH,por darme la idea, al fin pude dar solucion y quedo asi

--creas el temporal
SELECT *, row_number() over (partition BY ano,meta,mes ORDER BY ano,meta,mes) x
INTO #rm
FROM resumen_metas
--seleccionar el ultimo dato de varias columnas
SELECT top 1 T0.ano,T0.meta,T0.mes,T1.m1,T2.m2, T3.m3, T4.m4, T5.m5, T6.m6, T7.m7, T8.m8, T9.m9,T10.m10, T11.m11,T12.m12
FROM #rm T0
LEFT JOIN (
SELECT top 1 ano,meta,mes,x,m1 from #rm T1 WHERE m1 >= 0 GROUP BY ano,meta,mes,x,m1 order by x desc
) T1 ON T0.ano = T1.ano AND T0.meta = T1.meta AND T0.mes = T1.mes
LEFT JOIN (
SELECT top 1 ano,meta,mes,x,m2 from #rm T2 WHERE m2 >= 0 GROUP BY ano,meta,mes,x,m2 order by x desc
) T2 ON T0.ano = T2.ano AND T0.meta = T2.meta AND T0.mes = T2.mes
LEFT JOIN (
SELECT top 1 ano,meta,mes,x,m3 from #rm T3 WHERE m3 >= 0 GROUP BY ano,meta,mes,x,m3 order by x desc
) T3 ON T0.ano = T3.ano AND T0.meta = T3.meta AND T0.mes = T3.mes
LEFT JOIN (
SELECT top 1 ano,meta,mes,x,m4 from #rm T4 WHERE m4 >= 0 GROUP BY ano,meta,mes,x,m4 order by x desc
) T4 ON T0.ano = T4.ano AND T0.meta = T4.meta AND T0.mes = T4.mes
LEFT JOIN (
SELECT top 1 ano,meta,mes,x,m5 from #rm T5 WHERE m5 >= 0 GROUP BY ano,meta,mes,x,m5 order by x DESC
) T5 ON T0.ano = T5.ano AND T0.meta = T5.meta AND T0.mes = T5.mes
LEFT JOIN (
SELECT top 1 ano,meta,mes,x,m6 from #rm T6 WHERE m6 >= 0 GROUP BY ano,meta,mes,x,m6 order by x DESC
) T6 ON T0.ano = T6.ano AND T0.meta = T6.meta AND T0.mes = T6.mes
LEFT JOIN (
SELECT top 1 ano,meta,mes,x,m7 from #rm T7 WHERE m7 >= 0 GROUP BY ano,meta,mes,x,m7 order by x DESC
) T7 ON T0.ano = T7.ano AND T0.meta = T7.meta AND T0.mes = T7.mes
LEFT JOIN (
SELECT top 1 ano,meta,mes,x,m8 from #rm T8 WHERE m8 >= 0 GROUP BY ano,meta,mes,x,m8 order by x DESC
) T8 ON T0.ano = T8.ano AND T0.meta = T8.meta AND T0.mes = T8.mes
LEFT JOIN (
SELECT top 1 ano,meta,mes,x,m9 from #rm T9 WHERE m9 >= 0 GROUP BY ano,meta,mes,x,m9 order by x DESC
) T9 ON T0.ano = T9.ano AND T0.meta = T9.meta AND T0.mes = T9.mes
LEFT JOIN (
SELECT top 1 ano,meta,mes,x,m10 from #rm T10 WHERE m10 >= 0 GROUP BY ano,meta,mes,x,m10 order by x DESC
) T10 ON T0.ano = T10.ano AND T0.meta = T10.meta AND T0.mes = T10.mes
LEFT JOIN (
SELECT top 1 ano,meta,mes,x,m11 from #rm T11 WHERE m11 >= 0 GROUP BY ano,meta,mes,x,m11 order by x DESC
) T11 ON T0.ano = T11.ano AND T0.meta = T11.meta AND T0.mes = T11.mes
LEFT JOIN (
SELECT top 1 ano,meta,mes,x,m12 from #rm T12 WHERE m12 >= 0 GROUP BY ano,meta,mes,x,m12 order by x DESC
) T12 ON T0.ano = T12.ano AND T0.meta = T12.meta AND T0.mes = T12.mes