SSRS report showing number of appointments per customer per week (layout)

Question

I want to create a report that shows the number of meetings per customer per week in a calendar type of layout. I'm using SQL Server Reporting Services 2005.

For simplicity lets say we have only these two tables:

Customer

• Id
• Name

Appointment

• Id
• Subject
• Date

What is the most efficient way to produce a report with this layout?

         | WEEK #
CUSTOMER | 1| 2| 3| 4| 5| 6|...|52| TOTAL
---------+-+-+-+-+-+-+--+--+---+--+------
Cust A   | 0| 2| 0| 1| 0| 0|...| 1|  4
Cust B   | 0| 1| 0| 1| 1| 0|...| 0|  3
Cust C   | 0| 0| 0| 1| 0| 0|...| 0|  1
Cust D   | 1| 0| 0| 0| 1| 0|...| 0|  2

Edit: remember some years might start with week # 53.

Answer 1

I skipped enumerating all 52 (53) weeks, and just enumerated the ones where I put test data. Hopefully you get the idea.

DECLARE @Customer TABLE
(
   [Id] int not null primary key,
   [Name] nvarchar(50) not null
);

DECLARE @Appointment TABLE
(
   [Id] int not null primary key,
   [CustomerId] int not null,
   [OccurredOn] datetime not null DEFAULT getdate(),
   [Subject] nvarchar(50) not null
);


INSERT INTO @Customer
SELECT 1, 'Aaron Burr' UNION ALL
SELECT 2, 'John Adams' UNION ALL
SELECT 3, 'George Washington';

INSERT INTO @Appointment
SELECT 1, 1, '2009-01-04', 'Ants in the pants' UNION ALL
SELECT 2, 1, '2009-02-04', 'Follow up' UNION ALL
SELECT 3, 1, '2009-07-20', 'Check-up' UNION ALL
SELECT 4, 2, '2009-02-05', 'Wellness Check' UNION ALL
SELECT 5, 2, '2009-11-26', 'Private' UNION ALL
SELECT 6, 2, '2009-06-03', 'Stubbed Toe' UNION ALL
SELECT 7, 3, '2009-11-27', 'Toothache' UNION ALL
SELECT 8, 3, '2009-11-28', 'Crown';

WITH AggregateAppointments AS
(
    SELECT      c.Id,
                c.Name,
                DATEPART(wk, a.OccurredOn) [Week],
                COUNT(c.Id) [Count]
    FROM        @Appointment a
    JOIN        @Customer c ON a.CustomerId = c.Id
    GROUP BY    c.Id, c.Name, DATEPART(wk, a.OccurredOn)
),

PivotAppointments AS
(
    SELECT      [Id],
                [Name],
                ISNULL([53], 0) [53],
                ISNULL([2], 0) [2],
                ISNULL([6], 0) [6],
                ISNULL([23], 0) [23],
                ISNULL([30], 0) [30],
                ISNULL([48], 0) [48]
    FROM        AggregateAppointments
    PIVOT      (
      SUM([Count])
      FOR [Week] IN ([2], [6], [23], [30], [48], [53])
    ) as [PivotAppointments]
)

SELECT        *,
              [53]+[2]+[6]+[23]+[30]+[48] [Total]
FROM          PivotAppointments

Results in:

[Id] [Name]              [53]  [2]  [6]  [23]  [30]  [48]  [Total]
------------------------------------------------------------------
1    Aaron Burr          0     1    1    0     1     0     3
2    George Washington   0     0    0    0     0     2     2
3    John Adams          0     0    1    1     0     1     3