T-SQL Calculate Turnover for Rolling 12 month from Staff_table and Leavers_Table

74 views Asked by At

Hi I have two table one is holding list of employee the other table 
   holding who is left the company.
I need to do Turnover Calculation as :
Average number of staff at the start and end of rolling 12 month from 
   date in tables divided by 
sum of number of employee left the company for each department by 
   grade and code. dont need to 
know the person id or identity just grade, role, organisation, date
For example :
Number of staff in month 1=55 
Number of Staff in month 12= 57
number of leavers within that 12 m period =8
Turnover= 8/((55+57)/2)
however I need to be able breaking it down for each department by 
   band and job code and role.
I hope this helps

here is something I have tried by not getting right figures.
Problem I have with script below is the denominator is giving me sum of staff breaking down by grades but number of leaves by band are not again that band for the month. 
Thanks for your help.

     WITH Staff AS
    (
    SELECT date,Cust_ID,Organisation,Jobrole,jobCode,Grade,
    CAST(COUNT(*)AS FLOAT) NoOfStaff
    FROM    staff_table GROUP BY         
    date,Cust_ID,Organisation,Jobrole,jobCode,Grade
    )
    ,
    Staffing 
    AS
    (
    SELECT  date,Cust_ID,Organisation,Jobrole,jobCode,Grade
     ,a.NoOfStaff CurrentMonth,b.NoOfStaff PreviousMonth

     FROM Staff a inner join Staff b 
    ON a.date = DATEADD(month,11,b.date) and a.jobRole = b.jobrole 
     and a.Grade = b.Grade and a.organisation=b.organisation a.job 
    code=b.jobcode
    ,
    Denominator as
    (
    SELECT  date,Cust_ID,Organisation,Jobrole,jobCode,Grade, 
    (CurrentMonth+PreviousMonth)/2          
    Metric_Denominator
    FROM Staffing 
    )   ,
    Numerator  
    AS
    (
     SELECT 
    date,Cust_ID,Organisation,Jobrole,jobCode,Grade
    SUM(TOTAL LEAVERS) OVER (PARTITION BY 
    JOBROLE,GRADE,ORGANISATION,JOB 
    CODE
    ORDER BY CensusDate ROWS BETWEEN 11 PRECEDING AND CURRENT ROW) 
    Metric_Numerator
    FROM LEAVERS_TABLE where 
    )

    SELECT 
    date,Cust_ID,Organisation,Jobrole,jobCode,Grade
    ,a.Metric_Denominator 
   Metric_Denominator,ISNULL(b.Metric_Numerator,0) 
    Metric_Numerator
    FROM    
    Denominator a LEFT JOIN Numerator b
    ON A.DATE=B.DATE a.Grade = b.Grade and 
    a.organisation=b.organisation 
    a.job code=b.jobcode
    WHERE A.date> ='2018 Jan 01'

   
example of excel data to show the staff list and leavers list hope it helps

0

There are 0 answers