我将编辑您的存储过程以传递在网页中选择的开始日期和结束日期,以便您可以将它们包含在WHERE
clause.
-- Daily
SELECT CallTime, COUNT(*) AS 'Records Found'
FROM TRN_Call
WHERE CallTime BETWEEN @StartDate AND @EndDate
GROUP BY CallTime
每周可能相当复杂。
如果你检查这个link http://www.sqlteam.com/article/returning-a-week-number-for-any-given-date-and-starting-fiscal-month您将看到一个函数(如下),您可以使用它来帮助您获得每周结果
例如所以创建下面的函数
create function FiscalWeek (@startMonth varchar(2), @myDate datetime)
returns int
as
begin
declare @firstWeek datetime
declare @weekNum int
declare @year int
set @year = datepart(year, @myDate)+1
--Get 4th day of month of next year, this will always be in week 1
set @firstWeek = convert(datetime, str(@year)+@startMonth+'04', 102)
--Retreat to beginning of week
set @firstWeek = dateadd(day, (1-datepart(dw, @firstWeek)), @firstWeek)
while @myDate < @firstWeek --Repeat the above steps but for previous year
begin
set @year = @year - 1
set @firstWeek = convert(datetime, str(@year)+@startMonth+'04', 102)
set @firstWeek = dateadd(day, (1-datepart(dw, @firstWeek)), @firstWeek)
end
set @weekNum = (@year*100)+((datediff(day, @firstweek, @myDate)/7)+1)
return @weekNum
end
现在,您应该能够运行下面的代码,该代码应按周分组。要选择“日”、“周”或“年”,您还必须将用户选择的时间段传递给存储过程。
(ps.还没来得及做年)
SELECT dbo.FiscalWeek('04', CallTime), dbo.FiscalWeek('04', CallTime)%100 , COUNT(*) AS 'Records Found - Weekly'
FROM TRN_Call
WHERE CallTime BETWEEN @StartDate AND @EndDate
GROUP BY dbo.FiscalWeek('04', CallTime), dbo.FiscalWeek('04', CallTime)%100