员工信息表
EmpCode(员工编码),EmpName(员工姓名),Sex(性别),Brithday(出生日期),DeptName(所在部门),Station(岗位)
要求根据员工信息表内容得到三个月之内退休的人员信息
男的退休年龄为60,根据Brithday计算,
女的退休年龄计算方式为如果岗位=‘领导’则55岁
如果岗位=‘工人’则退休年龄为50==============================================================================================================
Deptname Empcode EmpName sex brithday station
人力资源部 19591008101 朱 女 1959-10-08 工人
人力资源部 19490221001 马 男 1949-11-21 工人
人力资源部 19500609001 宋 女 1954-12-09 领导
人力资源部 19510522001 周 男 1951-05-22 工人 结果:
Deptname Empcode EmpName sex brithday station ADate AMonth
人力资源部 19591008101 朱 女 1959-10-08 工人 2009-10-08 1
人力资源部 19490221001 马 男 1949-11-21 工人 2009-11-21 2
人力资源部 19500609001 宋 女 1954-12-09 领导 2009-12-09 3
EmpCode(员工编码),EmpName(员工姓名),Sex(性别),Brithday(出生日期),DeptName(所在部门),Station(岗位)
要求根据员工信息表内容得到三个月之内退休的人员信息
男的退休年龄为60,根据Brithday计算,
女的退休年龄计算方式为如果岗位=‘领导’则55岁
如果岗位=‘工人’则退休年龄为50==============================================================================================================
Deptname Empcode EmpName sex brithday station
人力资源部 19591008101 朱 女 1959-10-08 工人
人力资源部 19490221001 马 男 1949-11-21 工人
人力资源部 19500609001 宋 女 1954-12-09 领导
人力资源部 19510522001 周 男 1951-05-22 工人 结果:
Deptname Empcode EmpName sex brithday station ADate AMonth
人力资源部 19591008101 朱 女 1959-10-08 工人 2009-10-08 1
人力资源部 19490221001 马 男 1949-11-21 工人 2009-11-21 2
人力资源部 19500609001 宋 女 1954-12-09 领导 2009-12-09 3
DATEDIFF(MONTH,GETDATE(),
CASE sex WHEN '男' THEN DATEADD(YEAR,60,brithday)
ELSE
CASE station WHEN '领导' THEN DATEADD(YEAR,55,brithday)
ELSE
DATEADD(YEAR,50,brithday)
END
END
)<=3
AND
brithday>=GETDATE()
declare @t table(Deptname varchar(20), Empcode varchar(20),EmpName varchar(20),sex varchar(20),brithday datetime,station varchar(20))
Insert @t select
'人力资源部', '19591008101', '朱', '女', '1959-10-08', '工人' union all select
'人力资源部', '19490221001', '马', '男', '1949-11-21', '工人' union all select
'人力资源部', '19500609001', '宋', '女', '1954-12-09', '领导' union all select
'人力资源部', '19510522001', '周', '男', '1951-05-22', '工人'
/*
Deptname Empcode EmpName sex brithday station ADate AMonth
人力资源部 19591008101 朱 女 1959-10-08 工人 2009-10-08 1
人力资源部 19490221001 马 男 1949-11-21 工人 2009-11-21 2
人力资源部 19500609001 宋 女 1954-12-09 领导 2009-12-09 3
*/
select
*
from
@t
where
datediff(year,brithday,getdate())=
case when sex='男' then 60
when sex='女' and station='领导' then 55
else 50
end Deptname Empcode EmpName sex brithday station
-------------------- -------------------- -------------------- -------------------- ----------------------- --------------------
人力资源部 19591008101 朱 女 1959-10-08 00:00:00.000 工人
人力资源部 19490221001 马 男 1949-11-21 00:00:00.000 工人
人力资源部 19500609001 宋 女 1954-12-09 00:00:00.000 领导(3 行受影响)
or (datediff(yy,birthday,getdate()+90)>60 and SEX='男')
Deptname,Empcode,EmpName,sex,brithday,station
dateadd(year,case when sex = '男' then 60
when sex='女' and station='领导' then 55
else 50 end,Brithday) as ADate
datediff(month,getdate(),dateadd(year,case when sex = '男' then 60
when sex='女' and station='领导' then 55
else 50 end,Brithday) ) as AMonth
from tb
where sex='男' and Brithday<= dateadd(year,-60,dateadd(month,3,getdate()))
or sex='女' and station='领导' and Brithday<= dateadd(year,-55,dateadd(month,3,getdate
()))
or sex='女' and station='工人' and Brithday<= dateadd(year,-50,dateadd(month,3,getdate
()))
Insert @t select
'人力资源部', '19591008101', '朱', '女', '1959-10-08', '工人' union all select
'人力资源部', '19490221001', '马', '男', '1949-11-21', '工人' union all select
'人力资源部', '19500609001', '宋', '女', '1954-12-09', '领导' union all select
'人力资源部', '19510522001', '周', '男', '1951-05-22', '工人'
/*
Deptname Empcode EmpName sex brithday station ADate AMonth
人力资源部 19591008101 朱 女 1959-10-08 工人 2009-10-08 1
人力资源部 19490221001 马 男 1949-11-21 工人 2009-11-21 2
人力资源部 19500609001 宋 女 1954-12-09 领导 2009-12-09 3
*/
SELECT * FROM @T WHERE
DATEDIFF(MONTH,GETDATE(),
CASE sex WHEN '男' THEN DATEADD(YEAR,60,brithday)
ELSE
CASE station WHEN '领导' THEN DATEADD(YEAR,55,brithday)
ELSE
DATEADD(YEAR,50,brithday)
END
END
)<=3
AND
DATEDIFF(MONTH,GETDATE(),
CASE sex WHEN '男' THEN DATEADD(YEAR,60,brithday)
ELSE
CASE station WHEN '领导' THEN DATEADD(YEAR,55,brithday)
ELSE
DATEADD(YEAR,50,brithday)
END
END
)>=0/*
人力资源部 19591008101 朱 女 1959-10-08 00:00:00.000 工人
人力资源部 19490221001 马 男 1949-11-21 00:00:00.000 工人
人力资源部 19500609001 宋 女 1954-12-09 00:00:00.000 领导
*/
select * from tableName
where (datediff(yy,birthday,getdate()-90)>60 and Sex='男')
or (datediff(yy,birthday,getdate()-90)>50 and Sex='女' and station='工人')
or (datediff(yy,birthday,getdate()-90)>50 and Sex='女' and station='领导')
station ADate AMonth
Empcode varchar(20),
EmpName varchar(20),
sex char(2),
brithday datetime,
station varchar(20))
insert @t
select '人力资源部','19591008101','朱','女','1959-10-08','工人' union all
select '人力资源部','19490221001','马','男','1949-11-21','工人' union all
select '人力资源部','19500609001','宋','女','1954-12-09','领导' union all
select '人力资源部','19510522001','周','男','1951-05-22','工人';with tb as(
select *,adate =(case when sex='男' and datediff(year,brithday,dateadd(month,-3,getdate()))>=60 then dateadd(year,60,brithday)
when sex='女' and station='领导' and datediff(year,brithday,dateadd(month,-3,getdate()))>=55 then dateadd(year,55,brithday)
when sex='女' and station='工人' and datediff(year,brithday,dateadd(month,-3,getdate()))>=50 then dateadd(year,50,brithday) end)
from @t)select *,amonth=datediff(month,getdate(),adate) from tb where adate is not null
/*
Deptname Empcode EmpName sex brithday station adate amonth
-------------------- -------------------- -------------------- ---- ----------------------- -------------------- ----------------------- -----------
人力资源部 19591008101 朱 女 1959-10-08 00:00:00.000 工人 2009-10-08 00:00:00.000 1
人力资源部 19490221001 马 男 1949-11-21 00:00:00.000 工人 2009-11-21 00:00:00.000 2
人力资源部 19500609001 宋 女 1954-12-09 00:00:00.000 领导 2009-12-09 00:00:00.000 3(3 行受影响)
*/
declare @t table(Deptname varchar(20), Empcode varchar(20),EmpName varchar(20),sex varchar(20),brithday datetime,station varchar(20))
Insert @t select
'人力资源部', '19591008101', '朱', '女', '1959-10-08', '工人' union all select
'人力资源部', '19490221001', '马', '男', '1949-11-21', '工人' union all select
'人力资源部', '19500609001', '宋', '女', '1954-12-09', '领导' union all select
'人力资源部', '19510522001', '周', '男', '1951-05-22', '工人'
/*
Deptname Empcode EmpName sex brithday station ADate AMonth
人力资源部 19591008101 朱 女 1959-10-08 工人 2009-10-08 1
人力资源部 19490221001 马 男 1949-11-21 工人 2009-11-21 2
人力资源部 19500609001 宋 女 1954-12-09 领导 2009-12-09 3
*/
select
*,
ADate=dateadd(year,case when sex='男' then 60
when sex='女' and station='领导' then 55
else 50
end, brithday),
AMonth=datediff(month,getdate(),dateadd(year,case when sex='男' then 60
when sex='女' and station='领导' then 55
else 50
end, brithday))
from
@t
where
datediff(year,brithday,getdate())>=
case when sex='男' then 60
when sex='女' and station='领导' then 55
else 50
end Deptname Empcode EmpName sex brithday station ADate AMonth
-------------------- -------------------- -------------------- -------------------- ----------------------- -------------------- ----------------------- -----------
人力资源部 19591008101 朱 女 1959-10-08 00:00:00.000 工人 2009-10-08 00:00:00.000 1
人力资源部 19490221001 马 男 1949-11-21 00:00:00.000 工人 2009-11-21 00:00:00.000 2
人力资源部 19500609001 宋 女 1954-12-09 00:00:00.000 领导 2009-12-09 00:00:00.000 3(3 行受影响)
Empcode varchar(20),
EmpName varchar(20),
sex char(2),
brithday datetime,
station varchar(20))
insert @t
select '人力资源部','19591008101','朱','女','1959-10-08','工人' union all
select '人力资源部','19490221001','马','男','1949-11-21','工人' union all
select '人力资源部','19500609001','宋','女','1954-12-09','领导' union all
select '人力资源部','19510522001','周','男','1951-05-22','工人';with tb as(
select *,adate =(case when sex='男' and datediff(year,brithday,dateadd(month,-3,getdate()))>=60 then dateadd(year,60,brithday)
when sex='女' and station='领导' and datediff(year,brithday,dateadd(month,-3,getdate()))>=55 then dateadd(year,55,brithday)
when sex='女' and station='工人' and datediff(year,brithday,dateadd(month,-3,getdate()))>=50 then dateadd(year,50,brithday) end)
from @t)select *,amonth=datediff(month,getdate(),adate) from tb where adate is not null
/*
Deptname Empcode EmpName sex brithday station adate amonth
------------ -------------- ---------- ---- ----------- --------- ----------- -----------
人力资源部 19591008101 朱 女 1959-10-08 工人 2009-10-08 1
人力资源部 19490221001 马 男 1949-11-21 工人 2009-11-21 2
人力资源部 19500609001 宋 女 1954-12-09 领导 2009-12-09 3(3 行受影响)
*/
Insert @t select
'人力资源部', '19591008101', '朱', '女', '1959-10-08', '工人' union all select
'人力资源部', '19490221001', '马', '男', '1949-11-21', '工人' union all select
'人力资源部', '19500609001', '宋', '女', '1954-12-09', '领导' union all select
'人力资源部', '19510522001', '周', '男', '1951-05-22', '工人' SELECT *
,CASE sex WHEN '男' THEN DATEADD(YEAR,60,brithday)
ELSE
CASE station WHEN '领导' THEN DATEADD(YEAR,55,brithday)
ELSE
DATEADD(YEAR,50,brithday)
END
END '退休日'
,
DATEDIFF(MONTH,GETDATE(),
CASE sex WHEN '男' THEN DATEADD(YEAR,60,brithday)
ELSE
CASE station WHEN '领导' THEN DATEADD(YEAR,55,brithday)
ELSE
DATEADD(YEAR,50,brithday)
END
END
) '离退休的月数'
FROM @T WHERE
DATEDIFF(MONTH,GETDATE(),
CASE sex WHEN '男' THEN DATEADD(YEAR,60,brithday)
ELSE
CASE station WHEN '领导' THEN DATEADD(YEAR,55,brithday)
ELSE
DATEADD(YEAR,50,brithday)
END
END
)<=3
AND
DATEDIFF(MONTH,GETDATE(),
CASE sex WHEN '男' THEN DATEADD(YEAR,60,brithday)
ELSE
CASE station WHEN '领导' THEN DATEADD(YEAR,55,brithday)
ELSE
DATEADD(YEAR,50,brithday)
END
END
)>=0/*
人力资源部 19591008101 朱 女 1959-10-08 00:00:00.000 工人 2009-10-08 00:00:00.000 1
人力资源部 19490221001 马 男 1949-11-21 00:00:00.000 工人 2009-11-21 00:00:00.000 2
人力资源部 19500609001 宋 女 1954-12-09 00:00:00.000 领导 2009-12-09 00:00:00.000 3
*/
insert @a select '人力资源部' ,'19591008101' ,'朱' ,'女' ,'1959-10-08' ,'工人'
union all select '人力资源部' ,'19490221001' ,'马' ,'男' ,'1949-11-21' ,'工人'
union all select '人力资源部' ,'19500609001' ,'宋' ,'女' ,'1954-12-09' ,'领导'
union all select '人力资源部' ,'19510522001' ,'周' ,'男' ,'1951-05-22' ,'工人'SELECT Deptname,EmpCode,EmpName,Sex,Brithday,Station,
DATEADD(YEAR,ye,brithday) Adate,
DATEDIFF(MONTH,GETDATE(),DATEADD(YEAR,ye,brithday)) Amonth
FROM
(
SELECT *,CASE WHEN sex='男' then 60 when sex='女' and station='领导' then 55 else 50 end ye
FROM @a
)aa
WHERE
DATEDIFF(YEAR,brithday,DATEADD(MONTH,3,GETDATE()))=ye
--result
/*
Deptname EmpCode EmpName Sex Brithday Station Adate Amonth
-------------------- -------------------- -------------------- -------------------- -------------------- -------------------- ------------------------------ -----------
人力资源部 19591008101 朱 女 1959-10-08 工人 2009-10-08 00:00:00.000 1
人力资源部 19490221001 马 男 1949-11-21 工人 2009-11-21 00:00:00.000 2
人力资源部 19500609001 宋 女 1954-12-09 领导 2009-12-09 00:00:00.000 3(所影响的行数为 3 行)*/
declare @t table(Deptname varchar(20), Empcode varchar(20),EmpName varchar(20),sex varchar(20),brithday datetime,station varchar(20))
Insert @t select
'人力资源部', '19591008101', '朱', '女', '1959-10-08', '工人' union all select
'人力资源部', '19490221001', '马', '男', '1949-11-21', '工人' union all select
'人力资源部', '19500609001', '宋', '女', '1954-12-09', '领导' union all select
'人力资源部', '19510522001', '周', '男', '1951-05-22', '工人' select a.*,Adate=(case when sex='男' then dateadd(year,60,brithday)
when sex='女' and station='工人' then dateadd(year,50,brithday)
when sex='女' and station='领导' then dateadd(year,55,brithday) end),
Amonth=(case when sex='男' then datediff(mm,getdate(),dateadd(year,60,brithday))
when sex='女' and station='工人' then datediff(mm,getdate(),dateadd(year,50,brithday))
when sex='女' and station='领导' then datediff(mm,getdate(),dateadd(year,55,brithday)) end)
from @t a where (case when sex='男' then datediff(mm,getdate(),dateadd(year,60,brithday))
when sex='女' and station='工人' then datediff(mm,getdate(),dateadd(year,50,brithday))
when sex='女' and station='领导' then datediff(mm,getdate(),dateadd(year,55,brithday)) end)<=3
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([Deptname] varchar(10),[Empcode] bigint,[EmpName] varchar(2),[sex] varchar(2),[brithday] datetime,[station] varchar(4))
insert [tb]
select '人力资源部',19591008101,'朱','女','1959-10-08','工人' union all
select '人力资源部',19490221001,'马','男','1949-11-21','工人' union all
select '人力资源部',19500609001,'宋','女','1954-12-09','领导' union all
select '人力资源部',19510522001,'周','男','1951-05-22','工人' select * from
(
select *, ADate=case when [sex]='男' then dateadd(year,60,[brithday])
when [sex]='女' and [station]='领导' then dateadd(year,55,[brithday])
when [sex]='女' and [station]='工人' then dateadd(year,50,[brithday]) end,
AMonth=case when [sex]='男' then datediff(month,getdate(),dateadd(year,60,[brithday]))
when [sex]='女' and [station]='领导' then datediff(month,getdate(),dateadd(year,55,[brithday]))
when [sex]='女' and [station]='工人' then datediff(month,getdate(),dateadd(year,50,[brithday])) end
from [tb] ) tmp
where AMonth<=3
/*
Deptname Empcode EmpName sex brithday station ADate AMonth
---------- -------------------- ------- ---- ----------------------- ------- ----------------------- -----------
人力资源部 19591008101 朱 女 1959-10-08 00:00:00.000 工人 2009-10-08 00:00:00.000 1
人力资源部 19490221001 马 男 1949-11-21 00:00:00.000 工人 2009-11-21 00:00:00.000 2
人力资源部 19500609001 宋 女 1954-12-09 00:00:00.000 领导 2009-12-09 00:00:00.000 3(3 行受影响)
*/
select '19591008101', '朱', '女' ,'1959-10-08','人力资源部','工人'
union
select '19490221001', '马', '男', '1949-11-21','人力资源部','工人'
union
select '19500609001', '宋', '女' ,'1954-12-09','人力资源部','领导'
union
select '19510522001', '周', '男', '1951-05-22','人力资源部','工人'
-- select deptname,empcode,empname,sex,brithday,station from @Employees
-- where (DATEDIFF(yy,Brithday,getdate())>=60 and Sex='男') or (DATEDIFF(yy,Brithday,getdate())>=55 and Sex='女' and station='领导') or (DATEDIFF(yy,Brithday,getdate())>=50 and Sex='女' and station='工人')
-- select deptname,empcode,empname,sex,brithday,station,
case when sex='男' then dateadd(yy,60,Brithday) else (case when station='领导' then dateadd(yy,55,Brithday) else dateadd(yy,50,Brithday) end ) end as ADate,
datediff(mm,getdate(),case when sex='男' then dateadd(yy,60,Brithday) else (case when station='领导' then dateadd(yy,55,Brithday) else dateadd(yy,50,Brithday) end ) end) as AMonth
from @Employees
where (dateadd(yy,60,Brithday) between getdate() and dateadd(mm,3,getdate()) and Sex='男')
or (dateadd(yy,55,Brithday) between getdate() and dateadd(mm,3,getdate()) and Sex='女' and station='领导')
or (dateadd(yy,50,Brithday) between getdate() and dateadd(mm,3,getdate()) and Sex='女' and station='工人')
union
select * from employee where sex='女' and Station='领导' and
datediff(mm,DATEADD(YEAR,60,brithday),getdate())<=3
union
select * from employee where sex='女' and Station<>'领导' and
datediff(mm,DATEADD(YEAR,60,brithday),getdate())<=3
这比case好理解,而且效率也不慢的
select * from tb
where datediff(day,brithday,getdate())/365>=( case when sex='男' then 60 when sex='女' and station='领导' then 55 when sex='女' and station='工人' then 50 end)已经测试。
EmpCode VARCHAR(12),
EmpName VARCHAR(12),
Sex CHAR(2),
Brithday DATETIME,
DeptName VARCHAR(20),
Station VARCHAR(12) )INSERT INTO #DBTemp(EmpCode,EmpName,Sex,Brithday,DeptName,Station)
SELECT '19591008101', '朱丽叶','女', '1959-10-08', '人力资源部', '工人' UNION ALL
SELECT '19490221001', '马正雄','男', '1949-11-21', '人力资源部', '工人' UNION ALL
SELECT '19491214001', '罗国雄','男', '1949-12-14', '人力资源部', '工人' UNION ALL
SELECT '19491215001', '王麻子','男', '1949-12-15', '人力资源部', '工人' UNION ALL
SELECT '19500609001', '宋娟','女', '1954-12-09', '人力资源部', '领导' UNION ALL
SELECT '19510522001', '周迅','男', '1951-05-22', '人力资源部', '工人' UNION ALL
SELECT '19531109001', '李丽娟','女', '1953-11-09', '人力资源部', '领导'SELECT EmpCode,EmpName,Sex,Brithday,Station FROM #DBTemp;SELECT EmpCode,EmpName,Sex,Brithday,Station FROM #DBTemp
WHERE ( Brithday>=DATEADD(YEAR,-60,GETDATE()) AND Brithday<=DATEADD(MONTH,3,DATEADD(YEAR,-60,GETDATE())) AND Sex='男')
OR ( Sex='女' AND ( Brithday>=DATEADD(YEAR,-55,GETDATE()) AND Brithday<=DATEADD(MONTH,3,DATEADD(YEAR,-55,GETDATE())) AND Station='领导' ) )
OR ( Sex='女' AND ( Brithday>=DATEADD(YEAR,-50,GETDATE()) AND Brithday<=DATEADD(MONTH,3,DATEADD(YEAR,-50,GETDATE())) AND Station='工人' ) );DROP TABLE #DBTemp;
EmpCode VARCHAR(12),
EmpName VARCHAR(12),
Sex CHAR(2),
Brithday DATETIME,
DeptName VARCHAR(20),
Station VARCHAR(12) )INSERT INTO #DBTemp(EmpCode,EmpName,Sex,Brithday,DeptName,Station)
SELECT '19591008101', '朱丽叶','女', '1959-10-08', '人力资源部', '工人' UNION ALL
SELECT '19490221001', '马正雄','男', '1949-11-21', '人力资源部', '工人' UNION ALL
SELECT '19491214001', '罗国雄','男', '1949-12-14', '人力资源部', '工人' UNION ALL
SELECT '19491215001', '王麻子','男', '1949-12-15', '人力资源部', '工人' UNION ALL
SELECT '19500609001', '宋娟','女', '1954-12-09', '人力资源部', '领导' UNION ALL
SELECT '19510522001', '周迅','男', '1951-05-22', '人力资源部', '工人' UNION ALL
SELECT '19531109001', '李丽娟','女', '1953-11-09', '人力资源部', '领导'SELECT EmpCode,EmpName,Sex,Brithday,Station FROM #DBTemp;SELECT EmpCode,EmpName,Sex,Brithday,Station FROM #DBTemp
WHERE ( Brithday>=DATEADD(YEAR,-60,GETDATE()) AND Brithday<=DATEADD(MONTH,3,DATEADD(YEAR,-60,GETDATE())) AND Sex='男')
OR ( Sex='女' AND ( Brithday>=DATEADD(YEAR,-55,GETDATE()) AND Brithday<=DATEADD(MONTH,3,DATEADD(YEAR,-55,GETDATE())) AND Station='领导' ) )
OR ( Sex='女' AND ( Brithday>=DATEADD(YEAR,-50,GETDATE()) AND Brithday<=DATEADD(MONTH,3,DATEADD(YEAR,-50,GETDATE())) AND Station='工人' ) );DROP TABLE #DBTemp;