Sorry, but your explanation is quite confusing.
Could you provide DDL plus INSERTs and for each row: why or why not it's in the result.
When you actually have values like '8Month' and this is supposed to be less than '1Year' you should strongly consider changing your data model. i wouldn't want to write a parsing routine to define less/greater.
dieter, there is no values like '8Month', here i have changed the datas and column names but the scenario is same. for pil1 i want the matched value, if not then i want the immediate lesser value as like this for pil2 i need the matched value, if not then want the immediate greater value. after i found all this, i wanna to subtract both the dates.
Ok, what is the actual data type?
Is it possible to compare '1Year' to '8Month'?
Is the query you posted anything you actually use?
Why is it so complicated to post some actual DDL and data?
data type is VARCHAR(10).
i know, we cannot able to compare 1Year and 8Month but as per its order using CASE function we can define some interger values for that, then we can compare right.
yes am going to use this query.
am working for banking project, i dont have an access for this site.