Q:

creating a rank Index

What I am trying to achieve is to add a concatenated ranking index to my DB which can then be joined to another table.  Below is the current query to get the results.
select WorksOrderNumber, PartNumber, ScheduledFinishDate
 from vFM_WORKS_ORDER_HEADER 
where Complete = 'No' and ScheduledFinishDate is not null

I am wanting to create an index column that includes the part number + ' - ' + a rank number based on the scheduledFinishedDate for that Part No. only (if that makes sense)
ie
WorksOrderNumber    PartNumber  ScheduledFinishDate Index
28091              100767922    12/01/2018 00:00    100767922 - 2
28300              100767922    09/10/2017 00:00    100767922 - 1 
28092              100767922    26/01/2018 00:00    100767922 - 3 
29166              T1043501     04/12/2017 00:00    T1043501 - 2 
29167              T1043501     25/01/2018 00:00    T1043501 - 3 
29168              T1043501     19/10/2017 00:00    T1043501 -1

Regards 

A:

SELECT
    WorksOrderNumber,
    PartNumber,
    ScheduledFinishDate,
    PartNumber + ' - ' + CONVERT(VARCHAR(10), rn) AS [Index]
FROM
(
    SELECT WorksOrderNumber, PartNumber, ScheduledFinishDate,
        ROW_NUMBER() OVER (PARTITION BY PartNumber ORDER BY ScheduledFinishDate) rn
    FROM yourTable
)

