SQL question - 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

SQL Server 2008 Forums

Transact-SQL (2008)

SQL question

Author

Topic

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2012-01-06 : 05:03:13

Hi.

I have the following table

accnum, bal, lim, mthyr
001, 432.00, 150, 201004
001, 342.00, null, 201005

I want to get the previous months lim to fill in the null from the latest month.

Any help would be great

thanks

Arumugam
Starting Member

11 Posts

Posted - 2012-01-06 : 06:16:17

Try this

Create Table #Table
(
accnum int,
bal int,
lim int,
mthyr int
)
Insert Into #Table values(001, 432.00, 150, 201004)
Insert Into #Table values(001, 342.00, null, 201005)

update #Table set lim = (select top 1 lim from #Table as T2 where T2.mthyr < T.mthyr and T2.lim is not null Order By T2.mthyr) from #Table as T where T.lim is null

Select * from #Table

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2012-01-06 : 14:05:01

[CODE]select
a.accnum,
a.bal,
coalesce(a.lim, b.lim) lim,
a.mthyr
from
MyTable a
left outer join
MyTable b
on DateDiff(month, b.mthyr + '01', a.mthyr + '01') = 1[/CODE]

=================================================
Men shout to avoid listening to one another. -Miguel de Unamuno

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-06 : 14:24:18

[code]
update t
set lim =t1.lim
from #Table t
cross apply
(select top 1 lim from #Table as T2 where T2.mthyr < t.mthyr and T2.lim is not null Order By T2.mthyr desc)t1
where t.lim is null
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources