January 30, 2017 at 2:14 pm
All you need is year, since any birthday during the @Service year won't count anyway:
DECLARE @Birth DATETIME = '11/30/1951'
,@Service DATETIME = '2016-08-26'
SELECT PeriodAge = YEAR(@Service) - YEAR(@Birth) - 1
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
January 31, 2017 at 5:43 am
ScottPletcher - Monday, January 30, 2017 2:14 PMAll you need is year, since any birthday during the @Service year won't count anyway:
DECLARE @Birth DATETIME = '11/30/1951'
,@Service DATETIME = '2016-08-26'SELECT PeriodAge = YEAR(@Service) - YEAR(@Birth) - 1
I was wondering about that option. But I think birth date of January 1st is a special case. The "- 1" on the end may need to be in a CASE.
January 31, 2017 at 5:20 pm
ScottPletcher - Monday, January 30, 2017 2:14 PMAll you need is year, since any birthday during the @Service year won't count anyway:
DECLARE @Birth DATETIME = '11/30/1951'
,@Service DATETIME = '2016-08-26'SELECT PeriodAge = YEAR(@Service) - YEAR(@Birth) - 1
Hi Scott, I could have sworn i tried something similar when i first started and it did not work, however i removed the minus one and sure enough it met the rules so far in testing. I really feel like i took something simple and really over thought it. Thanks again !
***SQL born on date Spring 2013:-)
February 1, 2017 at 7:00 pm
;
WITH cte
AS (
SELECT CAST(BirthDate AS DATETIME) AS BirthDate
,CAST(ServiceDate AS DATETIME) AS ServiceDate
FROM (
VALUES
( '01/01/1951', '2016-01-01'),
( '01/01/1951', '2016-08-26'),
( '01/02/1951', '2016-08-26'),
( '08/26/1951', '2016-08-26'),
( '11/01/1951', '2016-08-26'),
( '01/01/1993', '2016-08-26'),
( '01/02/1993', '2016-08-26')
) AS testdata(BirthDate, ServiceDate)
)
SELECT BirthDate
,ServiceDate
,DATEDIFF(YY, BirthDate, Jan1st) AS DiffYearsOnJan1
,DATEDIFF(YY, BirthDate, ServiceDate) AS DiffYearsOnServiceDate
,DATEDIFF(YY, BirthDate, Jan1st) - IIF(MONTH(BirthDate) = MONTH(Jan1st)
AND DAY(BirthDate) = DAY(Jan1st), 0, 1) AS ActualAgeOnJan1
FROM cte
CROSS APPLY (
SELECT DATEFROMPARTS(YEAR(ServiceDate), 1, 1) AS Jan1st
) _;
BirthDate ServiceDate DiffYearsOnJan1 DiffYearsOnServiceDate ActualAgeOnJan1
1951-01-01 00:00:00.000 2016-01-01 00:00:00.000 65 65 65
1951-01-01 00:00:00.000 2016-08-26 00:00:00.000 65 65 65
1951-01-02 00:00:00.000 2016-08-26 00:00:00.000 65 65 64
1951-08-26 00:00:00.000 2016-08-26 00:00:00.000 65 65 64
1951-11-01 00:00:00.000 2016-08-26 00:00:00.000 65 65 64
1993-01-01 00:00:00.000 2016-08-26 00:00:00.000 23 23 23
1993-01-02 00:00:00.000 2016-08-26 00:00:00.000 23 23 22
February 1, 2017 at 8:13 pm
Shifting gears back to this part of the original post...
So if I have a BirthDate = 11/30/1951 there age as a whole number as of January 1st of the service year. In this case any date within 2016
So any date in 1951 should = 65
any date in 1952 = 63
Is that a mistake? If not, please explain how 1951 could be 65 and 1952, which is just 1 year later, could be 2 years less in age?
--Jeff Moden
February 1, 2017 at 8:19 pm
thomashohner - Tuesday, January 31, 2017 5:20 PMScottPletcher - Monday, January 30, 2017 2:14 PMAll you need is year, since any birthday during the @Service year won't count anyway:
DECLARE @Birth DATETIME = '11/30/1951'
,@Service DATETIME = '2016-08-26'SELECT PeriodAge = YEAR(@Service) - YEAR(@Birth) - 1
Hi Scott, I could have sworn i tried something similar when i first started and it did not work, however i removed the minus one and sure enough it met the rules so far in testing. I really feel like i took something simple and really over thought it. Thanks again !
It depends... maybe it is a mistake. If someone is born in December of 1951, are they 0 years old or 1 year old on 1952-01-01 according to your requirements. Same question if they were born in January of 1951.
--Jeff Moden
February 1, 2017 at 11:15 pm
Sorry, my previous reply had a bug for computing ActualAgeOnJan1. Corrected code:
;
WITH cte
AS (
SELECT CAST(BirthDate AS DATE) AS BirthDate
,CAST(ServiceDate AS DATE) AS ServiceDate
FROM (
VALUES
('01/01/1951', '2016-01-01'),
('01/01/1951', '2016-08-26'),
('01/02/1951', '2016-08-26'),
('08/26/1951', '2016-08-26'),
('11/01/1951', '2016-08-26'),
('01/01/1993', '2016-08-26'),
('01/02/1993', '2016-08-26'),
('12/31/2015', '2016-08-26'),
('01/01/2016', '2016-08-26'),
('01/02/2016', '2016-08-26')
) AS testdata(BirthDate, ServiceDate)
)
SELECT BirthDate
,ServiceDate
,DATEDIFF(YY, BirthDate, Jan1st) AS DiffYearsOnJan1
,DATEDIFF(YY, BirthDate, ServiceDate) AS DiffYearsOnServiceDate
,DATEDIFF(YY, BirthDate, Jan1st) - IIF(YEAR(BirthDate) = YEAR(Jan1st)
OR (
MONTH(BirthDate) = MONTH(Jan1st)
AND DAY(BirthDate) = DAY(Jan1st)
), 0, 1) AS ActualAgeOnJan1
FROM cte
CROSS APPLY (
SELECT DATEFROMPARTS(YEAR(ServiceDate), 1, 1) AS Jan1st
) _;BirthDate ServiceDate DiffYearsOnJan1 DiffYearsOnServiceDate ActualAgeOnJan1
1951-01-01 2016-01-01 65 65 65
1951-01-01 2016-08-26 65 65 65
1951-01-02 2016-08-26 65 65 64
1951-08-26 2016-08-26 65 65 64
1951-11-01 2016-08-26 65 65 64
1993-01-01 2016-08-26 23 23 23
1993-01-02 2016-08-26 23 23 22
2015-12-31 2016-08-26 1 1 0
2016-01-01 2016-08-26 0 0 0
2016-01-02 2016-08-26 0 0 0
February 2, 2017 at 6:47 am
dale_berta - Tuesday, January 31, 2017 5:43 AMScottPletcher - Monday, January 30, 2017 2:14 PMAll you need is year, since any birthday during the @Service year won't count anyway:
DECLARE @Birth DATETIME = '11/30/1951'
,@Service DATETIME = '2016-08-26'SELECT PeriodAge = YEAR(@Service) - YEAR(@Birth) - 1
I was wondering about that option. But I think birth date of January 1st is a special case. The "- 1" on the end may need to be in a CASE.
I can confirm that birth date of Jan 1 needs to be handled. If you're born on Jan 1, then you're 1 year on on Jan 1 of the next year. If you're born anywhere from Jan 2 through Dec 31, you're 0 years (and x months and days) on Jan 1 of the next year.
To make this more obvious, I'll use test birth year of 2000, and service dates in 2001 and 2010:
DECLARE @TestCases TABLE
(
BirthDate DATE
,ServiceDate DATE
);
INSERT INTO @TestCases
VALUES ('2000-01-01', '2001-01-01'), ('2000-01-01', '2001-07-01')
,('2000-04-01', '2001-01-01'), ('2000-04-01', '2001-07-01')
,('2000-01-01', '2010-01-01'), ('2000-01-01', '2010-07-01')
,('2000-04-01', '2010-01-01'), ('2000-04-01', '2010-07-01');
SELECT t.BirthDate
,t.ServiceDate
,YEAR(t.ServiceDate) - YEAR(t.BirthDate) AS OrigAgeOnJan1
,YEAR(t.ServiceDate) - YEAR(t.BirthDate)
- CASE WHEN MONTH(t.BirthDate) = 1 AND DAY(t.BirthDate) = 1 THEN 0 ELSE 1 END
AS FixedAgeOnJan1
FROM @TestCases AS t;
The results are:
BirthDate ServiceDate OrigAgeOnJan1 FixedAgeOnJan1
---------- ----------- ------------- --------------
2000-01-01 2001-01-01 1 1
2000-01-01 2001-07-01 1 1
2000-04-01 2001-01-01 1 0
2000-04-01 2001-07-01 1 0
2000-01-01 2010-01-01 10 10
2000-01-01 2010-07-01 10 10
2000-04-01 2010-01-01 10 9
2000-04-01 2010-07-01 10 9
I haven't run any benchmarks, but I anticipate this calculation will be faster than anything involving DATEDIFF or DATEFROMPARTS.
February 2, 2017 at 8:55 am
dale_berta - Tuesday, January 31, 2017 5:43 AMScottPletcher - Monday, January 30, 2017 2:14 PMAll you need is year, since any birthday during the @Service year won't count anyway:
DECLARE @Birth DATETIME = '11/30/1951'
,@Service DATETIME = '2016-08-26'SELECT PeriodAge = YEAR(@Service) - YEAR(@Birth) - 1
I was wondering about that option. But I think birth date of January 1st is a special case. The "- 1" on the end may need to be in a CASE.
I can confirm that birth date of Jan 1 needs to be handled. If you're born on Jan 1, then you're 1 year on on Jan 1 of the next year. If you're born anywhere from Jan 2 through Dec 31, you're 0 years (and x months and days) on Jan 1 of the next year.
The original q states that "any date in yyya should be na", "any date in yyyb should be nb", etc., so it seemed clear to me that a Jan 1 birthday didn't actually matter. In that case, it's a very simple calc to get the actual age, as given above.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
February 2, 2017 at 9:09 am
Jeff Moden - Wednesday, February 1, 2017 8:19 PMthomashohner - Tuesday, January 31, 2017 5:20 PMScottPletcher - Monday, January 30, 2017 2:14 PMAll you need is year, since any birthday during the @Service year won't count anyway:
DECLARE @Birth DATETIME = '11/30/1951'
,@Service DATETIME = '2016-08-26'SELECT PeriodAge = YEAR(@Service) - YEAR(@Birth) - 1
Hi Scott, I could have sworn i tried something similar when i first started and it did not work, however i removed the minus one and sure enough it met the rules so far in testing. I really feel like i took something simple and really over thought it. Thanks again !
It depends... maybe it is a mistake. If someone is born in December of 1951, are they 0 years old or 1 year old on 1952-01-01 according to your requirements. Same question if they were born in January of 1951.
My Apologies, its clear i did not proof read my initial post.
The way CMS has it with examples
Patients 18 years of age and older were born on or before December 31, 1997 = 18 +
Patients born between January 1, 2007, and December 31, 2009 = 6 and 9 years of age
Patients born between January 1, 1952, and December 31, 1992 = 24 and 64 years of age
Patients born on or before December 31, 2003, = 12 +
I hope this clarifies. Again my appologies for a bad opening.
***SQL born on date Spring 2013:-)
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply