June 6, 2019 at 1:50 am
hiya amazing people
I need some help with the query below.
I need to see the breakdown of entries by month but the [RecvdDate] has a datatype of (varchar,null) & the date appears something like 2019-05-31 for example. What should I add in my SELECT statement to get the monthly breakdown?
SELECT COUNT(ID) AS 'Total Entries - 2019'
FROM [Log]
WHERE [RecvdDate] BETWEEN '2019-01-01' AND '2019-05-31'
AND LogType = 'Tech' AND (Category = 'Lappy Fix' OR Category = 'Phone Fix')
I want to see reults like below in SQL.
Total Entries MONTH
100 January 2019
200 February 2019
300 March 2019
I cannot change any data type or column as its read only & other jobs running based on this table can get affected. Can someone please help me with this?
Thanks in advance for your help.
June 6, 2019 at 4:10 am
Maybe this will help (?)
use tempdb;
go
declare @SomeDate DATE = '2019-05-31';
SELECT @SomeDate AS TextDate,
DATEADD(day,1,CAST(@SomeDate AS DATE)) AS NextDay,
YEAR(CAST(@SomeDate AS DATE)) AS SomeYear,
CONCAT(CONCAT(DATENAME(month,CAST(@SomeDate AS DATE)),' '), YEAR(CAST(@SomeDate AS DATE))) AS MonthYear
MonthYear returns 'May 2019', so you could calculate that in the inner query and then do the COUNT() in an outer query.
June 6, 2019 at 10:16 am
Please don't double post.
Further responses are available on https://www.sqlservercentral.com/forums/topic/count-of-entries-grouped-by-month-extracted-from-a-date
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply