sql - 如何获得不同年份的年龄计算

Question

我有一些这样的示例数据:

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

通过使用下面的查询，我将找到以年、月和日为单位的年龄

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 

我只想知道在接下来的 45 天里谁会达到 78、79 和 80 年。

通过使用下面的查询，我可以获得在未来 45 天内达到 80 岁的数据

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

如果我超过 80，这将一次提供一个数据，它将提供达到 80 岁的数据。如果我提供 79，它将提供达到 79 岁的数据。但是我怎么能在接下来的 45 天内达到 78、79 和 80 年的所有数据。

建议我最好的方法

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

以上人在45天内达到各自的年份

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

Answer 1

要查找生日在未来 45 天内的每个人，通常最简单的方法是将您要比较的所有值重置为一年，然后从那里开始工作:

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)
    )

结果:

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

我们可以使用任何年份进行重置，前提是它是闰年。我选择了 2000。当 45 天的时间段与年终过渡，12 月/1 月。

(在我所在的时区，目前是 2016 年 11 月 9 日，因此 Raj(ID 2)还没有完全在 45 天的期限内)

---

这个查询:

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

还是坏了。正如您所报告的，Raj(ID 2)不会将他们的年龄报告为 79 Y & 11 M & 2 d。他们会报告自己的年龄为 79 岁 10 个月零几天。您的月份计算很容易多算一个，而您的日计算很困惑(为什么 %12 用于日计算？)。

这是一种进行 Y/M/D 计算的方法，它应该更接近人类对人们年龄的预期。如果您有一个数字表，您可以使用它来创建 NumPairs CTE，而不是我在这里创建它的方式:

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)

结果:

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