Select range age with unit of measure in same field

I am working on a table where the age of a person is in a string field where it is in the following format: (amount UnitOfMeasurement)

1 year old = 1 y
11 months old = 11 m
5 Days old = 5 d

I am tr开发者_高级运维ying to do a search between a range of age. Is is possible to this via a SQL query where it would order the days (d) first, then months (m), and years (y)?

The database is on SQL Server 2008, but the query will probably be done on Access as it is used for a report's record source.

The first thing I'd do in your situation is try to clean up the messy age field, and standardise it. A quick start might be to create a query where you separate the age value and the age unit, by using expressions such as:

age_unit: Right([age], 1)

and

age_value: Val([age])

If you then sort by age_unit and age_value, you will get all ages sorted correctly (under the assumption that an age in days is always less than an age in months, which in turn is always less than an age in years). Note that you must sort by unit first, then value.

If you want to return ages between a certain minimum and maximum, it's not a problem if you're sticking to a single unit, such as all ages between 5 years and 15 years. Just enter "y" as a criteria under the "age_unit" field (assuming you're using the visual query builder here) and enter "Between 5 and 15" under the "age_value" field.

If you're mixing units ("all ages between 6 months and 2 years") it gets a little more complicated. In this case you'd need to do the following:

On one criteria row you'd enter the following values for each field:

age_unit: "m"

age_value: >=6

And then on the next criteria row:

age_unit: "y"

age_value: <=2

This will return all ages having unit "m" and a value >= 6 OR having unit "y" with a value <=2.

Another somewhat simpler solution would be to convert all ages to a standard unit such as years, by doing some simple calculations, e.g. divide "d" unit values by 365.25, and divide "m" unit values by 12. Then create a new field in your table for the new standardised age data.

Your best bet would be to create a new colum with a real DATETIME value in it. You could then write code, such as a CASE statement, to help convert the string into a DATETIME. Once completed, your calculations will become much simpler.

1.This field doesn't has atomic values. This means that your table is not in 1NF.

You should split Age field into 2 columns with atomic values: IntervalType(CHAR(1)... CHECK(IntervalType IN ('d','m','y')) and IntervalValue (INT; 1,2, etc). So, instead of Table(...,Age) you can use Table(...,IntervalType,IntervalValue) and

SELECT *
    ,CONVERT(VARCHAR(10),IntervalValue)
    +' '+CASE IntervalType WHEN 'd' THEN 'day' WHEN 'm' THEN 'month' WHEN 'y' THEN 'year' END
    +CASE WHEN IntervalValue > 1 THEN 's' ELSE '' END
    +' old = '
    +CONVERT(VARCHAR(10),IntervalValue)
    +' '+IntervalType
FROM table

2.How do you sort these two values: 30 d and 1 month ? One month can have from 28 to 31 days.

3.SQL Server solution:

DECLARE @TestData TABLE
(
     Age VARCHAR(25) NOT NULL
    ,IntervalValue AS CONVERT(INT,LEFT(Age,CHARINDEX(' ',Age))) PERSISTED
    ,IntervalType AS RIGHT(Age,1) PERSISTED
);
INSERT  @TestData
VALUES
 ('1 year old = 1 y')
,('2 years old = 2 y') 
,('11 months old = 11 m')
,('30 Days old = 30 d')
,('5 Days old = 5 d');

SELECT  *
FROM    @TestData a
ORDER BY a.IntervalType, a.IntervalValue;