按年龄划分的生命周期SQL计数暴露

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)

同一年出生的人在你的模型中总是有相同的年龄

这就是为什么如果他们真的去了,他们总是去一个小组,你只需要在他们留在项目期间每年生成一行。

你可以试试这样的

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

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

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

分块处理(一些随机想法)-创建视图以测试开发步骤(如果可以):

1. ALB-做一个查询,在给定的一年内,给出你的成员的ALB
2. 年度成员-另一个查询,告诉您某个成员是否是给定年度的成员
3. 把这两者放在一起,你应该能够创建一个查询,说明某个人在某一年是否是会员,以及该年的ALB是多少。
4. 嗯,很棘手-遵循这一思路,然后您要做的是生成一个表,该表包含该人在该年的所有成员年份和他们的ALB(以及唯一id)

我不确定我从大约3点开始是否朝着正确的方向前进,尽管它应该会起作用。

你可能会发现一个(临时的)年表很有帮助——把事情和日期表联系起来,使各种事情都成为可能。

不是一个真正的答案,但肯定有一些方向。。。