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)
 select only changes compared to previous row
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

3DBug
Starting Member

2 Posts

Posted - 12/06/2012 :  04:58:41  Show Profile  Reply with Quote
Hi there :)
Perhaps someone can help me with this...
SQL Server 2008
Have Table Data1..

Date_TimeA, Value1, Value2,....Value225..
2012-10-04 14:34:16.000 33,66,....1815
2012-10-04 14:35:17.000 11,23,....1815
2012-10-04 14:32:16.000 62,64,....1815
2012-10-04 14:31:19.000 33,66,....1815
2012-10-04 14:45:16.000 11,23,....9999
2012-10-04 14:52:15.000 62,64,....1815
2012-10-04 12:35:16.000 11,23,....9999

many entrys every minute one (about 2 years of data), but not allways in order of Date_Time..
with this i make querys between Date_Times ordered by Date_Time..
(using this for Charts-trending)

now i would like to see only the rows where the Value225 changed from the previous row
2012-10-04 12:35:16.000 11,23,....9999
2012-10-04 14:31:19.000 33,66,....1815 ****
2012-10-04 14:32:16.000 62,64,....1815
2012-10-04 14:34:16.000 33,66,....1815
2012-10-04 14:35:17.000 11,23,....1815
2012-10-04 14:45:16.000 11,23,....9999 ****
2012-10-04 14:52:15.000 62,64,....1815

the output doesn't need to include Value1,Value2 etc.. just need the Date_Time and ValueV225
think.. it could work somwehow to build a select with function ROW_NUMBER..
but problem ist between my ears ( how to.. do it in a fast way )

Perhaps someone can help me .. thx

please excuse my rusty english ;)

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 12/06/2012 :  05:25:14  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
;with cte as
(
select Date_TimeA, Value225, seq = row_number() over (order by Date_TimeA) from Data1
)
select c1.Date_TimeA, c1.Value225, case when c1.Value225 <> c2.Value225 or c2.Value225 is null then '****' else '' end)
from cte c1 left join cte c2
on c1.seq = c2.seq+1

if you only want the changed rows
select c1.Date_TimeA, c1.Value225
from cte c1 left join cte c2
on c1.seq = c2.seq+1
where c1.Value225 <> c2.Value225 or c2.Value225 is null


==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Edited by - nigelrivett on 12/06/2012 05:28:41
Go to Top of Page

3DBug
Starting Member

2 Posts

Posted - 12/06/2012 :  06:14:15  Show Profile  Reply with Quote
thx .. that's what i was looking for :)
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