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'