Use Last Value - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Use Last Value

Author

Topic

matta0990
Starting Member

44 Posts

Posted - 2011-12-01 : 03:58:05

Hi,

I've got a list fo data

VALUE:

1
2
3
4
5
6
7
0
0
0
8
9
0
0
0
10

etc...

I'm trying to get my sql to use the last known value if value = 0

so the list would like liek so

VALUE

1
2
3
4
5
6
7
7
7
7
8
9
9
9
9
10

any ideas?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-12-01 : 04:04:51

is there another column in the table that can determine the sequence / ordering of the records ?

KH
[spoiler]Time is always against us[/spoiler]

matta0990
Starting Member

44 Posts

Posted - 2011-12-01 : 04:09:21

yes sorry its

Period_Id

day1
day2
day3
day4
day5
day6
etc....

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-12-01 : 04:15:01

[code]
select Period_Id, t.Value, Value = coalesce(nullif(t.Value, 0), p.Value)
from yourtable t
cross apply
(
select top 1 x.Value
from yourtable x
where x.Value <> 0
and x.Period_Id <= t.Period_Id
order by x.Period_Id desc
) p
order by Period_Id
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources