0

We are trying to rank Invoice column and Only want first shipment Invoices among parts of Invoices.

That is in that, column we have multiple Invoices. We would like to select that group of invoices among rank column which consist of Earliest Timestamp.

Nature of TimeStamp Column:

The SQL Server timestamp data type has nothing to do with times or dates it is just a binary number to indicate a sequence.The timestamp column is recorded and incremented each time a record is added.

We tried following query :

select * 
from 
    (select 
         myrank = rank() over (partition by a.SalesOrder order by c.Invoice), 
         c.Invoice, c.TimeStamp
     from 
         OrderDetail a 
     left join 
         OrderMaster b on a.SalesOrder = b.SalesOrder  
     left join 
         InvoiceDetail c on a.SalesOrder = c.SalesOrder 
                         and a.SalesOrderLine = c.SalesOrderLine 
     where  
         a.SalesOrder = '218438'  
         and c.LineType = '1' 
         and b.OrderStatus = '9' ) z

Sample Data:

myrank | SalesOrder | SalesOrderLine |Invoice | TimeStamp
1   218438  8   207964  0x0000000048CD8CD4
2   218438  1   207965  0x0000000048CD8CD5
2   218438  2   207965  0x0000000048CD8CD6
2   218438  3   207965  0x0000000048CD8CD7
2   218438  4   207965  0x0000000048CD8CD8
2   218438  5   207965  0x0000000048CD8CD9
2   218438  6   207965  0x0000000048CD8CDA
2   218438  7   207965  0x0000000048CD8CDB

Expected Data:

Record Should be rank as First shipment invoice and First Added Record on the basis of timestamp What should be the output If i select 2 as my rank

myrank | SalesOrder | SalesOrderLine |Invoice | TimeStamp
    1   218438  8   207964  0x0000000048CD8CD4

Thanks in advance !

NEO
  • 389
  • 8
  • 31
  • 1
    How about the table definition as a starting point? As it is we are guessing as to what you are trying to do here. http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ – Sean Lange Jun 21 '16 at 19:02
  • Actually the `timestamp` data type is also altered each time you update a record. Its meant as a solution for concurrency by versioning the record. When 2 processes try to alter the same record one of them will lose out because their `timestamp` no longer matches the one on the record. – Igor Jun 21 '16 at 19:39
  • `The timestamp column is recorded and incremented each time a record is added` - if this is something you are looking for so you can identify the first record added in a sequence you should add an `identity` column and not a `timestamp` column. This would be sql server incremented for each new record and then the value would not be changed. – Igor Jun 21 '16 at 19:41
  • We would like to rank among Invoice column value . among this ranking want to check which record is added first that would be our First shipment for invoices. – NEO Jun 21 '16 at 20:24
  • I have edited the Question. In that , Included Sample data,Expected Data . – NEO Jun 21 '16 at 20:25
  • You should forget trying to use the timestamp column in order to determine which row is newest. That isn't how that datatype works at all. Timestamp works on a SINGLE row, not across multiple rows. – Sean Lange Jun 21 '16 at 20:40
  • Ok. So How do i identify among invoices which invoice used at first shipment. – NEO Jun 21 '16 at 20:42
  • I would recommend switching your timestamp to a datetime instead. That will allow to order rows by the value in the column across rows. – Sean Lange Jun 21 '16 at 20:42
  • Given the data you posted as a sample you have nothing you can use to determine order. You need something on each row that you can use to order. – Sean Lange Jun 21 '16 at 20:44
  • my datetime columns are same for each row that is InvoiceDate =2016-06-08 00:00:00.000 – NEO Jun 21 '16 at 20:49
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/115242/discussion-between-vickps-and-sean-lange). – NEO Jun 21 '16 at 20:50

1 Answers1

0

Use ROW_NUMBER.

I don't know your exact table structure but it would be similar to this:

SELECT *
FROM (
    SELECT c.Invoice, ROW_NUMBER() OVER (PARTITION BY c.Invoice ORDER BY c.TimeStamp ASC) n
    FROM InvoiceDetails c
    ) i
WHERE i.n = 1
Sherman
  • 853
  • 5
  • 16
  • when i am using your query . getting this as result http://paste.ofcode.org/cKzb6WH3wB2AnMbDjVCYyv – NEO Jun 21 '16 at 20:10
  • It also tells me for two multiple invoices i am could not able to rank for first Created INVOICE. – NEO Jun 21 '16 at 20:11
  • My Intention is with the help of TimeStamp column . can i get it Earliest Invoice among group of Invoices – NEO Jun 21 '16 at 20:12
  • http://paste.ofcode.org/d9XdFcTtRM5HfkSMArjvEQ I MIXED both of us query.but still not logically convinved . – NEO Jun 21 '16 at 20:56