仪表板查询优化 [英] Dashboard query optimization

问题描述

亲爱的，



我有桌子说客户​​

Dear all,

I Have table say Customer

Id	dtTime	Status
1	29/12/2014	A
2	29/12/2014	A
3	29/12/2014	A
4	29/12/2014	A
5	28/12/2014	B
6	28/12/2014	B
7	28/12/2014	B
8	28/12/2014	B
8	28/12/2014	B
9	27/12/2014	C
10	27/12/2014	C
11	27/12/2014	C
12	27/12/2014	C
12	27/12/2014	C
12	27/12/2014	C

我想在仪表板中显示数据

仪表板将看起来等于下面的< br $> b $ b

I want to display data in dashboard
Dashboard will look some what equal to below

29/12/2014 4
28/12/2014 5
27/12/2014 6

我想根据日期获得记录数。



目前我有以下查询获取数据..我想优化此查询。

I want to get count of records based on date.

currently i have below query which gets me data.. i want to optimize this query.

(select count(*) from Customer um where (select CONVERT(varchar(20),dtTime,101))=CONVERT(varchar(20),(GETDATE()-1),101) and 
 um.[Status]='A') as T_1,
(select count(*) from Customer um where (select CONVERT(varchar(20),dtTime,101))=CONVERT(varchar(20),(GETDATE()-2),101) and 
 um.[Status]='B') as T_2,
(select count(*) from Customer um where (select CONVERT(varchar(20),dtTime,101))=CONVERT(varchar(20),(GETDATE()-3),101) and 
 um.[Status]='C') as T_3

请帮帮我。

Please help me with this.

推荐答案

作为 Tomas Takac [ ^ ]在他对问题的评论中提到，最简单的方法是使用：

As Tomas Takac[^] mentioned in his comment to the question, the simplest way is to use:

SELECT dtTime, count(*) AS CountOfUsers
FROM Customer
GROUP BY dtTime

如果您想获取状态信息：

In case you want to fetch status information:

SELECT dtTime, [status], count(*) AS CountOfUsers
FROM Customer
GROUP BY dtTime, [status]

如果你想获得过去3天的数据：

If you want to get data from last 3 days:

SELECT dtTime, count(*) AS CountOfUsers
FROM Customer
WHERE dtTime BETWEEN DATEADD(dd, DATEDIFF(dd, -3, GETDATE()), 0) AND DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)
GROUP BY dtTime

这篇关于仪表板查询优化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！