sql server语言 - sql -如何计数记录，每个状态在一行每天

Question

我有一个表Sales

Sales
--------
id
FormUpdated
TrackingStatus

有Complete、Incomplete、SaveforLater、ViewRates等几种状态

我想在这个表格里看到我过去8天(包括今天)的成绩。

预期结果:

Date Part of FormUpdated, Day of Week, Counts of ViewRates, Counts of Sales(complete), Counts of SaveForLater
--------------------------------------
2015-05-19   Tuesday    3   1   21  
2015-05-18   Monday     12  5   10
2015-05-17   Sunday     6   1   8
2015-05-16   Saturday   5   3   7 
2015-05-15   Friday     67  5   32
2015-05-14   Thursday   17  0   5 
2015-05-13   Wednesday  22  0   9
2015-05-12   Tuesday    19  2   6

下面是我的sql查询:

select  datename(dw, FormUpdated), count(ID), TrackingStatus 
from Sales
where FormUpdated <= GETDATE()
AND FormUpdated >= GetDate() - 8
group by  datename(dw, FormUpdated), TrackingStatus
order by  datename(dw, FormUpdated) desc

我不知道下一步该怎么做。

更新

我忘了说，我只需要FormUpdated的Date部分，而不是所有部分。

Answer 1

您可以使用SUM(CASE WHEN TrackingStatus = 'SomeTrackingStatus' THEN 1 ELSE 0 END))来获取单个列中每个跟踪状态的状态计数。就像这样。SQL小提琴

select  
CONVERT(DATE,FormUpdated) FormUpdated,
DATENAME(dw, CONVERT(DATE,FormUpdated)),
SUM(CASE WHEN TrackingStatus = 'ViewRates' THEN 1 ELSE 0 END) c_ViewRates,
SUM(CASE WHEN TrackingStatus = 'Complete' THEN 1 ELSE 0 END) c_Complete,
SUM(CASE WHEN TrackingStatus = 'SaveforLater' THEN 1 ELSE 0 END) c_SaveforLater 
from Sales
where FormUpdated <= GETDATE()
AND FormUpdated >= DATEADD(D,-8,GetDate())
group by  CONVERT(DATE,FormUpdated)
order by  CONVERT(DATE,FormUpdated) desc

Answer 2

您也可以使用PIVOT来实现此结果—您只需要在SELECT和FOR中完成TrackingStatus名称列表，而不需要GROUP BY:

WITH DatesOnly AS
(
  SELECT Id, CAST(FormUpdated AS DATE) AS DateOnly, DATENAME(dw, FormUpdated) AS DayOfWeek, TrackingStatus
  FROM Sales
)
SELECT  DateOnly, DayOfWeek, 
        -- List of Pivoted Columns
        [Complete],[Incomplete], [ViewRates], [SaveforLater]
FROM DatesOnly
PIVOT 
(
   COUNT(Id)
   -- List of Pivoted columns
   FOR TrackingStatus IN([Complete],[Incomplete], [ViewRates], [SaveforLater])
) pvt
WHERE  DateOnly <= GETDATE() AND DateOnly >= GetDate() - 8
ORDER BY DateOnly DESC

SqlFiddle

另外，我认为你的ORDER BY是错误的——它应该是日期，而不是星期几。