Update Row with data from Previous Row

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)

Update Row with data from Previous Row

Author

Topic

stevieb
Starting Member

13 Posts

Posted - 2014-05-14 : 02:19:30

I have the below dataset I am wanting to update the 'oldstatus' with the value from the 'curstat' from the previous row.

probeid CurStat dbdate dbTime OldStatus
AD 9374 t 1/1/2014 12:00:28 AM 0
AD 9374 f 1/1/2014 12:02:27 AM 0
AD 9374 f 1/1/2014 12:04:27 AM 0
AD 9374 f 1/1/2014 12:06:27 AM 0
AD 9374 f 1/1/2014 12:08:28 AM 0
AD 9374 f 1/1/2014 12:10:28 AM 0
AD 9374 f 1/1/2014 12:12:27 AM 0
AD 9374 f 1/1/2014 12:14:27 AM 0
AD 9374 f 1/1/2014 12:21:06 AM 0
AD 9374 f 1/1/2014 12:26:06 AM 0

So the Result will look like this

probeid CurStat dbdate dbTime OldStatus
AD 9374 t 1/1/2014 12:00:28 AM NULL
AD 9374 f 1/1/2014 12:02:27 AM t
AD 9374 f 1/1/2014 12:04:27 AM f
AD 9374 f 1/1/2014 12:06:27 AM f
AD 9374 f 1/1/2014 12:08:28 AM f
AD 9374 f 1/1/2014 12:10:28 AM f
AD 9374 f 1/1/2014 12:12:27 AM f
AD 9374 f 1/1/2014 12:14:27 AM f
AD 9374 f 1/1/2014 12:21:06 AM f
AD 9374 f 1/1/2014 12:26:06 AM f

I have tried using CTE and rownum-1 but I cannot get the results I am looking for.

Can anyone help me solve this problem or offer any advice.

I have recently made the switch from Access to SQL due to the database size, so I am on a learning curve

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-05-14 : 02:30:51

[code]
;with aCTE
as
(select 'AD 9374' probeid,'t'CurStat, CAST('1/1/2014' as DATE) dbdate,CAST('12:00:28 AM' as Time) dbTime, 0 OldStatus union all
select 'AD 9374', 'f', CAST('1/1/2014' as DATE), CAST('12:02:27 AM' as Time), 0 union all
select 'AD 9374', 'f', CAST('1/1/2014' as DATE), CAsT('12:04:27 AM' as Time), 0 union all
select 'AD 9374', 'f', CAST('1/1/2014' as DATE), CAST('12:06:27 AM' as Time), 0 union all
select 'AD 9374', 'f', CAST('1/1/2014' as DATE), CAST('12:08:28 AM' as Time), 0 union all
select 'AD 9374', 'f', CAST('1/1/2014' as DATE), CAST('12:10:28 AM' as Time), 0 union all
select 'AD 9374', 'f', CAST('1/1/2014' as DATE), CAST('12:12:27 AM' as Time), 0 union all
select 'AD 9374', 'f', CAST('1/1/2014' as DATE), CAST('12:14:27 AM' as Time), 0 union all
select 'AD 9374', 'f', CAST('1/1/2014' as DATE), CAST('12:21:06 AM' as Time), 0 union all
select 'AD 9374', 'f', CAST('1/1/2014' as DATE), CAST('12:26:06 AM' as Time), 0 )

select *
from
aCTE A
outer apply
(
select top 1 CurStat
from
aCTE B
WHERE
A.probeid=B.probeid
AND A.dbdate>=B.dbdate
AND A.dbTime>B.dbTime
order by B.dbdate desc,B.dbTime desc)B
[/code]

output
[code]
probeid CurStat dbdate dbTime OldStatus CurStat
AD 9374 t 2014-01-01 00:00:28.0000000 0 NULL
AD 9374 f 2014-01-01 00:02:27.0000000 0 t
AD 9374 f 2014-01-01 00:04:27.0000000 0 f
AD 9374 f 2014-01-01 00:06:27.0000000 0 f
AD 9374 f 2014-01-01 00:08:28.0000000 0 f
AD 9374 f 2014-01-01 00:10:28.0000000 0 f
AD 9374 f 2014-01-01 00:12:27.0000000 0 f
AD 9374 f 2014-01-01 00:14:27.0000000 0 f
AD 9374 f 2014-01-01 00:21:06.0000000 0 f
AD 9374 f 2014-01-01 00:26:06.0000000 0 f
[/code]

sabinWeb MCP

stevieb
Starting Member

13 Posts

Posted - 2014-05-14 : 03:09:00

Thanks for the Code.

I can see what happening now. So if I wanted to use this to perform a table Update just change SELECT to UPDATE?

Or even a SELECT * INTO newtable

Thanks

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-05-14 : 03:18:29

Yes , you can

To be sure, add a

begin tran

(first line) and after the update add


select* from yourTable
rollback tran

and after you verify the output , change the rollback to commit

sabinWeb MCP

stevieb
Starting Member

13 Posts

Posted - 2014-05-14 : 03:40:40

Many Thanks for the help. All working now.. now onto the next challenge.

The main database contains over 250 Million Records and growing at 50 million a month.

Thankfull the above will only have to be carried out once a month

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources