We help IT Professionals succeed at work.

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

baberamin
baberamin asked
on
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
Comment
Watch Question

View Solution Only

Bill PrewTest your restores, not your backups...
Expert of the Year 2019
Top Expert 2016

Commented:
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 DBA
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
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 PrewTest your restores, not your backups...
Expert of the Year 2019
Top Expert 2016

Commented:
Thanks Scott.

»bp
baberamin

Author

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.
Senior DBA
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
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)
baberamin

Author

Commented:
Perfect.
Thank very much.
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
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"