SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Update Row with data from Previous Row
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

stevieb
Starting Member

13 Posts

Posted - 05/14/2014 :  02:19:30  Show Profile  Reply with Quote


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
Constraint Violating Yak Guru

Romania
428 Posts

Posted - 05/14/2014 :  02:30:51  Show Profile  Reply with Quote

;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


output

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



sabinWeb MCP
Go to Top of Page

stevieb
Starting Member

13 Posts

Posted - 05/14/2014 :  03:09:00  Show Profile  Reply with Quote
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

Edited by - stevieb on 05/14/2014 03:15:33
Go to Top of Page

stepson
Constraint Violating Yak Guru

Romania
428 Posts

Posted - 05/14/2014 :  03:18:29  Show Profile  Reply with Quote
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
Go to Top of Page

stevieb
Starting Member

13 Posts

Posted - 05/14/2014 :  03:40:40  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000