# Find the months in 2019 that had the highest and least number of new joinees respectively

Suppose I have an `employee` table with a column as `DateOfJoining`. And I have entered the data as

``````2019-12-4
2019-12-6
2019-12-5
2019-10-5
2010-08-17
``````

Now I want write the SQL query to find the month in which there is maximum number of joinees.

On

You can try below - using aggregation and TOP

``````select top 1 month(dateofjoining),count(*) as totaljoining
from tablename
where year(dateofjoining)=2019
group by month(dateofjoining)
order by 2 desc
``````
On

Using `Derived table` and `row_number()`

Below query will give you the month in which there is maxmium number of joinees for each year.

``````select cnt,mnth,yr
from
(select count(DateOfJoining)cnt,
month(DateOfJoining)mnth,
year(DateOfJoining)yr,
row_number()over(partition by year(DateOfJoining) order by count(DateOfJoining)desc)srno
from #employee
group by month(DateOfJoining),year(DateOfJoining)
)tbl
where srno = 1
``````

output

``````cnt         mnth        yr
----------- ----------- -----------
1           8           2010
3           12          2019
``````

and if you want specifically for 2019 then add the condition `yr ='2019'` in the where clause.

``````where srno = 1
and yr =2019
``````

output

``````cnt         mnth        yr
----------- ----------- -----------
3           12          2019
``````
On

You want both the biggest and least -- although I am guessing that you want at least one employee.

``````with e as (
select year(dateofjoining) as yyyy,
month(dateofjoining) as mm,
count(*) as totaljoining
from employee
where dateofjoining >= '2019-01-01' and
dateofjoining < '2020-01-01'
group by year(dateofjoining), month(dateofjoining)
)
select e.*
from ((select top (1) e.*
from e
order by totaljoining asc
) union all
(select top (1) e.*
from e
order by totaljoining desc
)
) e;
``````

Notes:

• The date comparison uses direct comparisons to dates rather than using functions. This is a best practice so the optimizer can use indexes.
• You want both the smallest and biggest value, so this uses a CTE so the `group by` is represented only once.
• This will not return months with no employees.
• If you want ties, then use `top (1) with ties`.