SQL calculates age by age

Question

(using SQL Server 2008)

I need help visualizing the solution. Let's say I have the following simple table for members of a retirement plan:

[Date of Birth]      [Date Joined]      [Date Left]
1970/06/1            2003/01/01         2007/03/01

I need to calculate the number of lives in each age group from 2000 to 2009.

NOTE. On January 1 of each of these gifts, “age” is defined as “age of last birthday” (or “ALB”). for example, if you exactly match 41.35 or 41.77, etc., then you must be ALB 41.

So, if the record above was the only record in the database, then the result would be something like this:

[Year]  [Age ]     [Number of Lives]
2003     32         1
2004     33         1
2005     34         1
2006     35         1
2007     36         1

(There are no files for 2000, 2001, 2002, 2008, and 2009, as the only member joined only on January 1, 2003 and came out on 1/3/2007)

I hope I make myself clear enough.

- ?

,

[EDIT]

:

, :

[Date of Birth]   [Date Joined]   [Date Left]   [Gender]  [Pension Value]
1970/06/1         2003/01/01      2007/03/01   'M'        100,000

, :

[Year]  [Age ]  [Gender] sum([Pension Value])   [Number of Lives]
2003     32       M      100,000                1
2004     33       M      100,000                1
2005     34       M      100,000                1
2006     35       M      100,000                1
2007     36       M      100,000                1

?

Answer 1

-

DECLARE @Table TABLE(
        [Date of Birth] DATETIME,
        [Date Joined] DATETIME,
        [Date Left] DATETIME
)

INSERT INTO @Table ([Date of Birth],[Date Joined],[Date Left]) SELECT '01 Jun 1970', '01 Jan 2003', '01 Mar 2007'
INSERT INTO @Table ([Date of Birth],[Date Joined],[Date Left]) SELECT '01 Jun 1979', '01 Jan 2002', '01 Mar 2008'

DECLARE @StartYear INT,
        @EndYear INT

SELECT  @StartYear = 2000,
        @EndYear = 2009

;WITH sel AS(
    SELECT  @StartYear YearVal
    UNION ALL
    SELECT  YearVal + 1
    FROM    sel 
    WHERE   YearVal < @EndYear
)
SELECT  YearVal AS [Year],
        COUNT(Age) [Number of Lives]
FROM    (
            SELECT  YearVal,
                    YearVal - DATEPART(yy, [Date of Birth]) - 1 Age
            FROM    sel LEFT JOIN
                    @Table  ON  DATEPART(yy, [Date Joined]) <= sel.YearVal
                            AND DATEPART(yy, [Date Left]) >= sel.YearVal
        ) Sub
GROUP BY YearVal

Answer 2

WITH    years AS
        (
        SELECT  1900 AS y
        UNION ALL
        SELECT  y + 1
        FROM    years
        WHERE   y < YEAR(GETDATE())
        ),
        agg AS
        (
        SELECT  YEAR(Dob) AS Yob, YEAR(DJoined) AS YJoined, YEAR(DLeft) AS YLeft
        FROM    mytable
        )
SELECT  y, y - Yob, COUNT(*)
FROM    agg
JOIN    years
ON      y BETWEEN YJoined AND YLeft
GROUP BY
        y, y - Yob
OPTION (MAXRECURSION 0)

, , .

, , , , .

Answer 3

SET NOCOUNT ON

Declare @PersonTable as Table
(
PersonId    Integer,
DateofBirth DateTime,
DateJoined DateTime,
DateLeft DateTime
)

INSERT INTO @PersonTable Values 
(1, '1970/06/10', '2003/01/01', '2007/03/01'),
(1, '1970/07/11', '2003/01/01', '2007/03/01'),
(1, '1970/03/12', '2003/01/01', '2007/03/01'),
(1, '1973/07/13', '2003/01/01', '2007/03/01'),
(1, '1972/06/14', '2003/01/01', '2007/03/01')

Declare @YearTable as Table
(
YearId  Integer,
StartOfYear DateTime
)

insert into @YearTable Values 
(1, '1/1/2000'),
(1, '1/1/2001'),
(1, '1/1/2002'),
(1, '1/1/2003'),
(1, '1/1/2004'),
(1, '1/1/2005'),
(1, '1/1/2006'),
(1, '1/1/2007'),
(1, '1/1/2008'),
(1, '1/1/2009')


;WITH AgeTable AS
(
select StartOfYear, DATEDIFF (YYYY, DateOfBirth, StartOfYear) Age
from @PersonTable
Cross join @YearTable
)
SELECT StartOfYear, Age, COUNT (1) NumIndividuals
FROM AgeTable
GROUP BY StartOfYear, Age
ORDER BY StartOfYear, Age

Answer 4

:

CREATE TABLE People (
ID int PRIMARY KEY
,[Name] varchar(50)
,DateOfBirth datetime
,DateJoined datetime
,DateLeft datetime
)
go

-- some data to test with
INSERT INTO dbo.People
VALUES
     (1, 'Bob', '1961-04-02', '1999-01-01', '2007-05-07')
    ,(2, 'Sadra', '1960-07-11', '1999-01-01', '2008-05-07')
    ,(3, 'Joe', '1961-09-25', '1999-01-01', '2009-02-11')
go

-- helper table to hold years
CREATE TABLE dimYear (
    CalendarYear int PRIMARY KEY
)
go

-- fill-in years for report
DECLARE 
    @yr int 
    ,@StartYear int
    ,@EndYear int

SET @StartYear = 2000
SET @EndYear = 2009

SET @yr = @StartYear
WHILE @yr <= @EndYear
    BEGIN
        INSERT INTO dimYear (CalendarYear) values(@yr)
        SET @yr =@yr+1
    END

-- show test data and year tables
select * from dbo.People
select * from dbo.dimYear
go

, .

-- returns [CalendarYear], [Age] for a member, if still active member in that year
CREATE FUNCTION dbo.MemberAge(@DateOfBirth datetime, @DateLeft datetime)
RETURNS TABLE
AS
RETURN (
    SELECT 
        CalendarYear,
        CASE 
            WHEN DATEDIFF(dd, cast(CalendarYear AS varchar(4)) + '-01-01',@DateLeft) > 0
                THEN DATEDIFF(yy, @DateOfBirth, cast(CalendarYear AS varchar(4)) + '-01-01')
            ELSE -1
        END AS Age
    FROM dimYear    
);
go

:

SELECT
    a.CalendarYear AS "Year"
    ,a.Age AS "Age"
    ,count(*) AS "Number Of Lives"
FROM 
    dbo.People AS p
    CROSS APPLY dbo.MemberAge(p.DateOfBirth, p.DateLeft) AS a
WHERE a.Age > 0
GROUP BY a.CalendarYear, a.Age

Answer 5

( ) - , , :

• ALB - , ALB
• - , , .
• , , , ALB .
• , - , , , , ALB ( ).
• 4. select year, alb, count (id) group by year, alb

, 3, .

You can find the (temporary) table of years useful - joining the date table does everything possible.

Not quite an answer, but, of course, some direction ...