Sql to get the latest status - 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 to get the latest status

Author

Topic

Corkylee
Starting Member

1 Post

Posted - 2010-03-26 : 05:20:49

Hello,

I would really appreciate it if I could have some assistence on a problem that I am currently having and have been scratching my head on!

I have a table that contains specification requirements which is updated via SharePoint, each time a change to any of the fields is made a new row in the database is added and is given the next revision number.

What I am trying to do is calculate the date difference from the last modified date to today but only when the status column is changed.

A sample data set is:

Revision StatusID Modified Date
[7] [2] [2010-03-27 08:01:31.000]
[6] [2] [2010-03-26 08:36:31.000]
[5] [13] [2010-03-25 15:06:56.000]
[4] [13] [2010-03-25 15:06:29.000]
[3] [13] [2010-03-25 15:02:33.000]
[2] [13] [2010-03-25 14:58:28.000]
[1] [13] [2010-03-25 14:58:19.000]

So I am trying to select the modified date but only when the satus has changed therefore in the example above I would want to return the date from revision 6 as the last time the status had changed from 13 to 2.

Any ideas or assistance is much appreciated.

Regards

Lee

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-26 : 05:55:28

Try this.


declare @a table
(
 id int,
 val int,
 dt datetime
 
)
insert @a
select 7, 2, '2010-03-27 08:01:31.000' union all
select 6 ,2 ,'2010-03-26 08:36:31.000' union all
select 5, 13, '2010-03-25 15:06:56.000' union all
select 4, 13, '2010-03-25 15:06:29.000' union all
select 3, 13, '2010-03-25 15:02:33.000' union all
select 2, 13, '2010-03-25 14:58:28.000' union all
select 1, 13 ,'2010-03-25 14:58:19.000'

select * from
(
 select top 1 id,seq=ROW_NUMBER()over(PARTITION by val order by dt),val,dt from @a
)as t

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-27 : 13:50:35

quote:

Originally posted by haroon2k9

Try this.


declare @a table
(
 id int,
 val int,
 dt datetime
 
)
insert @a
select 7, 2, '2010-03-27 08:01:31.000' union all
select 6 ,2 ,'2010-03-26 08:36:31.000' union all
select 5, 13, '2010-03-25 15:06:56.000' union all
select 4, 13, '2010-03-25 15:06:29.000' union all
select 3, 13, '2010-03-25 15:02:33.000' union all
select 2, 13, '2010-03-25 14:58:28.000' union all
select 1, 13 ,'2010-03-25 14:58:19.000'

select * from
(
 select top 1 id,seq=ROW_NUMBER()over(PARTITION by val order by dt),val,dt from @a
)as t

Don't know why you are doing that.
First there is no need for a derived table and
second there is no need for row_number() in your solution
because a simple ORDER BY gives the same output.

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-27 : 13:56:34

Ok now I see - sorry.
But there should be a WHERE seq=1


select * from
(
 select top 1 id,seq=ROW_NUMBER()over(PARTITION by val order by dt),val,dt from @a
)as t
where seq=1

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-27 : 14:45:39

What if sample data is as below and OP wants details for each status change?


declare @a table
(
 id int,
 val int,
 dt datetime
 
)
insert @a
select 11, 13, '2010-03-31 08:01:31.000' union all
select 10 ,13 ,'2010-03-30 08:36:31.000' union all
select 9, 2, '2010-03-29 08:01:31.000' union all
select 8 ,2 ,'2010-03-28 08:36:31.000' union all
select 7, 2, '2010-03-27 08:01:31.000' union all
select 6 ,2 ,'2010-03-26 08:36:31.000' union all
select 5, 13, '2010-03-25 15:06:56.000' union all
select 4, 13, '2010-03-25 15:06:29.000' union all
select 3, 13, '2010-03-25 15:02:33.000' union all
select 2, 13, '2010-03-25 14:58:28.000' union all
select 1, 13 ,'2010-03-25 14:58:19.000'
--Haroon
select * from
(
 select top 1 id,seq=ROW_NUMBER()over(PARTITION by val order by dt),val,dt from @a
)as t
where seq=1


--Visakh
SELECT a.*
FROM @a a
CROSS APPLY (SELECT TOP 1 id,val
             FROM @a
             WHERE id< a.id
             ORDER BY id DESC
             )b
    WHERE b.val <> a.val       

output
-----------------------------
Haroon 
id	seq	val	dt
6	1	2	2010-03-26 08:36:31.000

Visakh
id	val	dt
10	13	2010-03-30 08:36:31.000
6	2	2010-03-26 08:36:31.000

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources