SELECT 
  ROW_NUMBER() over (order by tt.法定退休日期) AS '序号'
  ,tt.工号,tt.姓名,tt.身份证,tt.入职日期,tt.部门,tt.岗位,tt.性别,tt.出生日期,tt.法定退休年龄,tt.法定退休日期
  ,CASE WHEN TT.法定退休日期 = tt.当前日期 THEN 1 ELSE 0 END '是否今天'
FROM (
    SELECT 
 	    t0.FNUMBER AS '工号'
 	    ,t0_l.FNAME AS '姓名'
 	    ,t0.FIDTYPENUMBER AS '身份证'
 	    ,t0.FJOINEDDATE AS '入职日期'
 	    ,t3_l.FNAME AS '部门'
 	    ,t2_l.FNAME AS '岗位'
 	    ,t1.性别
 	    ,t0.FBIRTHDATE AS '出生日期'
 	    ,t1.法定退休年龄
        ,DATEADD(YEAR ,t1.法定退休年龄 ,t0.FBIRTHDATE) AS '法定退休日期'
 	    ,DATEDIFF(MONTH ,t0.FBIRTHDATE ,(CASE tt.跨度类型 WHEN 'M' THEN tt.查询日期 ELSE DATEADD(MONTH,0,DATEADD(YEAR,DATEDIFF(YEAR, 0, tt.查询日期),0)) END)) AS '截止年龄合计月数'
 	    ,t1.法定退休年龄 * 12 AS '退休年龄合计月数'
        ,tt.查询日期
        ,tt.当前日期
        ,tt.跨度类型
        ,tt.时间跨度
        ,(tt.时间跨度 * CASE tt.跨度类型 WHEN 'M' THEN 1 ELSE 12 END) '跨度区间结束'
    FROM T_BD_StaffData t0
 	    INNER JOIN T_BD_StaffData_L t0_l on t0.FID = t0_l.FID
 	    INNER JOIN (
            SELECT t1e.FENTRYID AS SEXID,t1e_l.FDATAVALUE AS '性别'
                ,(CASE t1e_l.FDATAVALUE WHEN '女' THEN 55 ELSE 60 END) '法定退休年龄'
            FROM T_BAS_ASSISTANTDATA_L t1_l
                INNER JOIN T_BAS_ASSISTANTDATAENTRY t1e on t1_l.FID = t1e.FID AND t1_l.FNAME = '性别' AND t1_l.FLOCALEID = 2052
                INNER JOIN T_BAS_ASSISTANTDATAENTRY_L t1e_l on t1e.FENTRYID = t1e_l.FENTRYID AND t1e_l.FLOCALEID = 2052
        ) t1 on t0.FSEX = t1.SEXID
 	    LEFT JOIN T_ORG_POST_L t2_l on t2_l.FPOSTID = t0.FPOSTID
 	    LEFT JOIN T_BD_DEPARTMENT_L t3_l on t3_l.FDEPTID = t0.FDEPARTMENTID
        ,(SELECT '2053-10-20' AS '查询日期'
            ,CONVERT(varchar,GETDATE(),23) AS '当前日期'
            ,'M' AS '跨度类型',1 '时间跨度'
        ) tt
    WHERE t0.FTERMINATIONDATE IS NULL
) tt
WHERE 1=1
    AND (tt.退休年龄合计月数 - tt.截止年龄合计月数) BETWEEN 0 AND tt.跨度区间结束
    AND DATEDIFF(DAY,tt.当前日期,tt.法定退休日期) >= 0