Top n Query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

marty - 06 Jul 2006 17:26 GMT

I have a table that has Sales People that we track performance on on a weekly
basis. I'm trying to develope a query that will show the top 20 sales people
grouped by date. So the result will have 20 people per day X 5 Days or a
total of 100 records. This is the only query I have been able to come up
with and it only shows the top 10 per day. I'm stumped.

SELECT TOP 100
[ISBH Work Sheet_Weekly].Date,
[ISBH Work Sheet_Weekly].SalesPerson_NAME,
Max([ISBH Work Sheet_Weekly].DailySalesTotal) AS MaxOfDailySalesTotal

FROM [ISBH Work Sheet_Weekly]

GROUP BY [ISBH Work Sheet_Weekly].Date, [ISBH Work
Sheet_Weekly].SalesPerson_NAME;

Reply to this Message

John Spencer - 06 Jul 2006 20:42 GMT

Perhaps the following will do what you want

SELECT T1.Date
,T1.SalesPerson_Name
,T1.DailySalesTotal
FROM [ISBH Work Sheet_Weekly] as T1
WHERE T1.DailySalesTotal IN
(SELECT TOP 20
Max(DailySalesTotal)
FROM [ISBH Work Sheet_Weekly] as T
WHERE T.Date = T1.Date
GROUP BY T.Date
ORDER BY Max(DailySalesTotal) DESC)

>I have a table that has Sales People that we track performance on on a
>weekly
[quoted text clipped - 13 lines]
> GROUP BY [ISBH Work Sheet_Weekly].Date, [ISBH Work
> Sheet_Weekly].SalesPerson_NAME;

Reply to this Message

marty - 06 Jul 2006 22:22 GMT

Thank you for taking the time to answer my question. It's very much
appreciated!!

> Perhaps the following will do what you want
>
[quoted text clipped - 27 lines]
> > GROUP BY [ISBH Work Sheet_Weekly].Date, [ISBH Work
> > Sheet_Weekly].SalesPerson_NAME;

Reply to this Message

MGFoster - 06 Jul 2006 21:28 GMT

Date is an Access reserved word and shouldn't be used as a column name.
Try adding a descriptor to the word date, e.g.: sales_date.

The following is untested:

PARAMETERS [Begin Date] Date, [End Date] Date;
SELECT [Date], SalesPerson_Name, DailySalesTotal
FROM [ISBH Work Sheet_Weekly] As T1
WHERE [Date] BETWEEN [Begin Date] And [End Date]
AND SalesPerson_Name IN
(SELECT TOP 20 SalesPerson_Name
FROM [ISBH Work Sheet_Weekly]
WHERE [Date] T1.[Date]
ORDER BY DailySalesTotal DESC)
ORDER BY [Date], DailySalesTotal Desc

Signature

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

> I have a table that has Sales People that we track performance on on a weekly
> basis. I'm trying to develope a query that will show the top 20 sales people
[quoted text clipped - 11 lines]
> GROUP BY [ISBH Work Sheet_Weekly].Date, [ISBH Work
> Sheet_Weekly].SalesPerson_NAME;

Reply to this Message

marty - 06 Jul 2006 22:20 GMT

Thank you very much for you help! It worked out awesome.

> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
[quoted text clipped - 30 lines]
> > GROUP BY [ISBH Work Sheet_Weekly].Date, [ISBH Work
> > Sheet_Weekly].SalesPerson_NAME;

Reply to this Message