SQL How to count records for each state in one line per day?

Question

Update 2

Sorry, I forgot to mention, I only need the Date part FormUpdated, not all the parts.

---

I have a table Sales

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

There are several statuses, for example. Complete, Incomplete, SaveforLater, ViewRates, Etc.

I want to get the results in this form for the last 8 days (including today).

Expected Result:

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

Here is my sql query:

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

I do not know how to take the next step.

Please help, thanks!

Answer 1

You can use SUM(CASE WHEN TrackingStatus = 'SomeTrackingStatus' THEN 1 ELSE 0 END))to get the number of states for each tracking status in a separate column. Something like that. SQL Fiddle

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 - TrackingStatus SELECT FOR, 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 - , .