CODEWITHSUNDEEP
sqlsql-servert-sql
Sue
Sue

Reputation: 53

SQl: Update table with last row from a select

I´m struggling with this update...

I would like to update the column result from table #b with the column columna_1 resulting from the last row of the select below. The matching filed is id_car.

So if you run the select below, the last row of columna_1 is 42229.56230859 and the only record of #b would be:

id_car = 1
result = 42229.56230859

can anyone help please? thanks!!!

CREATE TABLE #b(
      id_car int,
      result money
      )
INSERT INTO #b VALUES (1,0)



CREATE TABLE #f(
      id_car int,
      fecha  datetime,
      sales money
      )
INSERT INTO #f VALUES (1,'2010-10-31',1.10912)
INSERT INTO #f VALUES (1,'2010-11-30',1.77227)
INSERT INTO #f VALUES (1,'2010-12-31',0.66944)
INSERT INTO #f VALUES (1,'2011-01-31',0.34591)
INSERT INTO #f VALUES (1,'2011-02-28',1.73468)
INSERT INTO #f VALUES (1,'2011-03-31',1.50102)
INSERT INTO #f VALUES (1,'2011-04-30',0.87270)
INSERT INTO #f VALUES (1,'2011-05-31',1.51555)

;with ctesource as 
(
    select
        id_car, fecha, sales, 
        sum( log( 1e0 + sales ) ) over ( partition by id_car order by fecha rows unbounded preceding) as LogAssetValue
    from
        #f
        WHERE id_car= 1 and fecha > '2010-10-30'
)
select convert(varchar, fecha, 104) AS fecha1, fecha, 
 CAST(SUM(exp(LogAssetValue)-1)*100  AS numeric(20, 8)) as columna_1
from ctesource 
GROUP BY fecha
order by fecha;

Upvotes: 0

Views: 62

Answers (1)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

SqlFiddleDemo

;with 
ctesource as (
    SELECT
        id_car, fecha, sales, 
        sum( log( 1e0 + sales ) ) over ( partition by id_car order by fecha rows unbounded preceding) as LogAssetValue,
        row_number() over (order by fecha) as rn
    FROM 
        f
    WHERE id_car= 1 
      and fecha > '2010-10-30'
)
SELECT convert(varchar, fecha, 104) AS fecha1,
       fecha,
       rn,
       CAST(SUM(exp(LogAssetValue)-1)*100  AS numeric(20, 8)) as columna_1
FROM ctesource
GROUP BY fecha, rn
HAVING rn = (SELECT max(rn) from ctesource)

Upvotes: 1

Related Questions