SQL Pivot data by time

Question

I'm trying to get a pivot of my data, based on the timestamp. I want to group them into half-hour "buckets". For example, with the data below:

CREATE TABLE #test (
    Employee nvarchar(20) NOT NULL
    ,[SaleTime] time NOT NULL
    ,Amount float NOT NULL
)

INSERT INTO #test VALUES
('A',  '08:10', '100.50')
,('A', '12:20', '758.23')
,('A', '11:59', '592.11')
,('B', '12:00', '95.00')
,('B', '09:01', '29.10')
,('B', '09:04', '53.22')
,('C', '11:23', '55.77')
,('C', '10:40', '128.00')

I would like the result to be something like

   Time         |       A       |       B       |       C       |
-----------------------------------------------------------------
08:00 - 08:30   |   100.5       |               |               |
08:30 - 09:00   |               |               |               |
09:00 - 09:30   |               |       82.32   |               |
09:30 - 10:00   |               |               |               |
10:00 - 10:30   |               |               |               |
10:30 - 11:00   |               |               |       128.00  |
11:00 - 11:30   |               |               |       55.77   |
11:30 - 12:00   |   592.11      |               |               |
12:00 - 12:30   |   758.23      |       95.00   |               |
12:30 - 13:00   |               |               |               |
-----------------------------------------------------------------

Do I have to create an empty table with the timeslots in order to do this? Is there a method of doing this without using CASE WHEN?

Thanks!

Answer 1

Maybe I'm try to solve a non-existent problem but I'd like to offer an alternative solution which is dynamic in respect to the number of employees (it adds columns for extra employees) and sums the amounts sold in a time slot per employee if there's more than one sale in that slot; if you don't aggregate the amount you'll end up with multiple rows for every time slot where someone has sold more than one time.

The time slot generation is borrowed from the excellent solution in Nenads answer.

First the the test data with a couple of extra rows to illustrate the difference:

DROP TABLE #test;
CREATE TABLE #test (
    Employee nvarchar(20) NOT NULL
    ,[SaleTime] time NOT NULL
    ,Amount float NOT NULL
)

INSERT INTO #test VALUES
('A',  '08:10', '100.50')
,('A', '12:20', '758.23')
,('A', '11:59', '592.11')
,('B', '12:00', '95.00')
,('B', '09:01', '29.10')
,('B', '09:04', '53.22')
,('C', '11:23', '55.77')
,('C', '10:40', '128.00')
,('D', '09:40', '28.00')
,('E', '11:40', '50.00')
,('E', '11:35', '20.00')

The query build a SQL statement dynamically and executes it with the EXECUTE statement:

DECLARE @Headers VARCHAR(MAX)
SELECT @Headers = COALESCE(@Headers + ',[' + Employee + ']', '[' + Employee + ']')
FROM (SELECT DISTINCT Employee FROM #test) Emp

DECLARE @SQL NVARCHAR(MAX)

SET @SQL = N'

WITH CTE_TimeSlots AS
(
    SELECT CAST(''8:00'' AS TIME) AS StartTime, CAST(''8:30'' AS TIME) AS EndTime
    UNION ALL
    SELECT DATEADD(MI, 30, StartTime), DATEADD(MI, 30, EndTime)   
    FROM CTE_TimeSlots
    WHERE StartTime <= ''12:00''
)

SELECT * FROM (
    SELECT CONVERT(NVARCHAR(10),StartTime) + '' - '' + CONVERT(NVARCHAR(10),EndTime) AS [Time],  Amount, Employee
    FROM CTE_TimeSlots t
    LEFT JOIN #test d ON d.SaleTime >= StartTime AND d.SaleTime < EndTime
    ) innerQuery
PIVOT (SUM(Amount) FOR Employee IN (' + @Headers + ')
  ) AS PivotTable
'    
--PRINT @SQL -- Uncomment to see the query which will be run
EXECUTE(@SQL)

Answer 2

You can use recursive CTE to create your time slots 'on the fly' and then join it to your data. There is a way to avoid using CASE, you can use PIVOT command instead, but I think this is much simpler:

WITH CTE_TimeSlots AS
(
    SELECT CAST('8:00' AS TIME) AS StartTime, CAST('8:30' AS TIME) AS EndTime
    UNION ALL
    SELECT DATEADD(MI, 30, StartTime), DATEADD(MI, 30, EndTime)   
    FROM CTE_TimeSlots
    WHERE StartTime <= '12:00'
)
SELECT CONVERT(NVARCHAR(10),StartTime) + ' - ' + CONVERT(NVARCHAR(10),EndTime) AS [Time]
 , CASE WHEN Employee = 'A' THEN Amount END AS A
 , CASE WHEN Employee = 'B' THEN Amount END AS B
 , CASE WHEN Employee = 'C' THEN Amount END AS C
FROM CTE_TimeSlots t
LEFT JOIN #test d ON d.SaleTime >= StartTime AND d.SaleTime < EndTime

SQLFiddle DEMO