多个6个月周期的平均值

Question

我需要创建一个以六个月为间隔的平均值，直到数据库的开始时间。这一周我一直在想，我想答案会是类似的。

SELECT AVG(q1.Entry1) as AvgPerWeek
FROM (SELECT COUNT(TestID) as Entry1, DATEPART(wk, TestDate) as Wk
FROM Table1
GROUP BY Year(TestDate), DATEPART(wk, TestDate))q1

我的第二个选择是尝试通过SSRS来做到这一点，如果有人对此有任何想法的话。

Answer 1

如果没有可用的功能，就创造一个!我会用CASE:

SELECT AVG(q1.Entry1) as AvgPerWeek
FROM (SELECT COUNT(TestID) as Entry1, Year(TestDate),
      CASE WHEN DATEPART(month, TestDate) between 1 AND 6 
       THEN 'first half' ELSE 'second half' END as term
      FROM Table1
      GROUP BY Year(TestDate), 
               CASE WHEN DATEPART(month, TestDate) between 1 AND 6 
                    THEN 'first half' ELSE 'second half' 
               END
     ) q1