Variables and Grouping - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Variables and Grouping

Author

Topic

CThomp
Starting Member

5 Posts

Posted - 2014-03-11 : 13:37:06

I need to calculate “NET_SALES” and “MARGIN_PERCENT” for each month of the current year … the following returns the same values for each month in the list, which are for the current month. Taking out the GROUP BY line works fine for an overall number.
I'm not much experienced with SQL, any help is appreciated!

SALES_MONTH, NET_SALES, MARGIN_PERCENT
January, 1246627.69, 24
February, 1246627.69, 24
March, 1246627.69, 24
-------------------------------------------------

DECLARE @NetSales DECIMAL(18,6)
DECLARE @Cost DECIMAL(18,6)

SELECT
@NetSales = sum(IL.MERCHANDISE+IL.TAX)
,@Cost = sum(IL.COST)
FROM INVOICELINE IL
INNER JOIN INVOICEHEADER IH ON IH.INVOICEID = IL.INVOICEID
WHERE YEAR(IH.INVOICEDATE)=YEAR(GETDATE())
GROUP BY datename(month, IH.INVOICEDATE)

SELECT
datename(month, IH2.INVOICEDATE) AS SALES_MONTH
,@NetSales as NET_SALES
,CAST(((@NetSales-@Cost)/@NetSales)*100 AS INT) AS MARGIN_PERCENT
FROM INVOICELINE IL2
INNER JOIN INVOICEHEADER IH2 ON IH2.INVOICEID = IL2.INVOICEID
WHERE YEAR(IH2.INVOICEDATE)=YEAR(GETDATE())
GROUP BY datename(month, IH2.INVOICEDATE)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2014-03-11 : 14:05:54

When your query has finished and you have a variable that can hold ONE value of datatype decimal(18,6) then what are you expecting as the value of that variable?

Too old to Rock'n'Roll too young to die.

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2014-03-11 : 14:37:34

How about

SELECT
Sales_Month
,Net_Sales
,CAST(((Net_Sales-Cost)/Net_Sales) * 100 AS INT) AS MARGIN_PERCENT
FROM
( SELECT
DATENAME(MONTH, IH2.INVOICEDATE) AS SALES_MONTH
,SUM(IL2.MERCHANDISE+IL2.TAX) as NET_SALES
,SUM(IL2.COST) AS Cost
FROM INVOICELINE IL2
INNER JOIN INVOICEHEADER IH2 ON IH2.INVOICEID = IL2.INVOICEID
WHERE YEAR(IH2.INVOICEDATE)=YEAR(GETDATE())
GROUP BY datename(month, IH2.INVOICEDATE)
) A

Cheers
MIK

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources