sql2008 根据生效日期和状态选出相关的人员，请指导...

[newchar]

table a: id,effdate,status
status: 0--new 1-rehire 2-terminated

如某人a存在如下记录:
a,2014-01-10, 0
a,2014-04-10,2
a,2014-08-10,1
...

现在想要统计如下结果：
根据选定日期，包或不包括当月离职的 active 人数（status不等于2都属于active; ）;
不包括当月离职：
1. 选定日期2014-03-15， a应该包括
2. 选定日期2014-04-15， a应该不包括
3. 选定日期2014-05-15 ， a应该不包括
...选定日期6月，7月， a应该不包括
4. 选定日期2014-08-15 ， a应该包括
5. 选定日期2014-09-15， a应该包括
...
包括当月离职：
1. 选定日期2014-03-15， a应该包括
2. 选定日期2014-04-15， a应该包括
3. 选定日期2014-05-15 ， a应该不包括
...选定日期6月，7月， a应该不包括
4. 选定日期2014-08-15 ， a应该包括
5. 选定日期2014-09-15， a应该包括
。。。

谢谢

[newchar]

有没有简单点

[还在加载中灬]

引用 2 楼 newchar 的回复:

有没有简单点

分组排序，貌似只能这样了

--不包括当月离职：
SELECT * FROM(
	SELECT ROW_NUMBER()OVER(PARTITION BY id ORDER BY effdate DESC)RN,* FROM a
	WHERE effdate<='2014-03-15'
)T
WHERE RN=1 AND STATUS<>2
--包括当月离职：
SELECT * FROM(
	SELECT ROW_NUMBER()OVER(PARTITION BY id ORDER BY effdate DESC)RN,* FROM a
	WHERE effdate<='2014-03-15'
)T
WHERE RN=1 AND STATUS=2

[Tiger_Zhao]

DECLARE @InputTime DATETIME
DECLARE @MonthFirst DATETIME
SET @InputTime='2014-04-15'
SET @MonthFirst = Convert(datetime,
                          Convert(varchar(7),@InputTime,120)+'-01',
                          120)

;WITH a(id,effdate,status) AS (--测试数据
    SELECT 'a',Convert(datetime,'2014-01-10',120),0 UNION ALL
    SELECT 'a',Convert(datetime,'2014-04-10',120),2 UNION ALL
    SELECT 'a',Convert(datetime,'2014-08-10',120),1
)
,u (id) AS (--用户表
    SELECT 'a'
)
,t AS (
        SELECT *
          FROM u
   CROSS APPLY ( -- 取最新的状态
                    SELECT TOP 1 status
                      FROM a
                     WHERE a.id = u.id
                     /* 不包括当月离职的条件 
                       AND effdate <= @InputTime 
                     */
                     /* 包括当月离职的条件 */
                       AND (  (status <> 2 AND effdate <= @InputTime)  -- 非离职的统计到查询日期
                            OR(status = 2 AND  effdate <= @MonthFirst) -- 离职的仅统计到上月底
                           )
                  ORDER BY effdate DESC
               ) s
)
SELECT Count(*) 人数
  FROM t
 WHERE status <> 2

就是两个条件选一个，不写两句SQL了，应该能看明白。

[还在加载中灬]

DECLARE @InputTime DATETIME
SET @InputTime='2014-03-15'
;WITH CTE AS(
	SELECT ROW_NUMBER()OVER(PARTITION BY id ORDER BY effdate)RN,* FROM a
)
SELECT T1.id,T1.effdate,T1.STATUS FROM CTE T1
	LEFT JOIN CTE T2 ON T1.id=T2.id AND T1.RN+1=T2.RN
WHERE T1.effdate<=@InputTime AND ISNULL(T2.effdate,GETDATE())>@InputTime

你参考下