SQL : how to choose the previous value in column when current value is zero?

1

Hallo,
i have a table with three columns : person_id , FS and Value .
person_id FS value

50566 1 20
50566 2 40
50566 3 0
50566 4 0

I want that when the 'value' = 0 then it should be set to the last value in that column (i.e. 40) . So, in this case the 'value' in row 3 and 4 should be set to 40 , how to do that in sql ?

Thank you so much in advance.

Alex

2

Hi.
The question is not exactly clear and it has not even been said what the primary key of the table is.
Assuming that the personal_id and FS columns uniquely identify a tuple, I assume this solution:

declare @Table as table
(
	person_id int
	, FS int
	, value int
)
insert into @Table (person_id, FS, value) values 
	(50566,1,20),(50566,2,40),(50566,3,0),(50566,4,0)
	,(50600,1,30),(50600,2,40),(50600,3,0),(50600,4,50)
	,(50700,1,0),(50700,2,0),(50700,3,10),(50700,4,20)

select
	t.person_id
	, t.FS
	, (case when t.value = 0 then s.value else t.value end) as new_value
FROM
	@Table t
LEFT JOIN @Table s ON
	s.person_id = t.person_id 
	AND s.FS = (select max(s.FS) from @Table s where s.person_id = t.person_id and s.value <> 0 and s.FS < t.FS)
3 

DROP TABLE IF EXISTS #temp;
CREATE TABLE #temp ( person_id int NOT NULL, FS int NOT NULL, value int NULL );
INSERT INTO #temp VALUES
    (50566, 1, 20), (50566, 2, 40), (50566, 3, 0), (50566, 4, 0),
    (50777, 1, 50), (50777, 2, 30), (50777, 3, 0), (50777, 4, 0), 
        (50777, 5, 10), (50777, 6, 0);

SELECT t1.person_id, t1.FS, 
    CASE WHEN t1.value <> 0 THEN t1.value ELSE t2.value END AS value
FROM #temp t1
OUTER APPLY (
    SELECT TOP (1) *
    FROM #temp t2
    WHERE t1.value = 0 AND
        t2.value <> 0 AND
        t2.person_id = t1.person_id AND
        t2.FS < t1.FS
    ORDER BY t2.FS DESC
) AS t2
4

Thank you all . I found the solution. Best wishes.

5

For everyone's benefit, would you be so kind to tell us what this solution is?