如何使用select语句中的函数按日期月分组

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.

如果我有D，它是成立的。但是我需要在该组中的该月进行分组，因为它不能正确地传递数据。

3 个解决方案

#1

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

您可以始终通过以下方法删除组，然后将整个select放入另一个select中，并通过外部select将than组放入另一个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 adress your month field as if it where a normal field

这样你就可以把你的月场当作一个正常的场

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

例子还远没有完成，但应该能让你上路

#2

You can try to find month by this code:

您可以尝试通过以下代码查找月:

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

集团由P。概率,DATEPART(月,D.expected_date)

#3

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')

#1