列中的sql日期范围并按组计数 [英] sql date range in column and count by group

问题描述

我正在尝试获取一份报告，您可以在其中选择日期范围并获得一些计数.我的桌子是这样的

I am trying to get a report where you can select a date range and get some counts. My table is something like this

--------------------------------------
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

但是，当我选择较大的日期范围时，这会变得非常慢，并且得到的结果当然是完整表格的计数.

But this gets very slow when I select a big date range and the results I get are of course the count of the complete table.

有什么想法吗?谢谢

推荐答案

我最终要做的是使用Reporting Services.这样，我就可以将一列中的所有值分配为一列，每行一个.

What I end up doing is using Reporting Services. That way I can assign all the values from a column as a columns, one per each row.

这篇关于列中的sql日期范围并按组计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！