We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.
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
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
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
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.
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
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
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.
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.
SELECT STUFF(CONVERT(varchar(12),
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)
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
Since you are on 2014, you can also try :
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),
Also noted requirement 3 : "3. Date column should be sorted DESC"
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
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),
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.
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
Open in new window
»bp