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

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

CREATE DATABASE SqlHintsDailyData
GO
USE SqlHintsDailyData
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-5 days as sales date
INSERT INTO #Sales(SalesDate)
VALUES(DATEADD(DAY, - ROUND(5 * RAND(), 0),GETDATE()))
GO 1000

Demo 1: Getting Daily Data by using Group By

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

RESULT:

Demo 2: Getting Daily data using Static PIVOT

[ALSO READ] PIVOT and UNPIVOT in Sql Server

SELECT *
FROM (SELECT CAST(SalesDate AS DATE) [Date], 
             Count(1)  [Sales Count]   
      FROM #Sales
      GROUP BY CAST(SalesDate AS DATE)) AS DailyData
PIVOT( SUM([Sales Count])  
    FOR [Date] IN ([2015-06-29],[2015-06-30],[2015-07-01],
  [2015-07-02],[2015-07-03],[2015-07-04])) AS DatePivot

RESULT:

Demo 3: Getting Daily 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 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 column.

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)

SELECT CAST(SalesDate AS DATE) [Date], Count(1)  [Sales Count] 
	INTO #PivotSalesData  
FROM #Sales
GROUP BY CAST(SalesDate AS DATE)

--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 ' + @ColumnName + '
    FROM #PivotSalesData
    PIVOT(SUM( [Sales Count]   ) 
          FOR [Date] IN (' + @ColumnName + ')) AS PVTTable'
--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery

RESULT:

[ALSO READ]:
How to get Yearly data in Sql Server
How to get Quarterly Data in Sql Server
How to get Monthly Data in Sql Server
How to get Hourly data in Sql Server