Hi all,
I've been given a specific type of report to produce, and am having difficulty in getting my SQL right to return the data as needed. I need to return TOP n Categories based on amount of sales (which I can do) but then a final category with a sum of all other
sales. The purpose would be to capture all of the small sales values into one 'Other' category so that the chart I'll produce does not have lots and lots of small-value columns.
I found the following post which seems to come close:
SELECT TOP 10 CompanyID, CompanyName, SUM(GrossSales) [GrossSales]
FROM CompanySales
GROUP BY CompanyID, CompanyName
ORDER BY SUM(GrossSales) desc
UNION
SELECT -1, 'Other' [CompanyName], SUM(GrossSales) [GrossSales]
FROM CompanySales
WHERE CompanyID NOT IN
(
SELECT TOP 10 CompanyID, CompanyName, SUM(GrossSales) [GrossSales]
FROM CompanySales
GROUP BY CompanyID, CompanyName
ORDER BY SUM(GrossSales) desc
)
But this gives a syntax error on UNION, which I'm guessing is due to the ORDER BY clauses. However I think the ORDER BY is needed to ensure I return the results in the correct order. Does anyone have any ideas of how to do this?
Thanks for any assistance
Matt

**View Complete Post**

## function or query to return continuous span of months $ years as rows in query

Hello,

I am using SS2008.

There are many situations in which I need to return a table where the rows are EVERY calendar month between two dates, not just the months where there is data present.

Is there a function that would take a beginning and end date mmddyyyy which would do this?

For example if I wanted 1/2004 through 8/2010?

1/2004

2/2004

3/2004

and so on to

........

6/2010

7/2010

8/2010

Thanks in advance,

John