How to get Hourly data in Sql Server

This article demonstrate how to get hourly data in Sql Server in different formats as shown in the below image. Here Sales table data is presented in two different hourly aggregated sales data formats.

Let us create a Sales table and insert 1000 Sample Sales Records With Random sales date in the past 60 hours using the below script.

CREATE DATABASE SqlHintsHourlyData GO USE SqlHintsHourlyData GO --Create Temporary Sales Table CREATE TABLE #Sales (SalesId INT IDENTITY(1,1), SalesDate DateTime) GO --Populate 1000 Sample Sales Records With --Random past 0-60 hours as sales date INSERT INTO #Sales(SalesDate) VALUES(DATEADD(hh, - ROUND(60 * RAND(), 0),GETDATE())) GO 1000

Demo 1: Getting Hourly Data using Group By

SELECT CAST(SalesDate AS DATE) [Date],     DATEPART(hour,SalesDate) [Hour], Count(1)  [Sales Count]   FROM #Sales GROUP BY CAST(SalesDate AS DATE), DATEPART(hour,SalesDate) ORDER BY 1 ,2

RESULT:

Demo 2: Getting Hourly Data using Static PIVOT

[ALSO READ] PIVOT and UNPIVOT in Sql Server
Format 1: Dates as column

SELECT * FROM (SELECT CAST(SalesDate AS DATE) [Date],    DATEPART(hour,SalesDate) [Hour], Count(1)  [Sales Count]      FROM #Sales    GROUP BY CAST(SalesDate AS DATE),          DATEPART(hour,SalesDate)) AS HourlySalesData PIVOT( SUM([Sales Count])     FOR [Date] IN ([2015-07-01], [2015-07-02],   [2015-07-03])) AS DatePivot

RESULT:

Format 2: Hours as column

SELECT * FROM (SELECT CAST(SalesDate AS DATE) [Date],    DATEPART(hour,SalesDate) [Hour], Count(1)  [Sales Count]      FROM #Sales    GROUP BY CAST(SalesDate AS DATE),          DATEPART(hour,SalesDate)) AS HourlySalesData PIVOT( SUM([Sales Count])     FOR [Hour] IN ([0], [1], [2], [3], [4], [5], [6], [7],      [8], [9], [10],[11], [12], [13], [14], [15], [16],      [17], [18], [19], [20], [21], [22], [23])) AS DatePivot

RESULT:

Demo 3: Getting Hourly Data using Dynamic PIVOT

[ALSO READ] Dynamic PIVOT in Sql Server

Problem with Demo 2 approach is , it is using the static PIVOT to get the data. Which requires the developer to specify all the dates or hours as the pivot columns manually. We can re-write the Demo 2 examples by using Dynamic pivot approach as below which doesn’t require the developer to manually specify the dates/hours column.

Format 1: Dates as column

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX) DECLARE @ColumnName AS NVARCHAR(MAX)   SELECT CAST(SalesDate AS DATE) [Date],    DATEPART(hour,SalesDate) [Hour], Count(1)  [Sales Count]       INTO #PivotSalesData FROM #Sales GROUP BY CAST(SalesDate AS DATE), DATEPART(hour,SalesDate) --Get distinct values of the PIVOT Column SELECT @ColumnName= ISNULL(@ColumnName + ',','')        + QUOTENAME([Date]) FROM (SELECT DISTINCT [Date] FROM #PivotSalesData) AS Dates --Prepare the PIVOT query using the dynamic SET @DynamicPivotQuery =   N'SELECT [Hour], ' + @ColumnName + '     FROM #PivotSalesData     PIVOT(SUM( [Sales Count]   )           FOR [Date] IN (' + @ColumnName + ')) AS PVTTable' --Execute the Dynamic Pivot Query EXEC sp_executesql @DynamicPivotQuery

RESULT:

Format 2: Hours as column

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX) DECLARE @ColumnName AS NVARCHAR(MAX) SELECT CAST(SalesDate AS DATE) [Date],    DATEPART(hour,SalesDate) [Hour], Count(1)  [Sales Count]       INTO #PivotSalesData FROM #Sales GROUP BY CAST(SalesDate AS DATE), DATEPART(hour,SalesDate) --Get distinct values of the PIVOT Column SELECT @ColumnName= ISNULL(@ColumnName + ',','')        + QUOTENAME([Hour]) FROM (SELECT DISTINCT [Hour] FROM #PivotSalesData) AS Dates --Prepare the PIVOT query using the dynamic SET @DynamicPivotQuery =   N'SELECT [Date], ' + @ColumnName + '     FROM #PivotSalesData     PIVOT(SUM( [Sales Count]   )           FOR [Hour] IN (' + @ColumnName + ')) AS PVTTable' --Execute the Dynamic Pivot Query EXEC sp_executesql @DynamicPivotQuery

RESULT: