SQL: How to get Quarterly Data in Sql Server

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

Quarterly Sales Data
[ALSO READ] How to get Yearly data in Sql Server

Let us create a Temporary Sales table and insert 1000 Sample Sales Records With Random past 0-1000 days as the sales date by using the below script.

--Create Temporary Sales Table
CREATE TABLE #Sales
(SalesId INT IDENTITY(1,1), SalesDate DateTime)
GO
--Populate 1000 Sample Sales Records With
--Random past 0-1000 days as sales date
INSERT INTO #Sales(SalesDate)
VALUES(DATEADD(dd, - CONVERT(INT, (1000+1)*RAND()),GETDATE()))
GO 1000

Demo 1: Getting Quarterly Sales Data

SELECT DATEPART(YEAR,SalesDate) [Year],
 DATEPART(QUARTER,SalesDate) [Quarter], COUNT(1) [Sales Count]
FROM #Sales
GROUP BY DATEPART(YEAR,SalesDate),DATEPART(QUARTER,SalesDate)
ORDER BY 1,2

RESULT:
Quarterly Data

Demo 2: Getting Quarterly Sales Data using PIVOT

SELECT Year, QPivot.[1] As Q1, QPivot.[2] As Q2,
 QPivot.[3] As Q3, QPivot.[4] As Q4
FROM (SELECT YEAR(SalesDate) [Year],
     DATEPART(QUARTER, SalesDate) [Quarter],
         COUNT(1) [Sales Count]
      FROM #Sales
      GROUP BY YEAR(SalesDate),
         DATEPART(QUARTER,SalesDate)) AS QuarterlyData
PIVOT( SUM([Sales Count])  
        FOR QUARTER IN ([1],[2],[3],[4])) AS QPivot

RESULT:
Quarterly Sales Data Using PIVOT

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s