我正在尝试获取一份报告，您可以在其中选择日期范围并进行一些计数。我的桌子是这样的
--------------------------------------
RecID|AgentID|date
--------------------------------------
1 |00123 | 05/09/2012 16:28:49
2 |00123 | 05/09/2012 17:28:49
3 |00124 | 05/09/2012 18:28:49
4 |00124 | 06/09/2012 19:28:49
5 |00125 | 06/09/2012 20:28:49
我想要类似的东西
--------------------------------------
AgentID|05/09/2012|06/09/2012
--------------------------------------
00123 | 2 | 0
00124 | 1 | 1
00125 | 0 | 1
我有的代码是
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX),
@INIDate as date,
@ENDDate as date,
@INIDateVAR nvarchar(50),
@ENDDateVAR nvarchar(50)
Set @INIDate = '10-01-2014'
Set @ENDDate = '10-10-2014'
DECLARE @sql NVARCHAR(MAX) = N'SELECT agentID';
;WITH dr AS
(
SELECT MinDate = @INIDate,
MaxDate = @ENDDate
),
n AS
(
SELECT TOP (DATEDIFF(DAY, (SELECT MinDate FROM dr), (SELECT MaxDate FROM dr)) + 1)
d = DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY [object_id])-1,
(SELECT MinDate FROM dr))
FROM sys.all_objects
)
SELECT @sql += ',
(Select Count(*) From AllRecordsView) as ' + QUOTENAME(d)
FROM n;
SELECT @sql += ' FROM dbo.AllRecordsView Group by AgentID;'
EXEC sp_executesql @sql;
GO
但是，当我选择一个较大的日期范围时，这变得非常慢，并且得到的结果当然是完整表格的计数。
有任何想法吗？谢谢
我最终要做的是使用Reporting Services。这样，我可以将一列中的所有值分配为一列，每行一个。
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句