CODEWITHSUNDEEP
sql-servert-sql
Alex
Alex

Reputation: 143

T-SQL Get Records for this year grouped by month

I have a table of data which looks like this

ID        CreatedDate   
A123      2015-01-01  
B124      2016-01-02  
A125      2016-01-03  
A126      2016-01-04

What I would like to do is group by month (as text) for this year only. I have some up with the following query but it returns data from all years not just this one:

 Select Count(ID), DateName(month,createddate) from table
 Where (DatePart(year,createddate)=datepart(year,getdate())
 Group by DateName(month,createddate)

This returns

Count    CreatedDate
4        January

Instead of

Count    CreatedDate
3        January

Where have I gone wrong? I'm sure it's something to do with converting the date to month where it goes wrong

Upvotes: 1

Views: 46

Answers (2)

kostas
kostas

Reputation: 461

 select count(id) as Count,
 case when month(createddate)=1 THEN 'Januray' END as CreatedDate
 from [table]
 --where year(createddate)=2016 optional if you only want the 2016 count
 group by month(createddate),year(createdDate)

Upvotes: 0

gofr1
gofr1

Reputation: 15977

Just tested your code:

;WITH [table] AS (
SELECT *
FROM (VALUES
('A123',      '2015-01-01'),
('B124',      '2016-01-02'),
('A125',      '2016-01-03'),
('A126',      '2016-01-04')
) as t(ID, CreatedDate)
)

SELECT  COUNT(ID), 
        DATENAME(month,CreatedDate) 
FROM [table]
WHERE DATEPART(year,CreatedDate)=DATEPART(year,getdate())
GROUP BY DATENAME(month,CreatedDate)

Output was 

3   January

I removed ( near WHERE

Upvotes: 1

Related Questions