Hi,
I have this select that works ok although the performance is not good.
Basically the select looks for precio=0 and updates this row with the price from the previous date so long as this is not cero either. All this grouped by codigo.
Can anyone help?
thanks
CREATE TABLE #f(
codigo int,
fecha datetime,
precio money
)
INSERT INTO #f VALUES (1,'2010-10-01',1.10912)
INSERT INTO #f VALUES (1,'2010-10-02',0)
INSERT INTO #f VALUES (1,'2010-10-03',0)
INSERT INTO #f VALUES (1,'2011-10-04',0.34591)
INSERT INTO #f VALUES (31,'2011-02-08',1.73468)
INSERT INTO #f VALUES (31,'2011-02-09',0)
INSERT INTO #f VALUES (31,'2011-02-11',0.87270)
INSERT INTO #f VALUES (31,'2011-02-10',1.51555)
;WITH CTE_cero
AS
( select * from #f where precio=0
)
UPDATE cte_cero
SET precio = (select top 1 #f.precio
from #f
where CTE_cero.codigo=#f.codigo and CTE_cero.fecha >#f.fecha order by codigo, fecha
)