I think I understand what you're after. You want the record(s) previous to the current record?
If your query actually returns something, you could do this:
select (case when Deposit_Type =1 then 'RD' else 'FD' end) as Deposit_Type,
ID,
Scheme_Name,
(case when Mode_Operation=1 then 'General' else 'SC' end)as Mode_Operation,
Period_Type,
Period_Value,
Type_Interest,
Interest_Rate,
LockIn_Period,
PNL_Interest,
Is_Active from [Scheme]
WHERE GETDATE() NOT BETWEEN Valid_From_Date AND Valid_To_Date
And if you want just the most current previous record:
select TOP 1
(case when Deposit_Type =1 then 'RD' else 'FD' end) as Deposit_Type,
ID,
Scheme_Name,
(case when Mode_Operation=1 then 'General' else 'SC' end)as Mode_Operation,
Period_Type,
Period_Value,
Type_Interest,
Interest_Rate,
LockIn_Period,
PNL_Interest,
Is_Active from [Scheme]
WHERE GETDATE() NOT BETWEEN Valid_From_Date AND Valid_To_Date
ORDER BY Valid_From_Date DESC