I have a table 'booking' like this:
booking_id,
date,
client,
sponsor
I'm trying to get a monthly summary:
SELECT
MONTH(date) AS M,
Sponsor,
Client,
COUNT(booking_id) AS c
FROM booking
GROUP BY
M, Sponsor, Client
Now I want to see at which dates the client made bookings. I tried using STUFF() (referenced in this post: Simulating group_concat MySQL function in Microsoft SQL Server 2005?) but it conflicts with the group-by statement.
Sample data as per request. Currently i have the following:
M Sponsor Client c
March AB y 3
March FE x 4
April AB x 2
Desired output:
M Sponsor Client c dates
March AB y 3 12, 15, 18
March FE x 4 16, 19, 20, 21
April AB x 2 4, 8
Where the numbers are the day-numers (e.g. 12 march, 15 march, 18 march). In mysql I would use group_concat(date) to get the last column.
Big kudos for the answer :-)
SELECT [Month] = DATENAME(MONTH, M), Sponsor, Client, c,
[dates] = STUFF((SELECT ', ' + RTRIM(DATEPART(DAY, [date]))
FROM dbo.booking AS b
WHERE b.Sponsor = x.Sponsor
AND b.Client = x.Client
AND b.[date] >= x.M AND b.[date] < DATEADD(MONTH, 1, x.M)
ORDER BY [date]
FOR XML PATH('')), 1, 2, '')
FROM
(
SELECT
M = DATEADD(MONTH, DATEDIFF(MONTH, '19000101', [date]), '19000101'),
Sponsor,
Client,
COUNT(booking_id) AS c
FROM dbo.booking
GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, '19000101', [date]), '19000101'),
Sponsor,
Client
) AS x
ORDER BY M, Sponsor, Client;
Note that if a combination of sponsor/client has two bookings on the same day, the day number will appear in the list twice.
EDIT Here is how I tested:
DECLARE @booking TABLE
(
booking_id INT IDENTITY(1,1) PRIMARY KEY,
[date] DATE,
Sponsor VARCHAR(32),
Client VARCHAR(32)
);
INSERT @booking([date], Sponsor, Client) VALUES
('20120312','AB','y'), ('20120315','AB','y'), ('20120318','AB','y'),
('20120316','FE','x'), ('20120319','FE','x'), ('20120321','FE','x'), ('20120320','FE','x'),
('20120404','AB','x'), ('20120408','AB','x');
SELECT [Month] = DATENAME(MONTH, M), Sponsor, Client, c,
[dates] = STUFF((SELECT ', ' + RTRIM(DATEPART(DAY, [date]))
FROM @booking AS b
WHERE b.Sponsor = x.Sponsor
AND b.Client = x.Client
AND b.[date] >= x.M AND b.[date] < DATEADD(MONTH, 1, x.M)
ORDER BY [date]
FOR XML PATH('')), 1, 2, '')
FROM
(
SELECT
M = DATEADD(MONTH, DATEDIFF(MONTH, '19000101', [date]), '19000101'),
Sponsor,
Client,
COUNT(booking_id) AS c
FROM @booking
GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, '19000101', [date]), '19000101'),
Sponsor,
Client
) AS x
ORDER BY M, Sponsor, Client;
Results:
Month Sponsor Client c dates
------- ------- ------- ------- --------------
March AB y 3 12, 15, 18
March FE x 4 16, 19, 20, 21
April AB x 2 4, 8