RE: T-SQL help – SQLServerCentral

Lynn Pettis

SSC Guru

Points: 442467

May 6, 2013 at 3:05 pm

#1612782

Maybe this:

declare @StartPeriod varchar(10),

@EndPeriod varchar(10);

select @StartPeriod = '4Months', @EndPeriod = '12Years';

select

cast(substring(@StartPeriod, 1, patindex('%[^0-9]%',@StartPeriod) - 1) as int) * case when patindex('%Months%',@StartPeriod) > 0 then 1 when patindex('%Years%',@StartPeriod) > 0 then 12 else 0 end,

cast(substring(@EndPeriod, 1, patindex('%[^0-9]%',@EndPeriod) - 1) as int) * case when patindex('%Months%',@EndPeriod) > 0 then 1 when patindex('%Years%',@EndPeriod) > 0 then 12 else 0 end

declare @people as table (

person varchar(20),

dob date

)

insert into @people(person,dob)

values ('Frank','3/20/1990'),

('Joey','12/31/2000'),

('Sue','2/6/2012'),

('Mary','4/1/2013'),

('Bil','5/3/2012'),

('Bob','1/1/2013'),

('Will','10/1/2012')

SELECT person,

dob,

case when datediff(month, dob, getdate()) - case when dateadd(month, datediff(month, dob, getdate()), dob) > getdate() then 1 else 0 end < 12

then cast(datediff(month, dob, getdate()) - case when dateadd(month, datediff(month, dob, getdate()), dob) > getdate() then 1 else 0 end as varchar) + ' Months'

else cast((datediff(month, dob, getdate()) - case when dateadd(month, datediff(month, dob, getdate()), dob) > getdate() then 1 else 0 end) / 12 as varchar) + ' Years'

end as age

FROM @people

WHERE case when datediff(month, dob, getdate()) - case when dateadd(month, datediff(month, dob, getdate()), dob) > getdate() then 1 else 0 end < 12

then datediff(month, dob, getdate()) - case when dateadd(month, datediff(month, dob, getdate()), dob) > getdate() then 1 else 0 end

else ((datediff(month, dob, getdate()) - case when dateadd(month, datediff(month, dob, getdate()), dob) > getdate() then 1 else 0 end) / 12) * 12

end between cast(substring(@StartPeriod, 1, patindex('%[^0-9]%',@StartPeriod) - 1) as int) * case when patindex('%Months%',@StartPeriod) > 0 then 1 when patindex('%Years%',@StartPeriod) > 0 then 12 else 0 end

and cast(substring(@EndPeriod, 1, patindex('%[^0-9]%',@EndPeriod) - 1) as int) * case when patindex('%Months%',@EndPeriod) > 0 then 1 when patindex('%Years%',@EndPeriod) > 0 then 12 else 0 end

select @StartPeriod = '3Years', @EndPeriod = '50Years';