I need to produce a list of staff and their start / end times for each day. The challenge (for me) is the format needed - shown below. I have seem examples of the 'FOR XML' clause in use and it looks like it could do the trick but I am struggling to understand how to build the query. Can anyone help?
DECLARE @Staff TABLE (stStaffID INT, StaffName VARCHAR(10))
INSERT INTO @Staff SELECT 1,'Bob'
INSERT INTO @Staff SELECT 2,'Fred'
INSERT INTO @Staff SELECT 3,'Mark'
DECLARE @Shifts TABLE (shStaffID INT, shShiftDate DATETIME, shStartTime CHAR(5), shEndTime CHAR(5))
INSERT INTO @Shifts SELECT 1,'2015/03/01','06:00','13:00'
INSERT INTO @Shifts SELECT 1,'2015/03/01','22:00','00:00'
INSERT INTO @Shifts SELECT 2,'2015/03/10','09:00','10:00'
INSERT INTO @Shifts SELECT 2,'2015/03/14','09:00','10:00'
INSERT INTO @Shifts SELECT 3,'2015/01/10','09:00','15:00'
INSERT INTO @Shifts SELECT 3,'2015/01/10','19:00','22:00'
-- Result set required as below (grouped by staff / date)
-- Name Date Details
-- Bob 01/03/2015 Start: 06:00 End: 13:00, Start: 22:00 End: 00:00
-- Fred 01/03/2015 Start: 09:00 End: 10:00
-- Fred 14/03/2015 Start: 09:00 End: 10:00
-- Mark 10/01/2015 Start: 09:00 End: 15:00, Start: 19:00 End: 22:00