CODEWITHSUNDEEP
sql-servert-sqlsql-server-2012
Chris J
Chris J

Reputation: 936

TSQL Ranking using timestamp and datetime

I'm trying to rank a series of transactions, however my source data does not capture the time of a transaction which can happen multiple times a day, the only other field I can use is a timestamp field - will this be ranked correctly?

Here's the code

SELECT [LT].[StockCode]
     , [LT].[Warehouse]
     , [LT].[Lot]
     , [LT].[Bin]
     , [LT].[TrnDate]
     , [LT].[TrnQuantity]
     , [LT].[TimeStamp] 
     , LotRanking = Rank() Over (Partition By [LT].[Warehouse],[LT].[StockCode],[LT].[Lot] Order By [LT].[TrnDate] Desc, [LT].[TimeStamp] Desc)
     From [LotTransactions] [LT]

Results being returned are as below

StockCode   |Warehouse  |Lot    |Bin    |TrnDate                    |TrnQuantity    |TimeStamp          |LotRanking
2090        |CB         |3036   |CB     |2016-02-16 00:00:00.000    |2.000000       |0x0000000000500AB9 |1
2090        |CB         |3036   |CB     |2016-02-16 00:00:00.000    |2.000000       |0x0000000000500A4E |2

Upvotes: 3

Views: 588

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269483

First, you should be using rowversion rather than timestamp for keeping track of row versioning information. I believe timestamp is deprecated. At the very least, the documentation explicitly suggests [rowversion][1].

Second, I would strongly recommend that you add an identity column to the table. This will provide the information that you really need -- as well as a nice unique key for the table.

In general, a timestamp or rowversion is used just to determine whether or not a row has changed -- not to determine the ordering. But, based on this description, what you are doing might be correct:

Each database has a counter that is incremented for each insert or update operation that is performed on a table that contains a timestamp column within the database. This counter is the database timestamp. This tracks a relative time within a database, not an actual time that can be associated with a clock. A table can have only one timestamp column. Every time that a row with a timestamp column is modified or inserted, the incremented database timestamp value is inserted in the timestamp column.

I would caution that this might not be safe. Instead, it gives a reason why such an approach might make sense. Let me repeat the recommendation: add an identity column, so you are correctly adding this information, at least for the future.

Upvotes: 3

gofr1
gofr1

Reputation: 15977

You can use something like this to get datetime of transaction:

SELECT LEFT(CONVERT(nvarchar(50),[LT].[TrnDate],121),10) + RIGHT(CONVERT(nvarchar(50),CAST([LT].[TimeStamp] as datetime),121),13)

For first string it will be:

2016-02-16 04:51:25.417

And use this for ranking.

Upvotes: 2

Related Questions