Say for example that you have a table with transactions and you would like to index the records from 1…n on each supplier id and potentially at the same time do this in transaction date sequence.

The ROW_NUMBER() ranking function with provide the current row number and the RANK() function will provide the number of rows before the current row. Therefore to calculate the rank within the sub-group we use:-

INDEX = ROW_NUMBER – RANKK + 1

Here is an example using the transaction data specified and the sample SQL.

declare @trx table (
[tran_date] date,
[supplier_id] int,
[supplier_name] varchar(32),
[amount] decimal(32, 2)
);

insert into @trx (tran_date, supplier_id, supplier_name, amount) values
(‘2016-05-20’, 1, ‘Katie Bear Ltd’, 1000.00),
(‘2016-05-21’, 1, ‘Katie Bear Ltd’, 500.00),
(‘2016-05-19’, 1, ‘Katie Bear Ltd’, 730.00),
(‘2016-05-22’, 2, ‘Shadow Cat Ltd’, 30.00),
(‘2016-05-23’, 2, ‘Shadow Cat Ltd’, 7780.20),
(‘2016-05-24’, 2, ‘Shadow Cat Ltd’, 8700.00),
(‘2016-05-31’, 3, ‘Master Builder Ltd’, 3000.00),
(‘2016-06-01’, 3, ‘Master Builder Ltd’, 1033.80);

select tran_date,
supplier_id,
supplier_name,
amount,
ROW_NUMBER() OVER(ORDER BY supplier_id,tran_date) – RANK() OVER (ORDER BY supplier_id) + 1 [index]
from @trx;

Advertisements