Merge date columns, display month as MMM and sort date by DESC

Hi,

I have a query in SQL Server 2014

select month((CDate)) as CreateMonth, year((CDate)) as CreateYear, count(*) "No of Users"

from kf, dt

where kf.type=0 and kf.ID=dt.ID

group by month(CDate), year(CDate)
order by year(CDate) desc, month(CDate) desc


It display results as below:

CreateMonth      CreateYear      No of Users
-------------------        ----------------     -----------------
10                              2018              7
9                              2018             9
8                              2018             6
7                              2018             3


Now I want to have results as below:

CreateMonthYear      No of Users
--------------------------     -----------------
Oct 2018                   6
Sep 2018                  16
Aug 2018                  3
July 2018                  20


So

1. Converting Month from numeric to three character Alphabets
2. Merging year together with month
3. Date column should be sorted DESC
baberaminAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill PrewIT / Software Engineering ConsultantCommented:
Give this a try:

select LEFT(DATENAME(MONTH, CDate), 3) + ' ' + YEAR(CDate) AS CreateMonthYear
count(*) "No of Users"
from kf, dt
where kf.type=0 
and kf.ID=dt.ID
group by LEFT(DATENAME(MONTH, CDate), 3) + ' ' + YEAR(CDate)
order by CDate desc;

Open in new window

»bp
Scott PletcherSenior DBACommented:
PLEASE NO POINTS FOR JUST THIS.

In Bill's, code, you'll need to CAST the YEAR() as varchar so it doesn't cause an addition error:

select LEFT(DATENAME(MONTH, CDate), 3) + ' ' + CAST(YEAR(CDate) AS varchar(4)) AS CreateMonthYear
Bill PrewIT / Software Engineering ConsultantCommented:
Thanks Scott.

»bp
Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.

Learn More!
baberaminAuthor Commented:
Hi,

It's not working. Also another factor to keep in mind that if you convert date to Characters then sorting will be alphabetically not date vise.


Column 'dt.CDate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Column 'dt.CDate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Scott PletcherSenior DBACommented:
SELECT STUFF(CONVERT(varchar(12), DATEADD(MONTH, DATEDIFF(MONTH, 0, dt.CDate), 0), 107),
    4, 4, '') AS CreateMonth, COUNT(*) AS [No of Users]
FROM kf, dt
WHERE kf.type=0 AND kf.ID=dt.ID
GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, dt.CDate), 0)
ORDER BY DATEADD(MONTH, DATEDIFF(MONTH, 0, dt.CDate), 0)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
baberaminAuthor Commented:
Perfect.
Thank very much.
Mark WillsTopic Advisor, Page EditorCommented:
Since you are on 2014, you can also try :

select format(DT.CreateDate,'MMM yyyy') as [CreateMonthYear], COUNT(*) as [No of Users]
from KF
CROSS APPLY (select datefromparts(year(cdate),month(cdate),01) from DT where kf.id=dt.id) DT(CreateDate)
where kf.type = 0
group by DT.CreateDate
order by DT.CreateDate desc

Open in new window


Make use of format() and datefromparts() introduced in sql2012

cross apply is like an inner join.

where as KF,DT is basically a cross join, until   kf.id=dt.id  making it essentially an inner join.

can also use eomonth()  e.g. instead of datefromparts(year(cdate),month(cdate),01)   you could use eomonth(cdate)   -- depends on need for start of month, or end of month (day is ignored in your requirement anyway).

Also noted requirement 3 : "3. Date column should be sorted DESC"
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.