Finding in which category the days falls in the Given Category of table in SQL

I have a table with column as and its value as given

ProvisionType------From Days------ToDays

• goodLoan --------- 1------------------90
• Substand ------------91--------------180
• doubful--------------181--------------365
• bad----------------365----------------365+

IN the following case i may have a variable which may be different and i am managing that from code....But suppose if i want to find where exactly 200 falls between From days and To Days so i get the Correct Provision Type. So please anybody c开发者_Go百科ould help me on this

If you're using SQL Server then this should work. Not sure if MySql has BETWEEN...

SELECT ProvisionType 
FROM myTable
WHERE (myVariable BETWEEN FromDays AND ToDays)

It could also be written without the BETWEEN...

SELECT ProvisionType
FROM myTable
WHERE myVariable >= FromDays 
AND myVariable <= ToDays

For this to work you should replace 365+ on the last provision with an actual number, otherwise the ToDays field will need to be a varchar field rather than numeric. Also, if a value of 365 is provided, would it be expected to fall into the last category or the one before it? The other categories have a distinct cut-off point, but the last ones overlap.

Does this work (in SQL Server):

select top 1 ProvisionType
from table
where FromDays < 200
order by FromDays desc

For MySQL:

select ProvisionType
from table
where FromDays < 200
order by FromDays desc
limit 1

I'm not sure why you need both FromDays and ToDays. That seems like you're asking for overlaps and gaps. If you just have FromDays, you guarantee no gaps or overlaps and you don't have to do clever things to manage the highest limit, ie. 365+ days.