Report query with multiple SUMs

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Report query with multiple SUMs

Author

Topic

HenryFulmer
Posting Yak Master

110 Posts

Posted - 2011-10-03 : 13:11:57

I have two tables:
[PRODUCTS]: with Columns ArtNum and ProdGroup
[SALES_SUM]: with Columns ArtNum, SalesDate, SalesAmount

I'd like to generate a query that sumarizes my sales by ProdGroup for the current month, the same month last year and the previous 12 months.


SELECT p.ProdGroup, 
CASE WHEN DATENAME(MONTH,SalesDate)= DATENAME(MONTH,CURRENT_TIMESTAMP)
   AND DATENAME(YEAR,SalesDate)= DATENAME(YEAR,CURRENT_TIMESTAMP)
THEN SUM(s.SalesAmount) ELSE NULL END as CurrentMonthSales...
FROM PRODUCTS p
INNER JOIN SALES_SUM s ON p.ArtNum = s.ArtNum

I don't think I'm on the right path, can anyone point me into the correct direction?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-03 : 13:26:30

[code]
SELECT p.ProdGroup,
SUM(CASE WHEN DATEPART(MONTH,SalesDate)= DATEPART(MONTH,CURRENT_TIMESTAMP)
AND DATEPART(YEAR,SalesDate)= DATEPART(YEAR,CURRENT_TIMESTAMP)
THEN s.SalesAmount ELSE NULL END) as CurrentMonthSales,
SUM(CASE WHEN DATEPART(MONTH,SalesDate)= DATEPART(MONTH,CURRENT_TIMESTAMP)
AND DATEPART(YEAR,SalesDate)= DATEPART(YEAR,CURRENT_TIMESTAMP)-1
THEN s.SalesAmount ELSE NULL END) as LastYearSameMonthSales
...
FROM PRODUCTS p
INNER JOIN SALES_SUM s ON p.ArtNum = s.ArtNum
WHERE (SalesDate >=DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)
AND SalesDate <DATEADD(yy,DATEDIFF(yy,0,GETDATE())+1,0))
OR (SalesDate >=DATEADD(mm,DATEPART(mm,GETDATE())-1,DATEADD(yy,DATEDIFF(yy,0,GETDATE())-1,0))
AND SalesDate <DATEADD(mm,DATEPART(mm,GETDATE()),DATEADD(yy,DATEDIFF(yy,0,GETDATE())-1,0)))
GROUP BY p.ProdGroup
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources