mohan111 mohan111 - 26 days ago 12
SQL Question

How to get Age calculation for different years

I have some sample data like this :

DECLARE @T Table (ID INT, Name VARCHAR(10), DOB DATE)
INSERT INTO @T (ID, Name, DOB) VALUES (1,'Mohan', '1937-12-01')
INSERT INTO @T (ID, Name, DOB) VALUES (2,'Raj', '1936-12-25')
INSERT INTO @T (ID, Name, DOB) VALUES (5,'Manny', '1937-01-30')
INSERT INTO @T (ID, Name, DOB) VALUES (3,'kamal', '1938-12-12')
INSERT INTO @T (ID, Name, DOB) VALUES (4,'Raj', '1937-05-12')

SELECT * FROM @T


By using this below query i will find the age in years and months and Days

SELECT Name,cast((DATEDIFF(m, DOB, GETDATE())/12) as varchar) + ' Y & ' +
cast((DATEDIFF(m, DOB, GETDATE())%12) as varchar) + ' M & ' +
cast((DATEDIFF(D, DOB, GETDATE())%12) as varchar) + ' d' from @T


I just want to know who is reaching the 78,79 and 80 years in coming 45 days .

By using below query i can get the the data who is reaching 80 years in coming 45 days

where
DOB > DATEADD(year,-80,GETDATE()) and
DOB < DATEADD(year,-80,DATEADD(day,45,GETDATE()))


this all giving data one at a time if i pass 80 it will give data who are reaching 80 years.If i give 79 it will give data who are reaching 79 years.
But how can i all the data who are reaching 78,79 and 80 years in coming 45 days.

suggest me the best way

Name Age
Mohan 78 Y & 11 M & 9 d
Raj 79 Y & 11 M & 2 d
kamal 77 Y & 11 M & 5 d


Above people are reaching respective years with in 45 days

Mohan 79 --> with in 45 days
Raj 80 --> with in 45 days
kamal 78 --> with in 45 days

Answer

To find everyone whose birthday is within the next 45 days, it's often easiest to reset all of the values you're comparing into a single year, and then work from there:

DECLARE @T Table (ID INT,Name VARCHAR(10),DOB DATE)
INSERT INTO @T (ID,Name,DOB) VALUES (1,'Mohan','1937-12-01')
INSERT INTO @T (ID,Name,DOB) VALUES (2,'Raj','1936-12-25')
INSERT INTO @T (ID,Name,DOB) VALUES (5,'Manny','1937-01-30')
INSERT INTO @T (ID,Name,DOB) VALUES (3,'kamal','1938-12-12')
INSERT INTO @T (ID,Name,DOB) VALUES (4,'Raj','1937-05-12')

declare @Today date
--Set "Today" to today's date in 2000
set @Today = DATEADD(year,DATEDIFF(year,GETDATE(),'20000101'),GETDATE())
declare @Plus45 date
set @Plus45 = DATEADD(day,45,@Today)
--And reset to 2000 (year wrap-around)
set @Plus45 = DATEADD(year,DATEDIFF(year,@Plus45,'20000101'),@Plus45)
select @Today as Today,@Plus45 as Plus45

;With ResetDOBs as (
    select
        *,
        DATEADD(year,DATEDIFF(year,DOB,'20000101'),DOB) as DOB2000
    from
        @T
)
select
    *
from
    ResetDOBs
where
    DOB2000 between @Today and @Plus45 or
    (@Plus45 < @Today and
        (DOB2000 <= @Plus45 or DOB2000 >= @Today)
    )

Results:

ID          Name       DOB        DOB2000
----------- ---------- ---------- ----------
1           Mohan      1937-12-01 2000-12-01
3           kamal      1938-12-12 2000-12-12

We could use any year for the reset, provided that it's a leap year. I chose 2000. There's a small bit of juggling both in computing @Plus45 and in the final Where clause to deal with oddities with using this method when the 45 day period overlaps the year-end transition, December/January.

(In my time zone, it's currently 2016-11-09, and so Raj (ID 2) is not quite within the 45 day window yet)


This query:

SELECT Name,cast((DATEDIFF(m, DOB, GETDATE())/12) as varchar) + ' Y & ' + 
cast((DATEDIFF(m, DOB, GETDATE())%12) as varchar) + ' M & ' +
cast((DATEDIFF(D, DOB, GETDATE())%12) as varchar) + ' d'  from @T

Is still badly broken. Raj (ID 2) would not report their age as 79 Y & 11 M & 2 d, as you report. They'd report their age as 79 years, 10 months and some days. Your month calculation can easily overcount by one, and your day calculation is confused (why %12 for a day calculation?).

Here's one way to do the Y/M/D calculations that should be closer to human expectations of people's ages. If you have a numbers table, you can use it to create the NumPairs CTE, rather than how I've created it here:

DECLARE @T Table (ID INT,Name VARCHAR(10),DOB DATE)
INSERT INTO @T (ID,Name,DOB) VALUES (1,'Mohan','1937-12-01')
INSERT INTO @T (ID,Name,DOB) VALUES (2,'Raj','1936-12-25')
INSERT INTO @T (ID,Name,DOB) VALUES (5,'Manny','1937-01-30')
INSERT INTO @T (ID,Name,DOB) VALUES (3,'kamal','1938-12-12')
INSERT INTO @T (ID,Name,DOB) VALUES (4,'Raj','1937-05-12')

declare @Today date
set @today = GETDATE()

;With NumPairs as (
    select 0 as n1, 1 as n2
    union all
    select n1+1,n2+1
    from NumPairs
    where n1 <= 150
)
select
    t.*,
    y.n1 as Y,
    m.n1 as M,
    d.n1 as D
from
    @T t
        cross apply
    (select n1,DATEADD(year,n1,DOB) as DOBy from NumPairs
    where DATEADD(year,n1,DOB) <= @today and
        DATEADD(year,n2,DOB) > @today
    ) y
        cross apply
    (select n1,DATEADD(month,n1,DOBy) as DOBmy from NumPairs
    where DATEADD(month,n1,DOBy) <= @today and
        DATEADD(month,n2,DOBy) > @today
    ) m
        cross apply
    (select n1 from NumPairs
    where DATEADD(day,n1,DOBmy) = @today
    ) d
option (maxrecursion 0)

Result:

ID          Name       DOB        Y           M           D
----------- ---------- ---------- ----------- ----------- -----------
3           kamal      1938-12-12 77          10          28
1           Mohan      1937-12-01 78          11          8
2           Raj        1936-12-25 79          10          15
5           Manny      1937-01-30 79          9           10
4           Raj        1937-05-12 79          5           28
Comments