You can also create a Data Driven Subscription for the same .

and pass a Visiting date as a parameter.

Give a Visiting date =getdate()

Thanks

Sonal Totla

HI Try this

with CTEOrder as

(

select Rank,

Case Right(Rank,2)

when 'th' then

convert(int,Replace(Rank,'th',0))

when 'st' then

convert(int,Replace(Rank,'st',0))

when 'nd' then

convert(int,Replace(Rank,'nd',0))

when 'rd' then

convert(int,Replace(Rank,'rd',0))

else ''

end

A from OrderBy

)

select Rank from CTEOrder order...