SQL count exposure of life time by age

(Using SQL Server 2008)

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

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

I need开发者_如何学C to calculate the number of lives in each age group from 2000 to 2009.

NOTE: "Age" is defined as "age last birthday" (or "ALB") on 1 January of each of those yeasrs. e.g. if you are exactly 41.35 or 41.77 etc. years old on 1/1/2009 then you would be ALB 41.

So if the record above were the only entry in the database, then the output would be something like:

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

(For 2000, 2001, 2002, 2008 and 2009 there are no lives on file since the sole member only joined on 1/1/2003 and left on 1/3/2007)

I hope I am making myself clear enough.

Anyone have any suggestions?

Thanks, Karl

[EDIT]

Adding another layer to the problem:

What if I had:

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

and I want the output to be:

[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

Any ideas?

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)

People born on same year always have the same age in your model

That's why if they go at all, they always go into one group and you just need to generate one row per year for the period they stay in the program.

You can try something like this

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

Try the following sample query

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

First some preparation to have something to test with:

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

Then a function to return person's age for each year, if the person is still an active member.

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

And the final query:

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

Deal with this in pieces (some random thoughts) - create views to test you dev steps if you can:

1. ALB - do a query that, for a given year, gives you your memeber's ALB
2. Member in year - another bit of query that tell you whether a member was a member in a given year
3. Put those two together and you should be able to create a query that says whether a person was a member in a given year and what their ALB was for that year.
4. Hmm, tricky - following this chain of thought what you'd then want to do is generate a table that has all the years the person was a member and their ALB in that year (and a unique id)
5. From 4. select year, alb, count(id) group by year, alb

I'm not sure I'm going in the right direction from about 3 though it should work.

You may find a (temporary) table of years helpful - joining things to a table of dates makes all kinds of things possible.

Not really an answer, but certainly some direction...