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.
[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:
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:
[ALSO READ]:
How to get Yearly data in Sql Server
How to get Monthly Data in Sql Server
How to get Daily Data in Sql Server
How to get Hourly data in Sql Server
It is interesting and I enjoyed reading.
By any chance you could share the data file ?
Thanks for your solutions.
Good One, Thanks for the explanation in detail.
Very great article guys, thank you
Thank you Asmaa
Thanks It was Great, I was trying different solution but could not able to get the Right Solution, but the Solution you provided is simple and Best.
Thanks Pradeep… I am glad that this article helped you
I have 3 tables wise Enquiry/Admission/Collections the identification field is customer ID
Now i want to generate Year – Month wise report by gathering all the 3 tables for the
given range of dates. Something like this.
Customer place ID Year-Month Enquiry Admission Collection
———————————————————————————————–
Can U help me by your valued suggestions please..
Thank you very much, this article is very helpful for me.
Thanks Mulk Sidqi. I’m glad that this article helped you.