Question

报告应该每天显示P、U、D、F和M栏的总和,以及比例:M/P+U(以合计形式)。

我对比率有问题,我不知道如何在零点前满足分数。

TYP | TIMESTAMP               | P   | U   | D   | F  | M
------------------------------------------------------------
L   | 2012-04-27 15:47:02.000 | 0   | 949 | 0   | 0  | 949
L   | 2012-04-27 15:48:18.000 | 0   | 949 | 0   | 0  | 949
L   | 2012-04-30 17:15:01.000 | 0   | 0   | 4051| 0  | 0
L   | 2012-04-30 17:44:44.000 | 0   | 984 | 5   | 0  | 986
L   | 2012-05-02 11:12:01.000 | 2117| 0   | 0   | 0  | 0
L   | 2012-05-02 11:12:09.000 | 149 | 4   | 210 | 0  | 157
L   | 2012-05-02 11:12:11.000 | 77  | 0   | 30  | 0  | 43

我的查询:

SELECT
CONVERT(date,TIMESTAMP,112) As  DAY ,
SUM(P) As PAS,
SUM(U) As UFR,
SUM(D) As DES,
SUM(F) As FIR,
SUM(M) As MOL,
[M%] = ISNULL( (SUM(M) / NULLIF( SUM(P)+SUM(U), 0 ) )*100, 0),
FROM DATASET
GROUP BY CONVERT(date,TIMESTAMP,112) ORDER BY CONVERT(date,TIMESTAMP,112) DESC

最新:这是报告

 DAY         |  PAS   | UFR   | DES   | FIR   | MOL   | M%
----------------------------------------------------------------
2012-05-02   | 2343   |  4    |  240  | 0     |  200  | 0
2012-04-30   | 0      |  984  |  4056 | 0     |  986  | 100
2012-04-27   | 0      |  1898 |  0    | 0     |  1898 | 100

Answer 1

问题在于您不核算整数分割。

Select Cast([Timestamp] As Date) As  Day 
  , Sum(P) As Pas
  , Sum(U) As Ufr
  , Sum(D) As Des
  , Sum(F) As Fir
  , Sum(M) As Mol
  , IsNull( (Sum(M) / NullIf( Sum(P) * 1.0000 + Sum(U), 0 ) ) *100, 0) As [M%]
From Dataset
Group By Cast([Timestamp] As Date)
Order By [Day] Desc

http://www.sqlfiddle.com/#! 3/891b0/10" rel=“nofollow” >SQL Fiddle 版本

友情链接