I have 2 SQL database Tables - Leavers Table and Headcount Table
I need to calculate attrition in an organization.
Now Attrition Formula - (No.of Leavers / Avg Headcount ) * 12 / (No.of Months)
Avg Headcount = Sum of the Headcount of every month / Total number of months
(eg : Jul-10 Leavers ,Aug-10 Leavers ,Sep-10.....Jan-10 Leavers ) = 70/7 . We follow fiscal Year and Our Fiscal Year starts from July.(7 is because Jan=7 if you start July as 1)
Question - 1 - In Numerator I need to sum all the months every month(cumulative sum). In denominator, I need to avg of all the months. How to write a query on this?
Question - 2 - I need to provide Attrition BY GRADE, BY TENURE ( it needs to be grouped by these fields) . How can we achieve this?
Appreciate a lot if someone could help me on this?
Attached excel sheet for reference.
Attrition Test Data.xlsx (34.29 KB)