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.
| Author |
Topic |
|
NickC
Yak Posting Veteran
68 Posts |
 Posted - 2012-08-09 : 08:00:45
|
HiI'm querying a table and looking at the difference between timestamps.The current table has Document_ID, Status, [TimeStamp]I'm running a query which adds the next time stamp for that document id so im doing a self join sub query to get that value which works great.SELECT Min(a2.[TimeStamp]) FROM ai_audit_trail a2 a2.doc_id = a.doc_id AND a.[TimeStamp] < a2.[TimeStamp]The problem is i'm getting instances where the timestamp is exactly the same so instead of looking at following row it slooking at the following row where its different :(DateBetweenStatus NextTimeStamp Doc_ID TimeStamp Status0 2007-01-29 13:55:08.297 351346 2007-01-29 13:55:06.140 Changed Owner to : 2nd Checker9962 2007-02-05 11:57:27.640 351346 2007-01-29 13:55:08.297 Changed Owner to : 2nd Checker9962 2007-02-05 11:57:27.640 351346 2007-01-29 13:55:08.297 Changed Status to : INTECH ENTRY COMPLETE |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-08-09 : 10:18:00
|
| dont you have a primary key ie an id column may be?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
NickC
Yak Posting Veteran
68 Posts |
Posted - 2012-08-09 : 10:32:42
|
| No its an awful table nothing but a doc_id which is duplicated for every status change, and a time stamp which can be the same as wel (to the micro second)I did think using identity(1,1) and then using that instead of timestamp but seems a messy way of doing it :/ uless theres a way to ode to look at next row rather than doing it where less than timestamp :/ i dunno |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-08-09 : 11:30:32
|
quote:
Originally posted by NickC
No its an awful table nothing but a doc_id which is duplicated for every status change, and a time stamp which can be the same as wel (to the micro second)I did think using identity(1,1) and then using that instead of timestamp but seems a messy way of doing it :/ uless theres a way to ode to look at next row rather than doing it where less than timestamp :/ i dunno
ok..if thats case identity is only way to solve it in sql 2000. if it was 2005 or above you could use window functions like ROW_NUMBER------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
Jared Drake
Starting Member
4 Posts |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-09-02 : 12:28:27
|
| i think its kind of an audit table with timestamp field. but for getting previous next row an identity column will certainly help rather than >,< comparison over timestamp column.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
duplicate timestamps