Requirements: the existing two tables respectively for table and leave table, respectively, the number of hiring and termination of each month, net, is hiring people minus departure

Problem: not every month has recruit and leave, not simple subtraction, also could be a month without hiring also does not have the number of departure, this month's value should be zero
Whether to need to use a stored procedure, how to write? There are more simple method?

SQL:
Select count (distinct a.f personid) the number of new TLEVEL2, entry_year, entry_month
The from RECRUITMENT a
Where TLEVEL2='pharmaceutical division and entry_year=2017
Group by entry_year entry_month, TLEVEL2
The order by entry_year, entry_month
;
Recruitment
-- -- -- -- --
Select
Count (distinct a.f personID) the number of departure, level2 stores, a. EAVEDATE_YEAR, a. EAVEDATE_MONTH
The from DIMISSION a
Where level2 stores='pharmaceutical division and LEAVEDATE_YEAR=2017
Group by level2 stores, a. EAVEDATE_YEAR, a. EAVEDATE_MONTH
The order by LEAVEDATE_MONTH

-- -- -- -- --
leaving

CodePudding user response:

Feel it is ok to get a view

CodePudding user response:

If the number of departure that month than hiring? Net how to calculate?

CodePudding user response:

refer to the second floor qq646748739 response:

if that month turnover number greater than hiring? Net how to calculate?

Net is a negative number

CodePudding user response:

I want to the way blind, no concrete implementation

Can be respectively on the two tables to create a trigger, when two tables new input data after insert on new_table or update data, the trigger is to insert a new table inside a 1, then you directly calculated in the new table