Job change details report

-- stored procedure
Create procedure m_get_femplids
@ fempltype varchar (50), @ begindate datetime, @ enddate datetime
as
-- create a temporary table to store all data
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]

-- Add the current department ID and user ID of all employees to the # temp_empls table
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
-- Variable in the cursor
Declare @ femplid int, @ fndepaid int, @ fjdate datetime, @ fnjobid int, @ fdepaid int, @ fjobid int
-- Delfid is used to store the ID to be deleted after the personnel change, and fbegindate is used to store the start time.
Declare @ delfid int, @ fbegindate datetime, @ empltype varchar (50)
Declare cur cursor
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
-- Find the employee's last record and take out the start time as the start time for inserting the new record.
Select top 1 @ delfid = FID, @ fbegindate = fbegindate, @ empltype = fempltype from # temp_empls where femplid = @ femplid order by FID DESC
-- Judge whether @ delfid is null. The test shows that femplid in hr_jobchange does not necessarily exist in the employee table.
If isnull (LEN (@ delfid), 0) <> 0
Begin
-- Convert the original record into two records based on the change situation
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
-- In the # temp_empls table, if the end date is not blank and his departure date is later than the statistical end time, change the end date to the statistical end date!
Update # temp_empls set fenddate = @ enddate where fenddate is not null and datediff (D, fenddate, @ enddate) <0
-- In the # temp_empls table, if the end date is null, that is, the end date is still active, and the end date is updated to the statistical end date.
Update # temp_empls set fenddate = @ enddate where fenddate is null

-- Insert records into an external temporary table # 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)
-- Delete a temporary table
Drop table # temp_empls

-- Query updates:

Input fbegindate, fdepaid, femplcode, femplname, fenddate

-- Data source 1
Select 'job change details report' as [report title],
'Date: '+ convert (char (10), cast (' @ fbegindate' as datetime), 20)
+ '--' + Convert (char (10), cast ('@ fenddate' as datetime), 20) + 'report Date:' + convert (char
(10), getdate (), 20) as [compile date]

-- Second Data Source
Create Table # temp_fdepaids
(Fdepaid INT)
Insert into # temp_fdepaids (fdepaid)
Exec DBO. m_get_allmulfdepaids '@ fdepaid'

-- External temporary table structure
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 employee name, hr_employee.femplcode as employee number, bd_department.fdepaname as department name, fjobname as post name,
Fbegindate as start date, fenddate as end date 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

-- The third data source is responsible for column loop.
Select Col from
(
Select 'employee name' as COL, '000' as sort
Union select 'employee No. 'As Col, '002' As sort
Union select 'department name' as COL, '003 'As sort
Union select 'job name' as COL, '004 'As sort
Union select 'start date' as COL, '005 'As sort
Union select 'end date' as COL, '006 'As sort

) A order by sort