How to group by month of a date using a function in a select statement

Question

I am having problems grouping by the month of a date when using a function. It was working before but the query was less complicated as I am now using a function that uses a rolling year from the current month. Here is my code.

SELECT
    CASE 
WHEN DATEDIFF(mm,dbo.fn_firstofmonth(getdate()), dbo.fn_firstofmonth(D.expected_date)) < 12
THEN DATEDIFF(mm,dbo.fn_firstofmonth(getdate()), dbo.fn_firstofmonth(D.expected_date)) + 1
ELSE 13 END AS [Expected Month],
    P.probability AS [Category], COUNT(O.id) AS [Customers]
FROM opportunity_probability P
INNER JOIN opportunity_detail D ON D.probability_id = P.id
INNER JOIN opportunities O ON D.opportunity_id = O.id
INNER JOIN
(
    SELECT opportunity_id
    FROM opportunity_detail
    GROUP BY opportunity_id
) T ON T.opportunity_id = O.customer_id
GROUP BY P.probability, MONTH(D.expected_date)
ORDER BY  P.probability, MONTH(D.expected_date)

It works if I have D.expected_date in the GROUP BY but I need to group on the MONTH of this date as it does not bring through the data correctly.

I would consider this query to be too long and complex to simply paste in a question and expect a meaningful answer without showing some sample input/output and possibly a table schema. — Tim Biegeleisen, Sep 10 '15 at 09:59
why r u getting MaxDate from your sub-query even not using it anywhere.... — Zafar Malik, Sep 10 '15 at 10:13
Thank you Zafar for pointing that out, I was using it for something else but have removed it now so have deleted MAX(date_added) — Tom McDonough, Sep 10 '15 at 10:17

score 2 · Answer 1 · answered Sep 10 '15 at 10:12

2

You can try to find month by this code:

GROUP BY P.probability, DATEPART(month, D.expected_date)

answered Sep 10 '15 at 10:12

Chintan7027

7,115
8
36
50

I get the following error Column 'opportunity_detail.expected_date' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. – Tom McDonough Sep 10 '15 at 10:17
expected_date is in which table? – Chintan7027 Sep 10 '15 at 10:20
expected_date is from the opportunity_detail table which I have aliased as D – Tom McDonough Sep 10 '15 at 10:21

score 2 · Accepted Answer · edited Feb 02 '19 at 09:23

2

You could always remove the group by, then put your entire select into another select, and than group by the outer select:

select t.A, t.B from (select A, datepart(month, b) as B) t group by t.A, t.B

This way you can address your month field as if it where a normal field.

Example is far from complete, but should get you on your way.

edited Feb 02 '19 at 09:23

halfer

19,824
17
99
186

answered Sep 10 '15 at 13:33

GuidoG

11,359
6
44
79

Thanks I will give this a go – Tom McDonough Sep 11 '15 at 14:10

Rohit Gaikwad · Answer 3 · 2015-09-10T11:17:39.053

1

try this

SELECT
to_char(D.expected_date, 'YYYY-MM'),
    CASE 
WHEN DATEDIFF(mm,dbo.fn_firstofmonth(getdate()), dbo.fn_firstofmonth(D.expected_date)) < 12
THEN DATEDIFF(mm,dbo.fn_firstofmonth(getdate()), dbo.fn_firstofmonth(D.expected_date)) + 1
ELSE 13 END AS [Expected Month],
    P.probability AS [Category], COUNT(O.id) AS [Customers]
FROM opportunity_probability P
INNER JOIN opportunity_detail D ON D.probability_id = P.id
INNER JOIN opportunities O ON D.opportunity_id = O.id
INNER JOIN
(
    SELECT opportunity_id
    FROM opportunity_detail
    GROUP BY opportunity_id
) T ON T.opportunity_id = O.customer_id
GROUP BY P.probability, to_char(D.expected_date, 'YYYY-MM')
ORDER BY  P.probability, to_char(D.expected_date, 'YYYY-MM')

edited Sep 10 '15 at 11:17

answered Sep 10 '15 at 10:49

Rohit Gaikwad

817
2
8
24

Thank you for taking the time but an error appears, Invalid column name 'exp_date'. This is due to the GROUP BY running before the SELECT statement and when I add MONTH(D.expected_date) it still needs DATEDIFF(mm,dbo.fn_firstofmonth(getdate()) to be added to the GROUP By in order for the query to function – Tom McDonough Sep 10 '15 at 11:11
I am assuming I could probably put the CASE statement in a sub query and do it that way – Tom McDonough Sep 10 '15 at 11:12
@TomMcDonough i have changed my query try it once again – Rohit Gaikwad Sep 10 '15 at 11:18
Thanks, I am using sql server so I cannot use this function but even when I use Convert(Varchar(25),D.expected_date) it says the same error as before – Tom McDonough Sep 10 '15 at 12:07

How to group by month of a date using a function in a select statement

3 Answers3