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 2000 Forums
 SQL Server Development (2000)
 duplicate timestamps		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

NickC
Yak Posting Veteran

United Kingdom
68 Posts
Posted - 08/09/2012 :  08:00:45  Show Profile  Reply with Quote
Hi

I'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	Status
0	2007-01-29 13:55:08.297	351346	2007-01-29 13:55:06.140	Changed Owner to : 2nd Checker
9962	2007-02-05 11:57:27.640	351346	2007-01-29 13:55:08.297	Changed Owner to : 2nd Checker
9962	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

India
47040 Posts
Posted - 08/09/2012 :  10:18:00  Show Profile  Reply with Quote
dont you have a primary key ie an id column may be?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

NickC
Yak Posting Veteran

United Kingdom
68 Posts
Posted - 08/09/2012 :  10:32:42  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47040 Posts
Posted - 08/09/2012 :  11:30:32  Show Profile  Reply with Quote
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 MVP
http://visakhm.blogspot.com/
Go to Top of Page

Jared Drake
Starting Member

4 Posts
Posted - 09/01/2012 :  22:12:13  Show Profile  Reply with Quote
What? Where is your distinct primary key?!

I am a writer for the http://www.afterhoursprogramming.com/index.php?article=167 (SQL section) on afterhoursprogramming.com
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47040 Posts
Posted - 09/02/2012 :  12:28:27  Show Profile  Reply with Quote
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 MVP
http://visakhm.blogspot.com/
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.2 seconds. Powered By: Snitz Forums 2000