T-Sql Find the Employee Count as average by the number of months in the year

Vote count: 0

I have table called "Employee"

Employee table has below columns

Id (identity)
EmploymentStartDate (datetime),
EmploymentEndDate (nullable datetime),

My query:

DECLARE @FromYear int = 2010, @ToYear int = 2017;

WITH YEARS AS 
(
    SELECT @FromYear As TheYear
    UNION ALL
    SELECT TheYear + 1
    FROM YEARS
    WHERE TheYear < @ToYear
)

SELECT
    Y.TheYear,
    SUM
    (
        CASE WHEN
            YEAR(EmploymentStartDate) <= Y.TheYear
            AND (EmploymentEndDate IS NULL OR YEAR(EmploymentEndDate) >= Y.TheYear)
        THEN 1
        ELSE 0
        END
    ) WorkingEmployeeCount,
    SUM
    (
        CASE WHEN
            YEAR(EmploymentStartDate) = Y.TheYear
        THEN 1
        ELSE 0
        END
    ) StartedEmployeeCount,
    SUM
    (
        CASE WHEN
            YEAR(EmploymentEndDate) = Y.TheYear
        THEN 1
        ELSE 0
        END
    ) SeperatedEmployeeCount
FROM
    YEARS Y
    CROSS JOIN
    Employees E
GROUP BY
    Y.TheYear

If ı run above query i get below result

TheYear - WorkingEmployeeCount - StartedEmployeeCount - SeperatedEmployeeCount

2010    -  1                   -  1                   -   0
2011    -  2                   -  1                   -   0
2012    -  2                   -  0                   -   0
2013    -  2                   -  0                   -   0
2014    -  2                   -  0                   -   0
2015    -  4                   -  2                   -   1
2016    -  3                   -  0                   -   0
2017    -  6                   -  3                   -   2

Question:

I need to use below formula.First i want to find every year's months count than AVG of EmployeeCount per year.

EmployeeCount / MonthsCountPerYear than get AVG

If i try below query it is not working for me (I can not create a solution)

(AVG(EmployeeCount / (CASE WHEN TheYear = DATE(GETUTCDATE) THAN 2 ELSE 12 END))) AS AvgEmployeeCount

What i want should be as below

TheYear - WorkingEmployeeCount - StartedEmployeeCount -SeperatedEmployeeCount - AvgEmployeeCount

2010    -  1                   -  1                   -   0                      - 1,30
2011    -  2                   -  1                   -   0                      - 1,20
2012    -  2                   -  0                   -   0                      - 1,00
2013    -  2                   -  0                   -   0                      - 3,50
2014    -  2                   -  0                   -   0                      - 5,33
2015    -  4                   -  2                   -   1                      - 7-33
2016    -  3                   -  0                   -   0                      - 9-34
2017    -  6                   -  3                   -   2                      - 1,15

How can i find employee count avg for every year according to months in a year.Any help will be appreciated.Thank you

asked 30 secs ago

S.S

Let's block ads! (Why?)

T-Sql Find the Employee Count as average by the number of months in the year