This article demonstrate how to get Monthly data in Sql Server in different formats as shown in the below image. Here Sales table data is presented in two different Monthly aggregated sales data formats.
[ALSO READ] How to get Quarterly Data in Sql Server
Let us create a Sales table and insert 1000 Sample Sales Records With Random sales date in past 0-798 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-798 days as sales date INSERT INTO #Sales(SalesDate) VALUES(DATEADD(dd, - CONVERT(INT, (798+1)*RAND()),GETDATE())) GO 1000
Demo 1: Getting Monthly Data
SELECT YEAR(SalesDate) [Year], MONTH(SalesDate) [Month], DATENAME(MONTH,SalesDate) [Month Name], COUNT(1) [Sales Count] FROM #Sales GROUP BY YEAR(SalesDate), MONTH(SalesDate), DATENAME(MONTH, SalesDate) ORDER BY 1,2
RESULT:
Demo 2: Getting Monthly Data using PIVOT
SELECT *
FROM (SELECT YEAR(SalesDate) [Year],
DATENAME(MONTH, SalesDate) [Month],
COUNT(1) [Sales Count]
FROM #Sales
GROUP BY YEAR(SalesDate),
DATENAME(MONTH, SalesDate)) AS MontlySalesData
PIVOT( SUM([Sales Count])
FOR Month IN ([January],[February],[March],[April],[May],
[June],[July],[August],[September],[October],[November],
[December])) AS MNamePivot
RESULT:
[ALSO READ]:
How to get Yearly data in Sql Server
How to get Quarterly 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 ?
Great Article………… In between below is my Stored procedure . @from and @to are the datepickers value, is there any chance where I can retrieve the values in the format DDMMYYYY from the database. the values are inserted using getdate() function so the format is like ddmmyyyy hh:mi:ss. Of cource Time is important so can use custom format and insert in DDMMYYYY format. but while retrieving data between dates I need to compare the values and both should be in the same format. Please help me.
[dbo].[getTransactionList](@from DateTime,@to DateTime, @CustomerID varchar(20))
where CustID = @CustID and Comit_Date between @from and @to
order byComitID Desc
end
This is great, I learned a lot from it, thank you!
Thank you Andy
Great work really hepful
Awesome, exactly what I needed. Can you also show me how to get weekly data.
A nice Article
works like a charm
THANK YOU
Thank you Hasnen Tai… Appreciate your comments…
how do i short the data by month name like JAN,FEB,MAR
in dynamic pivot sql queries
MY QUERY
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX) DECLARE @ColumnName AS NVARCHAR(MAX) select grno,format(a_date,’MMMM’) [Date] ,sum(status) [sum],count(status) [tot] INTO #PivotData from attendance_master where format(a_date,’MMMM’)>=’April’ and format(a_date,’MMMM’)<='May' and sid=30 group by a_date,grno order by format(a_date,'MM') SELECT @ColumnName= ISNULL(@ColumnName + ',','') + QUOTENAME([Date]) FROM (SELECT distinct [Date] FROM #PivotData) AS Dates SET @DynamicPivotQuery = 'SELECT grno,' + @ColumnName + 'FROM (select grno,[Date],sum from #PivotData) x PIVOT(sum(sum) FOR [Date] IN (' + @ColumnName + ')) AS PVTTable1 ' EXEC sp_executesql @DynamicPivotQuery
————————OUTPUT————————–
grno April August July June May
—————————————————————————————————————————————————————————————————————————————————————- ———– ———– ———– ———– ———–
2016BCA3011 4 3 3 3 4
2016BCA4007 3 3 3 3 3
2016BCA4008 3 4 4 3 3
2016BCA4009 3 3 3 3 3
2016BCA4010 3 3 3 4 4
2016BCA4011 4 3 3 4 3
date count
1/02/20016 0
2/02/20016 1
3/02/20016 0
.
.
.
10/02/20016 6
how can i get this out put in mysql have any query
thank you
good work
Thanks Ananeyulu. Appreciate your comments!
thank u so much…
Really superb
Is there a way to then get an overall sales number underneath when you add all of the years together for each month?
For example, under 2014 it would say Overall and at the bottom of January it would be 116 and for February it would be 99.
thx a lot bro, ur what i searching for
how about postgres?
is it the same?
Thank you very much. it was very helpful
Thank you it was very helpful