我有一个简单的SQL查询.我正在try 将数据按一年中的星期进行分组,从周一到周日.DATEPART函数似乎很容易做到这一点.但我遇到了一个问题.我得到了重叠的年份日期.
例如,01/01/23在技术上是2023年,但它是2022年最后一周的一部分.当天的数字包含在返回的表中.
这是我所说的一个例子.
SELECT DATEPART(WEEK, responsedate) AS week_number,
CAST(MIN(responsedate) AS DATE) AS from_date,
CAST(MAX(responsedate) AS DATE) AS to_date,
(COUNT(CASE WHEN ov_rating >= 8 THEN 1 END) * 100.0 / NULLIF(COUNT(ov_rating), 0)) AS overall_rating,
(COUNT(CASE WHEN recommend >= 9 THEN 1 END) * 100.0 / NULLIF(COUNT(recommend), 0)) -
(COUNT(CASE WHEN recommend BETWEEN 1 AND 6 THEN 1 END) * 100.0 / NULLIF(COUNT(recommend), 0)) AS recommend_score
FROM survey
WHERE EXTRACT(YEAR FROM responsedate) = EXTRACT(YEAR FROM GETDATE())
AND responsedate <= GETDATE()
GROUP BY DATEPART(WEEK, responsedate)
ORDER BY from_date