如何使用SQL Query获取计数和百分比？ [英] How to get count and percentage using SQL Query?

问题描述

下面给出的是数据库表（TableA）。

 SlNo TeamName WorkInfo SubmitDate 
 ---- ------- ------- --------- 
 1 Team1 0 10/10/2014 
 2 Team1 0 10/10/2014 
 3 Team1 1 11/10/2014 
 4 Team2 1 12/15/2014 
 5 Team2 1 10/10/2014 
 6 Team3 0 10/10/2014 
 7 Team3 1 10/10/2014 
 

我有两个文本框用于选择开始和结束日期。我希望在这些日期之间显示详细信息。

以下代码用于查找开始日期和结束日期之间的日期差异，并显示在另一个名为`txtTotalDays`的文本框中。 />


代码：

 DateTime d1 = txtStartDate.Text！=  string  .Empty？ Convert.ToDateTime（txtStartDate.Text）：DateTime.MinValue; 
 DateTime d2 = txtEndDate.Text！=  string  .Empty？ 
 Convert.ToDateTime（txtEndDate.Text）：DateTime.MinValue; 
 TimeSpan tspan = d2  -  d1; 
 txtTotalDays.Text = tspan.TotalDays.ToString（）

鉴于以下查询，我们根据提供的详细信息和计数开始日期和结束日期。



SQL查询

  SELECT  TeamName，Count（ DISTINCT   CASE   WHEN  WorkInfo =  1  那么 SlNo  end ） AS  Count1，Count（ DISTINCT   CASE   WHEN  WorkInfo =  0   THEN  SlNo  end ） AS  Count0  FROM  tableA 
  WHERE （Subm itDate  BETWEEN   @start   AND   @ end ） GROUP   BY  TeamName 

以上查询显示如下所示的输出：

 TeamName Count1 Count0 
 -------- ----- ------ 
 Team1 1 2 
 Team2 2 0 
 Team3 1 1 

我想根据`计数和日期差异'显示百分比。帮我修改上面的解决方案。 `期待的输出样本如下：`



我选择了开始日期和结束日期。我将日期差异（txtTotalDays.Text）设置为10.根据该日期选择，我想获得如下所示的值。

 TeamName Count1 Count0 Percentage1 Percentage0 
 -------- ----- ------ ----------- ----------- 
 Team1 1 2 10％20％
 Team2 2 0 20％0％
 Team3 1 1 10％10％
 

帮助我找到合适的解决方案。谢谢。

解决方案

用下面的方法修改查询。

 选择 t.TeamName，t.Count1，t.Count0，（t.Count1 /（daysDifference *  100  ） as 百分比1，
（t.Count0 /（daysDifference *  100 ） as 百分比0 来自 
（ SELECT  TeamName，Count （ DISTINCT   CASE   WHEN  WorkInfo =  1   THEN  SlNo  end ） AS  Count1，Count（ DISTINCT   CASE   WHEN  WorkInfo =  0   THEN  SlNo  end ） AS  Count0  FROM  tableA 
  WHERE （SubmitDate  BETWEEN   @start   AND   @ end ）  GROUP   BY  TeamName） as  t 

其中 daysDifference 是你的c＃变量。

  SELECT  
 TeamName，
 Count（ DISTINCT   CASE   WHEN  WorkInfo =  1  那么 SlNo  end ） AS  Count1，
计数（ DISTINCT   CASE   WHEN  WorkInfo =  0  那么 SlNo 结束） AS  Count0，
计数（ DISTINCT   CASE   WHEN  WorkInfo =  1  那么 SlNo  end ）*  100 。 0  /（DATEDIFF（Day， @ start ， @ end ）+  1 ） AS 百分比1，
计数（ DISTINCT   CASE   WHEN  WorkInfo =  0   THEN  SlNo  end ）*  100 。 0  /（DATEDIFF（Day， @ start ，  @ end ）+  1 ） AS 百分比0 
  FROM  
 tableA 
  WHERE  
（SubmitDate < span class =code-keyword> BETWEEN   @ start   AND   @ end ）
  GROUP   BY  
 TeamName 

Given below is the database table(TableA).

SlNo  TeamName  WorkInfo SubmitDate
----  -------   -------  ---------
 1     Team1        0      10/10/2014
 2     Team1        0      10/10/2014
 3     Team1        1      11/10/2014
 4     Team2        1      12/15/2014
 5     Team2        1      10/10/2014
 6     Team3        0      10/10/2014
 7     Team3        1      10/10/2014

I have two text boxes for selecting start and end date. I want to display details in between that dates.
Given below code is used to find the date difference between start date and end date and displayed on another text box named as "`txtTotalDays`".

Code:

DateTime d1 = txtStartDate.Text != string.Empty ? Convert.ToDateTime(txtStartDate.Text) : DateTime.MinValue;
DateTime d2 = txtEndDate.Text != string.Empty ? 
Convert.ToDateTime(txtEndDate.Text) : DateTime.MinValue; 
TimeSpan tspan = d2 - d1;
txtTotalDays.Text = tspan.TotalDays.ToString()

Given below query is giving the details and count based on the start date and end date.

SQL Query

SELECT TeamName,Count(DISTINCT CASE WHEN WorkInfo = 1 THEN SlNo end) AS Count1 , Count(DISTINCT CASE WHEN WorkInfo = 0 THEN SlNo end) AS Count0 FROM tableA
WHERE (SubmitDate BETWEEN @start AND @end) GROUP BY TeamName

The above query shows the out put like given below:

TeamName   Count1   Count0
--------   -----    ------
 Team1        1        2
 Team2        2        0
 Team3        1        1

I want to display the percentage based on the `count and date difference` . Help me to modify the above solution. `The expecting output sample is given below:`

I selected start date and end date. I got the date difference (txtTotalDays.Text) as 10. Based on that date selection I want get values like given below.

TeamName   Count1   Count0  Percentage1  Percentage0
--------   -----    ------  -----------  -----------
Team1        1        2        10%           20%
Team2        2        0        20%            0%
Team3        1        1        10%           10%

Help me to find a proper solution. Thank You.

解决方案

Modify the query with below.

select t.TeamName,t.Count1,t.Count0,(t.Count1/(daysDifference * 100) as Percentage1,
(t.Count0/(daysDifference * 100) as Percentage0 from 
(SELECT TeamName,Count(DISTINCT CASE WHEN WorkInfo = 1 THEN SlNo end) AS Count1 , Count(DISTINCT CASE WHEN WorkInfo = 0 THEN SlNo end) AS Count0 FROM tableA
WHERE (SubmitDate BETWEEN @start AND @end) GROUP BY TeamName) as t

where daysDifference is your c# variable.

SELECT 
  TeamName,
  Count(DISTINCT CASE WHEN WorkInfo = 1 THEN SlNo end) AS Count1, 
  Count(DISTINCT CASE WHEN WorkInfo = 0 THEN SlNo end) AS Count0,
  Count(DISTINCT CASE WHEN WorkInfo = 1 THEN SlNo end) * 100.0 / (DATEDIFF(Day, @start, @end) + 1) AS Percentage1,
  Count(DISTINCT CASE WHEN WorkInfo = 0 THEN SlNo end) * 100.0 / (DATEDIFF(Day, @start, @end) + 1) AS Percentage0
FROM 
  tableA 
WHERE 
  (SubmitDate BETWEEN @start AND @end) 
GROUP BY 
  TeamName

这篇关于如何使用SQL Query获取计数和百分比？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！