July 26, 2018 at 4:10 am
Hi, I have been trying to get a report to display the top 10 categories by sales for the current month with the sales broken down over the last 12 months.
Example if I run in July 2018 the report should show;
Name Jul-17 Aug-17 Sep-17 Oct-17 Nov-17 Dec-17 Jan-18 Feb-18 Mar-18 Apr-18 May-18 Jun-18
Category1
Category2
Category3
...
Category10
Other Categories
The table I am querying has the following;
DOCDATE datetime
CATEGORY varchar
SALES numeric
COST numeric
I have managed to get the categories by month using
SELECT * FROM
(
SELECT
CATEGORY,
MONTH(DOCDATE) AS MTH,
SALES
FROM vwSales WHERE (DOCDATE BETWEEN '20170701' AND '20180630')
) src
pivot
(
SUM(SALES)
FOR MTH IN ( [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12] )
) piv
ORDER BY 1
... how do I get this ordered correctly so it would be 7, 8, 9, 10, 11, 12, 1 etc?
July 26, 2018 at 5:22 am
Hi,
Can the below query be accommodated in your "src" subquery?
SELECT TOP 10
ROW_NUMBER() OVER(ORDER BY SUM(SALES)) AS RowNumber, CATEGORY, SUM(SALES) AS TotalSales
FROM vwSales
WHERE (DOCDATE BETWEEN '20170701' AND '20180630')
GROUP BY CATEGORY
ORDER BY RowNumber DESC
The RowNumber column is added just to make the ordering, so in final outer query you can ignore that.
July 26, 2018 at 6:07 am
Hi Debasis,
I have done that and seems to work, now I just need to get them in the correct month order.
How would I get Jun-18 to show as 12, May-18 as 11 and so on?
SELECT * FROM
(
SELECT
CASE WHEN b.ROWNO > 10 THEN 11 ELSE b.ROWNO END AS ROWNO,
CASE WHEN b.ROWNO > 10 THEN 'Other Categories' ELSE a.CATEGORY END AS CATEGORY,
MONTH(DOCDATE) AS MTH,
SALES
FROM vwSales a LEFT OUTER JOIN
(
SELECT
CATEGORY,
ROW_NUMBER() OVER (ORDER BY SUM(SALES) DESC) AS ROWNO
FROM vwSales
WHERE (DOCDATE BETWEEN '20180601' AND '20180630')
GROUP BY CATEGORY
) b ON a.CATEGORY = b.CATEGORY
WHERE (DOCDATE BETWEEN '20170701' AND '20180630')
) src
pivot
(
SUM(SALES)
FOR MTH IN ( [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12] )
) piv
ORDER BY 1
July 26, 2018 at 6:43 am
Hi,
Can you process the MTH column in the SELECT query from the view like this?
SELECT
CASE WHEN MONTH(DOCDATE) BETWEEN 7 AND 12 THEN MONTH(DOCDATE)-6
ELSE MONTH(DOCDATE)+6
END AS MTH
FROM Sales
Thanks.
July 26, 2018 at 6:50 am
aevans1981 - Thursday, July 26, 2018 6:07 AMHi Debasis,I have done that and seems to work, now I just need to get them in the correct month order.
How would I get Jun-18 to show as 12, May-18 as 11 and so on?
SELECT * FROM
(
SELECT
CASE WHEN b.ROWNO > 10 THEN 11 ELSE b.ROWNO END AS ROWNO,
CASE WHEN b.ROWNO > 10 THEN 'Other Categories' ELSE a.CATEGORY END AS CATEGORY,
MONTH(DOCDATE) AS MTH,
SALES
FROM vwSales a LEFT OUTER JOIN
(
SELECT
CATEGORY,
ROW_NUMBER() OVER (ORDER BY SUM(SALES) DESC) AS ROWNO
FROM vwSales
WHERE (DOCDATE BETWEEN '20180601' AND '20180630')
GROUP BY CATEGORY
) b ON a.CATEGORY = b.CATEGORY
WHERE (DOCDATE BETWEEN '20170701' AND '20180630')
) src
pivot
(
SUM(SALES)
FOR MTH IN ( [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12] )
) piv
ORDER BY 1
Using just the month may be sufficiently unique, but if you want to be able to order them, you need the year to be a part of the equation. So instead of just taking the month, you need:CONVERT(char(4), YEAR(DOCDATE)) + '_' + RIGHT('0' + CONVERT(varchar(2), MONTH(DOCDATE)), 2)
You'll also then need to be specific about month and year in your pivot, which might require some dynamic SQL to be effective at being able to run the query at any given point in time. Ordering is useless without this kind of thing.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply