以下脚本以统一的方式表示输出:它显示期间的开始日期和结束日期以及该期间的总计数。
这也决定了寻找分组依据的值的方式。基本上,您可以看到三种不同的模式:一种是'day'
频率,另一个为'week'
还有一个适用于所有其他频率类型。
第一个最简单:PeriodStart 和PeriodEnd 都只是Date
.
几周来,我一直在使用一个众所周知的技巧,即一周的第一天是从给定日期减去一个比其工作日数小一的值得出的。周末的发现类似:我们只是添加6
到相同的表达式。
月份、季度和年份按以下方式分组。将零日期和给定日期之间对应单位的整数加回零日期。这给了我们这个时期的开始。结尾的发现非常相似,只是我们添加了比差值大 1 的数字。这产生了next期间,所以我们减去一天,这给了我们正确的结束日期。
SELECT
PeriodStart,
PeriodEnd,
Count = SUM(Count)
FROM (
SELECT
PeriodStart = CASE @Frequency
WHEN 'day' THEN Date
WHEN 'week' THEN DATEADD(DAY, 1 - DATEPART(WEEKDAY, Date), Date)
WHEN 'month' THEN DATEADD(MONTH, DATEDIFF(MONTH, 0, Date), 0)
WHEN 'quarter' THEN DATEADD(QUARTER, DATEDIFF(QUARTER, 0, Date), 0)
WHEN 'year' THEN DATEADD(YEAR, DATEDIFF(YEAR, 0, Date), 0)
END,
PeriodEnd = CASE @Frequency
WHEN 'day' THEN Date
WHEN 'week' THEN DATEADD(DAY, 7 - DATEPART(WEEKDAY, Date), Date)
WHEN 'month' THEN DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, Date) + 1, 0))
WHEN 'quarter' THEN DATEADD(DAY, -1, DATEADD(QUARTER, DATEDIFF(QUARTER, 0, Date) + 1, 0))
WHEN 'year' THEN DATEADD(DAY, -1, DATEADD(YEAR, DATEDIFF(YEAR, 0, Date) + 1, 0))
END,
Count
FROM atable
WHERE Date BETWEEN @DateStart AND @DateEnd
) s
GROUP BY
PeriodStart,
PeriodEnd
-
EXEC spReport '1/1/2011', '12/31/2011', 'day'
:
PeriodStart PeriodEnd Count
----------- ---------- -----
2011-11-15 2011-11-15 6
2011-12-16 2011-12-16 9
2011-12-17 2011-12-17 2
2011-12-18 2011-12-18 5
-
EXEC spReport '1/1/2011', '12/31/2011', 'week'
:
PeriodStart PeriodEnd Count
----------- ---------- -----
2011-11-13 2011-11-19 6
2011-12-11 2011-12-17 11
2011-12-18 2011-12-24 5
-
EXEC spReport '1/1/2011', '12/31/2011', 'month'
:
PeriodStart PeriodEnd Count
----------- ---------- -----
2011-11-01 2011-11-30 6
2011-12-01 2011-12-31 16
-
EXEC spReport '1/1/2011', '12/31/2011', 'quarter'
:
PeriodStart PeriodEnd Count
----------- ---------- -----
2011-10-01 2011-12-31 22
-
EXEC spReport '1/1/2011', '12/31/2011', 'year'
:
PeriodStart PeriodEnd Count
----------- ---------- -----
2011-01-01 2011-12-31 22
注:来自MSDN:
避免使用sp_命名程序时的前缀。 SQL Server 使用此前缀来指定系统过程。如果存在同名的系统过程,则使用该前缀可能会导致应用程序代码中断。有关更多信息,请参阅设计存储过程(数据库引擎).