从7月到6月按月统计记录

Question

我们使用日历月份7月到6月而不是1月到12月。我有一个按月计算每条记录的查询，并将上个月的记录添加到新的月份Jan to Fab。有没有办法从7月份开始，然后把7月份的计数加到8月份，把8月份加到10月份，依此类推……

下面是查询：

SELECT ReviewType,
       COUNT(CASE WHEN MONTH(dateCompleted) <= 1 THEN ReviewType END) AS Jan,
       COUNT(CASE WHEN MONTH(dateCompleted) <= 2 THEN ReviewType END) AS Feb,
       COUNT(CASE WHEN MONTH(dateCompleted) <= 3 THEN ReviewType END) AS Mar,
       COUNT(CASE WHEN MONTH(dateCompleted) <= 4 THEN ReviewType END) AS Apr,
       COUNT(CASE WHEN MONTH(dateCompleted) <= 5 THEN ReviewType END) AS May,
       COUNT(CASE WHEN MONTH(dateCompleted) <= 6 THEN ReviewType END) AS Jun,
       COUNT(CASE WHEN MONTH(dateCompleted) <= 7 THEN ReviewType END) AS Jul,
       COUNT(CASE WHEN MONTH(dateCompleted) <= 8 THEN ReviewType END) AS Aug,
       COUNT(CASE WHEN MONTH(dateCompleted) <= 9 THEN ReviewType END) AS Sep,
       COUNT(CASE WHEN MONTH(dateCompleted) <= 10 THEN ReviewType END) AS Oct,
       COUNT(CASE WHEN MONTH(dateCompleted) <= 11 THEN ReviewType END) AS Nov,
       COUNT(CASE WHEN MONTH(dateCompleted) <= 12 THEN ReviewType END) AS Dec,
       COUNT(ReviewType) AS Result
FROM ALAN.dbo.qryPeakReviews 
WHERE fiscalYear = 1819
GROUP BY ReviewType;

下面是输出：

ReviewType  Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Result
1           0   17  46  57  67  72  72  72  83  87  93  94  94
2           0   2   22  27  31  34  34  37  43  48  48  48  48
3           0   1   16  28  34  37  37  39  45  47  49  49  49

Answer 1

将6与所有月份相加，得到结果除以12的余数：

SELECT ReviewType,
       COUNT(CASE WHEN (MONTH(dateCompleted) + 6) % 12 = 1 THEN ReviewType END) AS Jul,
       COUNT(CASE WHEN (MONTH(dateCompleted) + 6) % 12 BETWEEN 1 AND 2 THEN ReviewType END) AS Aug,
       COUNT(CASE WHEN (MONTH(dateCompleted) + 6) % 12 BETWEEN 1 AND 3 THEN ReviewType END) AS Sep,
       COUNT(CASE WHEN (MONTH(dateCompleted) + 6) % 12 BETWEEN 1 AND 4 THEN ReviewType END) AS Oct,
       COUNT(CASE WHEN (MONTH(dateCompleted) + 6) % 12 BETWEEN 1 AND 5 THEN ReviewType END) AS Nov,
       COUNT(CASE WHEN (MONTH(dateCompleted) + 6) % 12 BETWEEN 1 AND 6 THEN ReviewType END) AS Dec,
       COUNT(CASE WHEN (MONTH(dateCompleted) + 6) % 12 BETWEEN 1 AND 7 THEN ReviewType END) AS Jan,
       COUNT(CASE WHEN (MONTH(dateCompleted) + 6) % 12 BETWEEN 1 AND 8 THEN ReviewType END) AS Feb,
       COUNT(CASE WHEN (MONTH(dateCompleted) + 6) % 12 BETWEEN 1 AND 9 THEN ReviewType END) AS Mar,
       COUNT(CASE WHEN (MONTH(dateCompleted) + 6) % 12 BETWEEN 1 AND 10 THEN ReviewType END) AS Apr,
       COUNT(CASE WHEN (MONTH(dateCompleted) + 6) % 12 BETWEEN 1 AND 11 THEN ReviewType END) AS May,
       COUNT(ReviewType) AS Jun,
       COUNT(ReviewType) AS Result
FROM ALAN.dbo.qryPeakReviews 
WHERE fiscalYear = 1819
GROUP BY ReviewType;

我用BETWEEN 1 AND ...更改了不平等检查，因为6月是按0计算的，不应该包括在内。

此外，也没有理由计算上个月6月份的任何特殊情况。这是全年的总和。