职位变迁明细报表
--存储过程
CREATE procedure m_Get_FEmplIDs
@FEmplType varchar(50),@BeginDate datetime ,@EndDate datetime
as
--建临时表用来存放全部数据
CREATE TABLE [#Temp_Empls] (
[FID] [int] IDENTITY (1, 1) NOT NULL ,
[FDepaID] [int] NULL ,
[FEmplID] [int] NULL ,
[FEmplType] varchar(50) NULL ,
[FJobID] [int] NULL ,
[FBeginDate] [datetime] NULL ,
[FEndDate] [datetime] NULL
) ON [PRIMARY]
--将所有员工的现在部门ID，用户ID等信息放入到#Temp_Empls表中
insert into #Temp_Empls
Select e.FDepaID as FDepaID,e.FEmplID as FEmplID,e.FEmplType as FEmplType,e.FJobID as FJobID,e.FInDutyDate as FInDutyDate ,a.FOutDate as FOutDate
From Hr_Employee e
left outer Join Hr_Job j on e.FJobID=j.FJobID and j.FJobType like ''+@FEmplType+'%'
left outer join Hr_JobOutApply a on e.FEmplID=a.FEmplID and DateDiff(Day,e.FInDutyDate,a.FOutDate)>0
--游标中的变量
declare @FEmplID int,@FNDepaID int,@FJDate datetime,@FNJobID int,@FDepaID int,@FJobID int
--delfid用来存放人员异动以后要删除的ID，fbegindate用来存放开始时间
declare @DelFID int,@FBeginDate datetime,@EmplType varchar(50)
declare cur cursor for
select FEmplID,FNDepaID,FJDate,FNJobID,FDepaID,FJobID from Hr_JobChange
open cur
fetch next from cur into @FEmplID,@FNDepaID,@FJDate,@FNJobID,@FDepaID,@FJobID
while @@fetch_status<>-1
begin
--把这个员工最后一条记录找出来，并把开始时间拿出来，作为插入新记录中的开始时间
select top 1 @DelFID=FID,@FBeginDate=FBeginDate,@EmplType=FEmplType from #Temp_Empls where FEmplID=@FEmplID order by FID desc
--判断@DelFID是不是为空，测试发现Hr_JobChange中的FEmplID在员工表中并不一定存在
if isNull(len(@DelFID),0)<>0
begin
--根据异动情况，将原来的记录转成2条记录
insert into #Temp_Empls values (@FDepaID,@FEmplID,@EmplType,@FJobID,@FBeginDate,dateadd(d,-1,@FJDate))
insert into #Temp_Empls values (@FNDepaID,@FEmplID,@EmplType,@FNJobID,@FJDate,null)
delete from #Temp_Empls where FID=@DelFID
end
fetch next from cur into @FEmplID,@FNDepaID,@FJDate,@FNJobID,@FDepaID,@FJobID
end
close cur
deallocate cur
--将#Temp_Empls表中，结束日期不为空的而且他的离职日期晚于统计结束时间的，将结束日期改为统计结束日期!
update #Temp_Empls set FEndDate=@EndDate where FEndDate is not null and datediff(d,FEndDate,@EndDate)<0
--将#Temp_Empls表中,结束日期为NULL的，即还在职的，结束日期统一更新为统计结束日期
update #Temp_Empls set FEndDate=@EndDate where FEndDate is null
--将记录插入外部的临时表#Temp_FEmplIDs中
insert into #Temp_FEmplIDs
select FDepaID,FEmplID,FJobID,FEmplType,convert(varchar(10),FBeginDate,120) as FBeginDate,convert(varchar(10),FEndDate,120) as FEndDate from #Temp_Empls where
datediff(d,FEndDate,@EndDate)>=0 and datediff(d,FEndDate,@BeginDate)<=0 and datediff(d,FBeginDate,@EndDate)>=0
and FDepaID in(select FDepaID from #Temp_FDepaIDs)
--删除临时表
drop table #Temp_Empls
--查询更新：
参数传入FBeginDate,FDepaID,FEmplCode,FEmplName,FEndDate
--第一数据源
select '职位变迁明细报表' as [报表标题],
'日期:'+convert(char(10),cast('@FBeginDate' as datetime),20)
+'--'+convert(char(10),cast('@FEndDate' as datetime),20)+' 报表日期:'+convert(char
(10),GetDate(),20) as [编制日期]
--第二数据源
create table #Temp_FDepaIDs
(FDepaID int)
insert Into #Temp_FDepaIDs(FDepaID)
exec dbo.m_Get_AllMulFDepaIDs '@FDepaID'
--外部临时表结构
CREATE TABLE [#Temp_FEmplIDs] (
[FDepaID] [int] NULL ,
[FEmplID] [int] NULL ,
[FJobID] [int] NULL ,
[FEmplType] varchar(50) NULL ,
[FBeginDate] [datetime] NULL ,
[FEndDate] [datetime] NULL
) ON [PRIMARY]
exec dbo.m_Get_FEmplIDs '','@FBeginDate','@FEndDate'
select Hr_Employee.FEmplName as 员工姓名,Hr_Employee.FEmplCode as 员工号,Bd_Department.FDepaName as 部门名称,FJobName as 岗位名称,
FBeginDate as 开始日期,FEndDate as 结束日期 from #Temp_FEmplIDs
left outer join Bd_Department on Bd_Department.FDepaID=#Temp_FEmplIDs.FDepaID
left outer join Hr_JOb on Hr_JOb.FJobID=#Temp_FEmplIDs.FJobID
left outer join Hr_Employee on Hr_Employee.FEmplID=#Temp_FEmplIDs.FEmplID
where Hr_Employee.FEmplName like '%@FEmplName%' and Hr_Employee.FEmplCode like '%@FEmplCode%'
order by #Temp_FEmplIDs.FBeginDate
drop table #Temp_FEmplIDs
drop table #Temp_FDepaIDs
--第三个数据源，负责列循环
select col from
(
select '员工姓名' as col,'000' as sort
union select '员工号' as col, '002' as sort
union select '部门名称' as col ,'003' as sort
union select '岗位名称' as col ,'004' as sort
union select '开始日期' as col,'005' as sort
union select '结束日期' as col, '006' as sort
) a order by sort
