May 3, 2013 at 4:06 pm
For the below SQL code, I need the output as:
person dob Age
Frank3/20/199023 Years
Joey12/31/200012 Years
Sue2/6/20121 Years
Mary4/1/20131 Months
Bil5/3/20121 Years
Bob1/1/20134 Months
Will10/1/20127 Months
declare @people as table (
person varchar(20),
dob date
)
declare @SDate as date = '5/3/2013';
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,
DATEDIFF(YEAR, dob, @SDate) -
CASE WHEN((MONTH(dob)*100 + DAY(dob)) > (MONTH(@SDate)*100 + DAY(@SDate))) THEN 1 ELSE 0 END age
FROM @people
May 3, 2013 at 6:52 pm
The natural thing to do is calculate the age in years in a cte, then calculate teh age in months for cases where the age in years is 0. That's pretty straight forward:
with years(person,dob,age_y) as
(select person,
dob,
DATEDIFF(YEAR, dob, @SDate) -
CASE WHEN((MONTH(dob)*100 + DAY(dob)) > (MONTH(@SDate)*100 + DAY(@SDate)))
THEN 1
ELSE 0
END as age_y
FROM @people)
select person, dob,
case when age_y > 0
then LTRIM(STR(age_y)) + ' Yrs'
else LTRIM(STR(DATEDIFF(MM,dob,@SDate) -
case when DATEPART(DD,dob)>DATEPART(DD,@SDATE) then 1 else 0
end)) + ' Mths'
end as age
from years
Tom
May 4, 2013 at 10:15 am
Thank you!!
But I also need to get the persons WHERE age BETWEEN '4 Mths' AND '12 Yrs'.
May 6, 2013 at 10:47 am
But I also need to get the persons [WHERE age BETWEEN '4 Mths' AND '12 Yrs'/code]
May 6, 2013 at 10:53 am
etirem (5/4/2013)
Thank you!!But I also need to get the persons
WHERE age BETWEEN '4 Mths' AND '12 Yrs'.
Is this an additional requirement to only display individuals that are between 4 months old and 12 years old? I didn't see that in your original post.
May 6, 2013 at 11:29 am
Yes..This is an additional requirement. I need to retrieve the Persons based on the AGE range BETWEEN months and years.
May 6, 2013 at 11:49 am
This:
declare @people as table (
person varchar(20),
dob date
)
declare @SDate as date = '5/3/2013';
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 4 and 144
May 6, 2013 at 12:12 pm
This also should work..
declare @people as table (
person varchar(20),
dob date
)
declare @SDate date
set @SDate= '5/3/2013'
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,@SDate)/12>0 then convert(varchar,DATEDIFF(YEAR,dob,@SDate))+ ' Years' else convert(varchar,DATEDIFF(MONTH,dob,@SDate)) + ' Months' end as age
FROM @people
where DATEDIFF(MONTH,dob,@SDate)>=4 and DATEDIFF(YEAR,dob,@SDate)<=12
May 6, 2013 at 1:41 pm
But I have different scenarios to get the persons retrieved based on the parameters for BEGIN AGE and END AGE in WHERE Clause:
Like :
SELECT person, dob,
case when DATEDIFF(MONTH,dob,@SDate)/12>0 then convert(varchar,DATEDIFF(YEAR,dob,@SDate))+ ' Years' else convert(varchar,DATEDIFF(MONTH,dob,@SDate)) + ' Months' end as age
FROM @people
where --DATEDIFF(MONTH,dob,@SDate)>=1 and DATEDIFF(YEAR,dob,@SDate)<=7
--Age BETWEEN @BEGINAge AND @ENDAge
Query1: Age BETWEEN 1 Months AND 1 Years
Query2: Age BETWEEN 4 Months AND 13 Years
Query3: Age BETWEEN 1 Years AND 23 Years
May 6, 2013 at 2:07 pm
etirem (5/6/2013)
But I have different scenarios to get the persons retrieved based on the parameters for BEGIN AGE and END AGE in WHERE Clause:Like :
SELECT person, dob,case when DATEDIFF(MONTH,dob,@SDate)/12>0 then convert(varchar,DATEDIFF(YEAR,dob,@SDate))+ ' Years' else convert(varchar,DATEDIFF(MONTH,dob,@SDate)) + ' Months' end as age
FROM @people
where --DATEDIFF(MONTH,dob,@SDate)>=1 and DATEDIFF(YEAR,dob,@SDate)<=7
--Age BETWEEN @BEGINAge AND @ENDAge
Query1: Age BETWEEN 1 Months AND 1 Years
Query2: Age BETWEEN 4 Months AND 13 Years
Query3: Age BETWEEN 1 Years AND 23 Years
Is this the last change in your requirements?
Also, looking above it looks like this even changes your last change for 4 months to 12 years.
May 6, 2013 at 2:09 pm
Oh, and how is this information passed to query?
May 6, 2013 at 2:22 pm
Sorry, this is the last requirement.
The values are passed to the query from a drop-down list from SSRS report.
Both drop down lists are sames values:
1Months
2Months
.
.
12Months
1Years
2Years
3Years
.
100Years
BeginAge: 2Months EndAge: 48Years
May 6, 2013 at 3:05 pm
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';
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
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
May 6, 2013 at 7:59 pm
Try this (function posted below) the script:
DECLARE @people AS TABLE
(person VARCHAR(20)
,dob DATE)
DECLARE @SDate AS DATE = '5/3/2013' ;
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
*
FROM
@People p
CROSS APPLY
dbo.itvfCalcAge(p.dob,@SDate)
CREATE FUNCTION dbo.itvfCalcAge
(
@StartDate DATETIME
,@EndDate DATETIME
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
(
-- CREATE DATE DEFAULTS
WITH cteDefaults
(
CurrDate,
CurrYrStartDate,
FirstDOMCurrMonth,
LastDOMCurrMonth,
FirstDOMStartDate,
LastDOMEndDate,
FirstDOYCurrYr,
LastDOYCurrYr
)
AS
(
SELECT
GETDATE() AS CurrDate
,(CASE
WHEN DATEDIFF(DAY,DATEADD(YEAR,(YEAR(GETDATE())-YEAR(@StartDate)),@StartDate),GETDATE()) > 0
THEN DATEADD(YEAR,(YEAR(GETDATE())-YEAR(@StartDate)),@StartDate)
ELSE
DATEADD(YEAR,-1,DATEADD(YEAR,(YEAR(GETDATE())-YEAR(@StartDate)),@StartDate))
END)
AS CurrYrStartDate
,DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0) AS FirstDOMCurrMonth
,DATEADD(MILLISECOND,-3,DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE())+1,0)) AS LastDOMCurrMonth
,DATEADD(MONTH,DATEDIFF(MONTH,0,@StartDate),0) AS FirstDOMStartDate
,DATEADD(MILLISECOND,-3,DATEADD(MONTH,DATEDIFF(MONTH,0,@EndDate)+1,0)) AS LastDOMEndDate
,DATEADD(YEAR,DATEDIFF(YEAR,0,GETDATE()),0) AS FirstDOYCurrYr
,DATEADD(MILLISECOND,-3,DATEADD(YEAR,DATEDIFF(YEAR,0,GETDATE())+1,0)) AS LastDOYCurrYr
),
-- DETERMINE THE MOST RECENT ANNIVERSARY DATE AND NEXT ANNIVERSARY DATE.
-- THIS WILL BE USED FOR DETERMINING THE DECIMAL PART OF AGE.
cteAnniversaries (LastAnniversary,NextAnniversary)
AS
(
SELECT
CurrYrStartDate AS LastAnniversary
,DATEADD(YEAR,1,CurrYrStartDate) AS NextAnniversary
FROM
cteDefaults
),
-- DETERMINE THE NUMBER OF DAYS BETWEEN THE ANNIVERSARY DATES.
-- THIS WILL TAKE INTO ACCOUNT LEAP YEARS
cteDaysPerYear(NumDaysCurrYear,TotalYears,TotalMonths,TotalDays,RecentAnniversary)
AS
(
SELECT
DATEDIFF(DAY, a.LastAnniversary, a.NextAnniversary)
AS NumDaysCurrYear
,DATEDIFF(YEAR,@StartDate,@EndDate)
AS TotalYears
,DATEDIFF(MONTH,a.LastAnniversary,@EndDate)
AS TotalMonths
,(DATEDIFF(DAY,DATEADD(DAY,-(DAY(@EndDate)-1),@EndDate),@EndDate))+1
AS TotalDays
,a.LastAnniversary
FROM
cteAnniversaries a
)
-- NOW GET THE DECIMAL PART AND ADD TOGETHER
SELECT
(CASE
WHEN
dpy.TotalYears
+ CAST(DATEDIFF(DAY,dpy.RecentAnniversary,@EndDate) AS NUMERIC(6,2))
/ ISNULL(CAST(dpy.NumDaysCurrYear AS NUMERIC(6,2)),1) > 0
THEN
dpy.TotalYears
+ CAST(ROUND(CAST(DATEDIFF(DAY,dpy.RecentAnniversary,@EndDate) AS NUMERIC(6,2))
/ ISNULL(CAST(dpy.NumDaysCurrYear AS NUMERIC(6,2)),1),4) AS NUMERIC(6,4))
ELSE 0
END) AS TotalAge
,(CASE
WHEN dpy.TotalYears < 0 THEN 0
ELSE dpy.TotalYears
END) AS Years
,(CASE
WHEN dpy.TotalMonths < 0 THEN 0
ELSE dpy.TotalMonths
END) AS Months
,(CASE
WHEN dpy.TotalDays < 0 THEN 0
ELSE dpy.TotalDays
END) AS Days
FROM
cteDaysPerYear AS dpy
)
GO
Output
<?xml version="1.0" ?>
<RESULTS1>
<RECORD>
<person>Frank</person>
<dob>1990-03-20</dob>
<TotalAge>23.1205</TotalAge>
<Years>23</Years>
<Months>2</Months>
<Days>3</Days>
</RECORD>
<RECORD>
<person>Joey</person>
<dob>2000-12-31</dob>
<TotalAge>13.3370</TotalAge>
<Years>13</Years>
<Months>5</Months>
<Days>3</Days>
</RECORD>
<RECORD>
<person>Sue</person>
<dob>2012-02-06</dob>
<TotalAge>1.2356</TotalAge>
<Years>1</Years>
<Months>3</Months>
<Days>3</Days>
</RECORD>
<RECORD>
<person>Mary</person>
<dob>2013-04-01</dob>
<TotalAge>0.0877</TotalAge>
<Years>0</Years>
<Months>1</Months>
<Days>3</Days>
</RECORD>
<RECORD>
<person>Bil</person>
<dob>2012-05-03</dob>
<TotalAge>1.0000</TotalAge>
<Years>1</Years>
<Months>0</Months>
<Days>3</Days>
</RECORD>
<RECORD>
<person>Bob</person>
<dob>2013-01-01</dob>
<TotalAge>0.3342</TotalAge>
<Years>0</Years>
<Months>4</Months>
<Days>3</Days>
</RECORD>
<RECORD>
<person>Will</person>
<dob>2012-10-01</dob>
<TotalAge>1.5863</TotalAge>
<Years>1</Years>
<Months>7</Months>
<Days>3</Days>
</RECORD>
</RESULTS1>
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply