Make a column for each count(*) in a day of the week

I have a table of people who are doing piece work. Every entry in the table has a user-ID, name, and a Date for each report they enter.

I need to make a weekly report of what these people have done per day starting with Sunday

• How do you group by a week starting Sunday?

• How do you make a column for each COUNT(*) per day?

t0001, Tod, 2015-6-29
t0001, Tod, 2015-6-29
t0001, Tod, 2015-6-29
t0001, Tod, 2015-6-29
t0001, Tod, 2015-6-29
t0001, Tod, 2015-6-28
t0001, Tod, 2015-6-28
b0002, Ben, 2015-6-29
b0002, Ben, 2015-6-29
b0002, Ben, 2015-6-28

This is what I'm looking for.

NAME | S | M | T | W | R | F | S | TOTAL
----------------------------------------
TOD  | 2 | 5 | - | - | - | - | - | 7
BEN  | 1 | 2 | - | - | - | - | - | 3

Here's one way. Assuming this table and sample data:

USE tempdb;
GO
CREATE TABLE dbo.splunge(UserID CHAR(5), Name VARCHAR(32), Date DATE);

INSERT dbo.splunge(UserID, Name, Date) VALUES('t0001','Tod','20150629'),
  ('t0001','Tod','20150629'),('t0001','Tod','20150629'),('t0001','Tod','20150629'),
  ('t0001','Tod','20150629'),('t0001','Tod','20150628'),('t0001','Tod','20150628'),
  ('b0002','Ben','20150629'),('b0002','Ben','20150629'),('b0002','Ben','20150628');

Then with a variable or parameter to dictate the week you care about:

DECLARE @dt DATE = '20150702';

-- roll it back to Sunday
SET @dt = DATEADD(WEEK, DATEDIFF(WEEK, '19050101', @dt), '19050101');

;WITH dt(dt) AS
(
  SELECT TOP (7) dt = DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY [object_id])-1, @dt)
  FROM sys.all_objects
  ORDER BY [object_id]
),
x AS 
(
  SELECT 
    s.Name, dt.dt, 
    dd = DATEDIFF(DAY, @dt, dt.dt), 
    c = CONVERT(VARCHAR(11), COUNT(*))
  FROM dt
  LEFT OUTER JOIN dbo.splunge AS s 
  ON s.Date = dt.dt
  GROUP BY Grouping SETS((s.Name),(s.Name,dt.dt))
)
SELECT 
  Name, 
  [S] = COALESCE(MAX(CASE dd WHEN 0 THEN c END), '-'),
  [M] = COALESCE(MAX(CASE dd WHEN 1 THEN c END), '-'),
  [T] = COALESCE(MAX(CASE dd WHEN 2 THEN c END), '-'),
  [W] = COALESCE(MAX(CASE dd WHEN 3 THEN c END), '-'),
  [T] = COALESCE(MAX(CASE dd WHEN 4 THEN c END), '-'),
  [F] = COALESCE(MAX(CASE dd WHEN 5 THEN c END), '-'),
  [S] = COALESCE(MAX(CASE dd WHEN 6 THEN c END), '-'),
  TOTAL = MAX(CASE WHEN dt IS NULL THEN c END)
FROM x
WHERE Name IS NOT NULL
GROUP BY Name;

Results:

Name    S   M   T   W   T   F   S   TOTAL
------  --- --- --- --- --- --- --- -----
Ben     1   2   -   -   -   -   -   3
Tod     2   5   -   -   -   -   -   7

Another way:

DECLARE @dt DATE = '20150702';

-- make sure Sunday is the "beginning" of the week:
SET DATEFIRST 7;

-- roll it back to Sunday
SET @dt = DATEADD(WEEK, DATEDIFF(WEEK, '19050101', @dt), '19050101');

;WITH dt AS
(
  SELECT Name, dw = DATEPART(WEEKDAY, Date)
  FROM dbo.splunge
  WHERE Date >= @dt AND Date < DATEADD(DAY, 7, @dt)
),
x AS
(
  SELECT Name, dw = COALESCE(dw, 8),  
    c = CONVERT(VARCHAR(11), COUNT(*))
  FROM dt 
  GROUP BY GROUPING SETS((Name),(Name,dw))
)
SELECT Name, 
  [S] = COALESCE([1],'-'),
  [M] = COALESCE([2],'-'),
  [T] = COALESCE([3],'-'),
  [W] = COALESCE([4],'-'),
  [T] = COALESCE([5],'-'),
  [F] = COALESCE([6],'-'),
  [S] = COALESCE([7],'-'),
  TOTAL = [8]
FROM x 
PIVOT (MAX(c) FOR dw IN ([1],[2],[3],[4],[5],[6],[7],[8])) AS pvt;

Don't forget to clean up:

DROP TABLE dbo.splunge;

StackExchange Database Administrators Q#105461, answer score: 5