alter procedure p_siey_proj_human_inout
@starttime datetime,
@endtime datetime,
@name varchar(100),
@projectname varchar(100)
as
create table #tmp
(
humanid varchar(36),
--humanname varchar(100),
proj_guid varchar(36),
atype int,
posiname varchar(100),
indate datetime,
outdate datetime,
ProjectLong int
)
insert into #tmp(atype,humanid,proj_guid,posiname,indate,outdate)
select 1,t1.HumanId,t1.EpsProjId,t1.PosiName,t1.ApprDate,t2.Actual_End_Date from(select x.Status,x.projectcode,x.EpsProjId,x.ApprDate,y.PosiName,z.HumanId,z.HumanName from PS_MK_ProjTeamEstablish x
join PS_MK_ProjTeamEstablish_Posi y on x.Id=y.MasterId
join PS_MK_ProjTeamEstablish_Human z on x.Id=y.MasterId where x.Status='50') t1 join SY_Technologyclosure t2 on t1.EpsProjId=t2.EpsProjId
--select x.EpsProjName,x.ApprDate,y.PosiName,z.HumanId,z.HumanName from PS_MK_ProjTeamEstablish x
--join PS_MK_ProjTeamEstablish_Posi y on x.Id=y.MasterId
--join PS_MK_ProjTeamEstablish_Human z on x.Id=y.MasterId where x.Status='50'
--select * from PS_CL_ProjectCloseApply
--select * from PB_PersonnelAllocation r join PB_PersonnelAllocationList d on r.Id=d.PersonnelAllocationId
---人员调拨
insert into #tmp(atype,humanid,indate,proj_guid)
select 2, d.HumId, d.TransferDate as indate, d.ToProjectId as proj_guid from PB_PersonnelAllocation r
join PB_PersonnelAllocationList d
on r.Id=d.PersonnelAllocationId
join PB_DefaultField e on r.id=e.DefaultFieldId
where r.AllocationType=0 or r.AllocationType = 20
--insert into #tmp(atype,humanid,outdate,proj_guid)
--select 2,d.HumId,d.TransferDate as outdate, d.ToProjectId as proj_guid from PB_PersonnelAllocation r
--join PB_PersonnelAllocationList d
--on r.Id=d.PersonnelAllocationId
--join PB_DefaultField e on r.id=e.DefaultFieldId
--where r.AllocationType=35
update #tmp set outdate = (select d.TransferDate as outdate from PB_PersonnelAllocation r
join PB_PersonnelAllocationList d
on r.Id=d.PersonnelAllocationId
join PB_DefaultField e on r.id=e.DefaultFieldId
where r.AllocationType=35 and #tmp.humanid=d.HumId and #tmp.atype=2 ) where #tmp.atype=2
update #tmp set ProjectLong = DATEDIFF(DD,indate,outdate) where outdate is not null and indate is not null
select x1.*, x2.name as humanname,x2.Code as humancode, x3.project_name
from #tmp x1
left outer join PB_Human x2 on x1.humanid=x2.id
left outer join pln_project x3 on x1.proj_guid= x3.project_guid where x2.Name like @name and x3.project_name like @projectname and x1.indate BETWEEN CONVERT(datetime,@starttime,120) AND CONVERT(datetime,@endtime,120)
drop table #tmp
go
--exec aa '123';
sql存储过程案例
最新推荐文章于 2023-03-02 14:57:15 发布