asked on

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

Bill Prew

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 Pletcher

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 Prew

Thanks Scott.

»bp

Baber Amin

ASKER

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.

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Baber Amin

ASKER

Perfect.
Thank very much.

Mark Wills

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"