## mardi 14 février 2017

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

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