declare @emp table
(
    EMPLOYEE_NO VARCHAR(20)
    ,FULL_NAME VARCHAR(20)
    ,DEPARTMENT VARCHAR(20)
    ,PREV_DEPARTMENT VARCHAR(20)
    ,EMP_START_DATE DATE
    ,EMP_END_DATE DATE
    ,PERSON_TYPE VARCHAR(20)
    ,EFFECTIVE_START_DATE DATE
    ,EFFECTIVE_END_DATE DATE
    ,CURRENT_FLAG CHAR(1)
)

INSERT INTO @emp
SELECT 
    '182383' EMPLOYEE_NO
    , 'ABACUS' FULL_NAME
    , 'ADMIN' DEPARTMENT
    , 'ADMIN' PREV_DEPARTMENT
    , '04/01/2009' EMP_START_DATE
    , '23/11/2010' EMP_END_DATE
    ,'LOCAL' PERSON_TYPE
    , '04/01/2009' EFFECTIVE_START_DATE
    , CAST(NULL AS DATE) EFFECTIVE_END_DATE
    ,'Y' CURRENT_FLAG
 
 UNION
 SELECT 
    '182383' EMPLOYEE_NO
    , 'ABACUS' FULL_NAME
    , 'ADMIN' DEPARTMENT
    , 'ADMIN' PREV_DEPARTMENT
    , '12/05/2011' EMP_START_DATE
    , CAST(NULL AS DATE) EMP_END_DATE
    ,'LOCAL' PERSON_TYPE
    , '12/05/2011' EFFECTIVE_START_DATE
    , '26/12/2011' EFFECTIVE_END_DATE
    ,'N' CURRENT_FLAG
 
 UNION
 SELECT 
    '182383' EMPLOYEE_NO
    , 'ABACUS' FULL_NAME
    , 'FINANCE' DEPARTMENT
    , 'ADMIN' PREV_DEPARTMENT
    , '12/05/2011' EMP_START_DATE
    , CAST(NULL AS DATE) EMP_END_DATE
    ,'LOCAL' PERSON_TYPE
    , '27/12/2012' EFFECTIVE_START_DATE
    , CAST(NULL AS DATE) EFFECTIVE_END_DATE
    ,'Y' CURRENT_FLAG
 
SELECT * FROM @emp

• PREV_DEPARTMENT : Used lag to generate this, ABACUS movement within the organization


• EMP_START_DATE : Date ABACUS joined


• EMP_END_DATE: Date ABACUS left


• EFFECTIVE_START_DATE: SCD field, Date ABACUS moves from one department to another.


• EFFECTIVE_END_DATE: SCD field, Date ABACUS left the department from another. To keep history of movement.

MONTH   | DEPARTMENT |NEW_HIRES | RELOCATION (OUT) | RELOCATION (IN) | NET TOTAL PREV MONTH | NET TOTAL PRESENT MONTH |

• MONTH: REPORT MONTH


• DEPARTMENT: THE DEPARTMENTS IN THE ORGANIZATION


• NEW_HIRES: NUMBER OF NEW STAFFS HIRED THAT MONTH BASE ON EMP_START_DATE


• RELOCATION_OUT: NUMBER OF STAFFS MOVEMENT TO ANOTHER DEPARTMENT (EMP_END_DATE IS NULL, EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE ARE FILLED)


• RELOCATION_IN: NUMBER OF STAFFS MOVEMENT TO THIS DEPARTMENT (EMP_END_DATE IS NULL, EFFECTIVE_START_DATE IS FILLED AND EFFECTIVE_END_DATE IS NULL)


• NET TOTAL PREV MONTH: HEAD COUNT PREVIOUS MONTH


• NET TOTAL PRESENT MONTH: HEAD COUNT CURRENT MONTH

 cross join (select number from master..spt_values where type = 'p' and number between 0 and 11)