CODEWITHSUNDEEP
c#sqlsql-server
MOHSIN ALI
MOHSIN ALI

Reputation: 13

Monthly data of current year in SQL Server

I'm using SQL Server for getting the records of sale and purchase all the month of current year but the query I've used doesn't give correct data.

I want the total sum of sale and purchase of all the months of the current year and using group by in query.

The result would be then used in chart 

SELECT YEAR(Purchase_Product.Purchase_date) [Year], 
       DATENAME(MONTH,Purchase_Product.Purchase_date) [Month Name],
       sum(Purchase_Product.total_Amount) as 'Total'
FROM Purchase_Product
GROUP BY YEAR(Purchase_Product.Purchase_date), 
         DATENAME(MONTH, Purchase_Product.Purchase_date)
having DATEPART(yyyy, Purchase_Product.Purchase_date) = DATEPART(yyyy, DATEADD(m, -1, getdate()))
ORDER BY 1,2

The expected output would be like

Year | Month | Total
2019 | January| 90000
2019 | February|120000
2019 | March| 786329
2019 | April| 67237
2019 | May| 96792
2019 | June| 80000
2019 | July| 799000
2019 | August| 1832002
2019 | September| 93822
2019 | October| 97382
2019 | November| 97340
2019 | December| 43000

Upvotes: 0

Views: 80

Answers (2)

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131403

Date-based aggregations become a lot easier when you use a calendar table. Once you do that you can simply join with the calendar table and filter, group by month, year taking full advantage of indexes, eg :

SELECT DateDimension.YEAR [Year], 
       DateDimension.MontName ,
       sum(Purchase_Product.total_Amount) as 'Total'
FROM Purchase_Product inner join DateDimension 
    on Purchase_Date = DateDimension.Date
GROUP BY 
    DateDimension.Year,
    DateDimension.MonthName
Where 
    DateDimension.Year=YEAR(DATEADD(m, -1, getdate()))
Order by 
    DateDimension.Year,
    DateDimension.MonthName

This query will have very good performance if the Purchase_Date and the calendar table columns are covered by indexes.

Upvotes: 1

Fahmi
Fahmi

Reputation: 37473

You can try below -

SELECT YEAR(Purchase_Product.Purchase_date) [Year], 
DATENAME(MONTH,Purchase_Product.Purchase_date) [Month Name], sum(Purchase_Product.total_Amount) as 'Total'
FROM Purchase_Product
where DATEPART(yyyy, Purchase_Product.Purchase_date) = DATEPART(yyyy, DATEADD(m, -1, getdate()))
GROUP BY YEAR(Purchase_Product.Purchase_date), 
DATENAME(MONTH, Purchase_Product.Purchase_date)
ORDER BY [Year],[Month Name]

Upvotes: 0

Related Questions