create table Employees(员工编号,进厂时间,状态)--状态0为离职,大于0在职
insert into Employees('001','2010-12-2',0)
insert into Employees('002','2011-12-12',1)
insert into Employees('003','2011-10-12',3)
create table EmployeesQuit(员工编号,离职时间)
insert into EmployeesQuit('001','2012-12-4')--想要的结果
员工编号 天数
001 27
002 12
insert into Employees('001','2010-12-2',0)
insert into Employees('002','2011-12-12',1)
insert into Employees('003','2011-10-12',3)
create table EmployeesQuit(员工编号,离职时间)
insert into EmployeesQuit('001','2012-12-4')--想要的结果
员工编号 天数
001 27
002 12
insert into Employees values('001','2010-12-2',0)
insert into Employees values('002','2011-12-12',1)
insert into Employees values('003','2011-10-12',3)
create table EmployeesQuit(员工编号 varchar(10),离职时间 datetime)
insert into EmployeesQuit values('001','2012-12-4')
go
select 员工编号,datepart(d,进厂时间) 天数 from Employees where 状态=1
union all
select 员工编号,datediff(d,离职时间,convert(varchar(8),dateadd(m,1,离职时间),120)+'01')-1 from employeesquit
/*
员工编号 天数
---------- -----------
002 12
001 27(2 行受影响)*/
go
drop table Employees,employeesquit
select 员工编号,datepart(d,进厂时间) 天数 from Employees where 状态=1 union all select 员工编号,datediff(d,离职时间,convert(varchar(8),dateadd(m,1,离职时间),120)+'01')-1 from employeesquit
select 员工编号,datepart(d,进厂时间) 天数 from Employees where 状态=1 union all select 员工编号,datediff(d,离职时间,convert(varchar(8),dateadd(m,1,离职时间),120)+'01')-1 from employeesquit