June 7, 2015 at 6:26 am
Hi,
I need a help!
How do I find sales trend of an employee via comparing current month and previous month sales?
I got so far query upto following,
;WITH SalesOrderHeader As
(
SELECT ROW_NUMBER() OVER (ORDER BY SUM(H.SUBTOTAL)) AS ROWNUMBER, SUM(H.SUBTOTAL),H.SALESPERSONID,
MONTH(H.SHIPDATE) AS SALES_MONTH, YEAR(H.SHIPDATE) AS SALES_YEAR,SUM(H.SUBTOTAL) TOTAL_SALES_MONTH
FROM SALES.SalesOrderHeader H
WHERE YEAR(H.SHIPDATE)=2006 AND H.SalesPersonID IS NOT NULL
GROUP BY H.SalesPersonID, MONTH(H.ShipDate),YEAR(H.SHIPDATE) ORDER BY H.SalesPersonID,SALES_MONTH
)
SELECT CUR.TOTAL_SALES_MONTH, CUR.TOTAL_SALES_MONTH-PREVIOUS.TOTAL_SALES_MONTH
FROM SalesOrderHeader CUR
LEFT OUTER JOIN SALESORDERHEADER PREVIOUS
ON CUR.ROWNUMBER=PREVIOUS.ROWNUMBER+1
I am getting following error:
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.
please share your idea!
June 7, 2015 at 2:55 pm
You need to partion by salesperson in the OVER clause in the CTE and thro away the order statement, and then add CUR.salespersonid = previous.salespersonid to the ON clause in the main query. You may also want to add CUR. salespersonid and CUR.sales_month (and perhaps some more) to the select list for the join, and add an order clause after the join condition.
Tom
June 7, 2015 at 7:37 pm
Thanks for your help.
June 8, 2015 at 7:35 am
You can actually do this without the self join. Since you haven't provided sample data, I can't readily do a performance comparison.
;WITH SalesOrderHeader As
(
SELECT H.SALESPERSONID,
MONTH(H.SHIPDATE) AS SALES_MONTH,
YEAR(H.SHIPDATE) AS SALES_YEAR,
SUM(H.SUBTOTAL) TOTAL_SALES_MONTH
FROM SALES.SalesOrderHeader H
WHERE YEAR(H.SHIPDATE)=2006 AND H.SalesPersonID IS NOT NULL
GROUP BY H.SalesPersonID, MONTH(H.ShipDate),YEAR(H.SHIPDATE)
)
SELECT CUR.TOTAL_SALES_MONTH,
CUR.TOTAL_SALES_MONTH - LAG(CUR.TOTAL_SALES_MONH, 1) OVER(PARTITION BY CUR.SalesPersonID ORDER BY CUR.SALES_YEAR, CUR.SALES_MONTH)
FROM SalesOrderHeader CUR
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply