CODEWITHSUNDEEP
sql-serversql-server-2012group-by
Randika89
Randika89

Reputation: 185

Get the no of records in table group by year

Consider the following data set.

table name : TBL_EMPLOYEE

EMP_NUMBER      EMP_JOIN_DATE

001             2014-02-08
002             2014-03-15
003             2014-05-20
004             2015-05-24
005             2015-10-24
006             2017-01-30
007             2017-12-06

I need to get no. of joined employees in last 10 years as follows

required result

year    (no of joined employees count)

2018            0
2017            2
2016            0
2015            2
2014            3

I have tried bellow sql.

SELECT top 10 DATEPART(yyyy, EMP_JOIN_DATE) AS RESIGN_YEAR,COUNT(EMP_NUMBER)AS COUNTT
FROM TBL_EMPLOYEE
WHERE DATEPART(yyyy, EMP_JOIN_DATE) <= 2018 
GROUP BY DATEPART(yyyy, EMP_JOIN_DATE)
ORDER BY EMP_JOIN_DATE DESC

But it returns following result

year    (no of joined employees count)

2017            2
2015            2
2014            3

Upvotes: 1

Views: 80

Answers (4)

Jayasurya Satheesh
Jayasurya Satheesh

Reputation: 8033

try This

DECLARE @Strt INT=2007,@End INT = 2017
;WITH YR
AS
(
    SELECT
       MyYear = @Strt

    UNION ALL

    SELECT
       MyYear = MyYear+1
       FROM YR
          WHERE MyYear < @End

)
SELECT
    YR.MyYear,
    JoinCnt = COUNT(EMP.EMP_NUMBER)
    FROM YR
       LEFT JOIN Tbl_EmploYee EMP
          ON YEAR(EMP.EMP_JOIN_DATE) = YR.MyYear
       GROUP BY YR.MyYear

Upvotes: 0

Shiju Shaji
Shiju Shaji

Reputation: 1730

I have done it in another way.. Try this

SELECT @min_year = min(EMP_JOIN_DATE)
    ,@max_year = max(EMP_JOIN_DATE)
FROM @TBL_EMPLOYEE;

WITH CTE
AS (
    SELECT datepart(year, @min_year) AS yr

    UNION ALL

    SELECT yr + 1
    FROM CTE
    WHERE yr <= datepart(year, @max_year)
    )
SELECT TOP 10 yr AS RESIGN_YEAR
    ,COUNT(EMP_NUMBER) AS COUNTT
FROM CTE
LEFT JOIN @TBL_EMPLOYEE ON year(EMP_JOIN_DATE) = yr
WHERE yr <= 2018
GROUP BY yr
ORDER BY yr ASC

Upvotes: 1

sanatsathyan
sanatsathyan

Reputation: 1763

Try the following query : 

WITH yearlist AS 
(
    SELECT (DATEPART(Year,getdate())-10) as year
    UNION all
    SELECT yl.year + 1 as year
    FROM yearlist yl
    WHERE yl.year + 1 <= YEAR(GetDate())
)

SELECT Y.year ,ISNULL(A.[No of Employees],0) [No of Employees]
FROM yearlist Y
LEFT JOIN
 ( 
SELECT YEAR(EMP_JOIN_DATE) [Year],Count(YEAR(EMP_JOIN_DATE)) [No of Employees]
FROM TBL_EMPLOYEE
WHERE DATEDIFF(YEAR,EMP_JOIN_DATE,GETDATE()) < 10
GROUP BY YEAR(EMP_JOIN_DATE)
 ) A ON A.Year = Y.year
ORDER BY Y.year DESC;

Hope it works!!

Upvotes: 1

Eralper
Eralper

Reputation: 6612

@Scrat, you can use a numbers table or a SQL numbers table function as given in the referred document

Then you can use this table as the main table in FROM clause as follows

select * from dbo.NumbersTable(2008,2018,1)

enter image description here

Then you can simply LEFT JOIN your tables and development and join these two over years

;with cte as (
SELECT top 10 DATEPART(yyyy, EMP_JOIN_DATE) AS RESIGN_YEAR,COUNT(EMP_NUMBER)AS COUNTT
FROM TBL_EMPLOYEE
WHERE DATEPART(yyyy, EMP_JOIN_DATE) <= 2018 
GROUP BY DATEPART(yyyy, EMP_JOIN_DATE)
)
select * 
from dbo.NumbersTable(2008,2018,1) nt
left join cte on nt.i = cte.RESIGN_YEAR
ORDER BY EMP_JOIN_DATE DESC

Upvotes: 3

Related Questions