SQL 更新临时表 自定义查询格式 存储过程

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:  <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================

CREATE PROCEDURE EmloyeeAlterationReport
@year int ,
@month int
AS
BEGIN
  select department,0 ondutybegmon,0 ondutyendmon,0 moninduty,0 monoutduty,0 monmove,0 monintoonduty,employeeid monoutrate ,id into #1 from Qn_EmloyeeInfo where 1 = 2
 
  insert into #1(department,ondutybegmon,ondutyendmon,moninduty,monoutduty,monmove,monintoonduty,monoutrate)
  select department,0,0,0,0,0,0,0
  from Qn_EmloyeeInfo
  where year(createddate) = convert(int , '2011') and month(createddate) = 4 and isnull(department,'') <> '' 
  group by department

  --月初在职人数
  select department, count(*) reccount into #2 from Qn_EmloyeeInfo where year(createddate) <= convert(int , '2011') and month(createddate) < (6-1) and isnull(department,'') <> ''  group by department
  --月末在职人数
  select department, count(*) reccount into #3 from Qn_EmloyeeInfo where year(createddate) <= convert(int , '2011') and month(createddate) <= (6-1) and isnull(department,'') <> ''  group by department
  --本月入职人数
  select department, count(*) reccount into #4  from HR_ContractInfo_formal where year(staffDate) <= convert(int , '2011') and month(staffDate) = (6-1) and isnull(department,'') <> '' group by department
  --本月离职人数
  select department, count(*) reccount into #5 from HR_ContractInfo_formal where year(leaveDate) <= convert(int , '2011') and month(leaveDate) = (6-1) and isnull(department,'') <> '' and status in ('离职') group by department
  --本月异动人数
  select newdepartment department, count(*) reccount into #6 from HR_StaandPayChangeList where changeType In ('部门变动','岗位变动') and year(changeDate) <= convert(int , '2011') and month(changeDate) = (6-1) and isnull(newdepartment,'') <> '' group by newdepartment
  --本月转正人数
  select department, count(*) reccount into #7 from HR_ContractInfo where year(formalDate) <= convert(int , '2011') and month(formalDate) = (6-1) and isnull(department,'') <> '' and status in ('转正') and isnull(department,'') <> '' group by department
  
  --更新月初在职人数
  update #1
  set ondutybegmon = #2.reccount
  from #2
  where #1.department = #2.department
  --更新月末在职人数
  update #1
  set ondutyendmon = #3.reccount
  from #3
  where #1.department = #3.department

  --更新本月入职人数
  update #1
  set moninduty = #4.reccount
  from #4
  where #1.department = #4.department

  --更新本月离职人数
  update #1
  set monoutduty = #5.reccount
  from #5
  where #1.department = #5.department

  --更新本月异动人数
  update #1
  set monmove = #6.reccount
  from #6
  where #1.department = #6.department

  --更新本月转正人数
  update #1
  set monintoonduty = #7.reccount
  from #7
  where #1.department = #7.department

  --查询报表
  select * from #1

END
GO