WITH all_CTE
AS
(
select 1 id, '1954-11-01' csrq, '男' xb
union all
select 2 id, '1954-12-01' csrq, '男' xb
union all
select 3 id, '1959-11-01' csrq, '女' xb
union all
select 4 id, '1959-12-01' csrq, '女' xb
)select * from all_CTE where id in (
select id from
(select id, DATEADD(YY, case when xb='男' then 60 else 55 end, csrq) csrq from all_CTE) aa
where substring(CONVERT(varchar, csrq, 111),1,7)=substring(CONVERT(varchar, dateadd(month,-1,GETDATE()), 111),1,7)
)
AS
(
select 1 id, '1954-11-01' csrq, '男' xb
union all
select 2 id, '1954-12-01' csrq, '男' xb
union all
select 3 id, '1959-11-01' csrq, '女' xb
union all
select 4 id, '1959-12-01' csrq, '女' xb
)select * from all_CTE where id in (
select id from
(select id, DATEADD(YY, case when xb='男' then 60 else 55 end, csrq) csrq from all_CTE) aa
where substring(CONVERT(varchar, csrq, 111),1,7)=substring(CONVERT(varchar, dateadd(month,-1,GETDATE()), 111),1,7)
)
SET @查询日期='2014-12-01'
SELECT * FROM TB
WHERE (DATEDIFF(MONTH,csrq,@查询日期)/12.0=60 AND xb='男')
OR (DATEDIFF(MONTH,csrq,@查询日期)/12.0=55 AND xb='女')
@FemaleRetureDate VARCHAR(10)
SELECT @MaleRetireDate = CONVERT(VARCHAR(7),DATEADD(MM,-60*12-1,@DT),120)+'-01',
@FemaleRetureDate = CONVERT(VARCHAR(7),DATEADD(MM,-55*12-1,@DT),120)+'-01'
;WITH all_CTE
AS
(
select 1 id, '1954-11-01' csrq, '男' xb
union all
select 2 id, '1954-12-01' csrq, '男' xb
union all
select 3 id, '1959-11-01' csrq, '女' xb
union all
select 4 id, '1959-12-01' csrq, '女' xb
)
SELECT * FROM all_CTE
WHERE (xb='男' AND csrq >= @MaleRetireDate AND csrq < CONVERT(VARCHAR(10),DATEADD(mm,1,@MaleRetireDate),120))
OR (xb='女' AND csrq >= @FemaleRetureDate AND csrq < CONVERT(VARCHAR(10),DATEADD(mm,1,@FemaleRetureDate),120))
select * from tb
where datediff(mm,csrq,getdate())>=(case when xb='男' then 720 else 660 end)