Find Rank using Firstsortedvalue

[gireesh1216]

Hi Hello,

I have a data like this:

ID	ID NAME	LAYER	PERIOD	R STATUS
1216	A	APP	01-01-2017-30-12-2017	COMP
1216	B	APP	01-01-2017-30-12-2017	PEND
1216	C	DB	01-01-2017-30-12-2017	SCOPE
1216	D	OS	01-01-2017-30-12-2017	NOTAVBLE
1217	A	APP	01-01-2017-30-12-2017	NOTAVBLE
1217	C	APP	01-01-2017-30-12-2017	PEND
1217	D	DB	01-01-2017-30-12-2017	COMP

If one ID has more then one R STATUS ,then we need to calculate rank based on below table.

R STATUS, RANK:

COMP,1

PEND,2

SCOPE,3

NOTAVBLE,4

RESULTS:

ID	ID NAME	LAYER	PERIOD	R STATUS
1216	A	APP	01-01-2017-30-12-2017	COMP
1216	C	DB	01-01-2017-30-12-2017	SCOPE
1216	D	OS	01-01-2017-30-12-2017	NOTAVBLE
1217	C	APP	01-01-2017-30-12-2017	PEND
1217	D	DB	01-01-2017-30-12-2017	COMP

Thanks,

Gireesh