Complicated Query - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Complicated Query

Author

Topic

sqldev80
Yak Posting Veteran

68 Posts

Posted - 2006-06-05 : 14:51:50

I have a table which has 4 columns

Sales_person, vehicle_id, price, date

I want a query to return names of the sales persons who sold the vehicle at a record price in the month of may 2006.

say if sales person A sold a car for 25k in April 06 and 26k in may 06
I want the record high price for all sales if occured in may 06. But if B sold 2 cars for 26k, 28k in may 06 but another car for 30k in dec 2005 I do not want that record.

any help with this query.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-05 : 19:19:32

Is this what you want ? If not, please post some sample data and the result that you want.

select top 1 *
from   yourtable
where  date >= '2006-05-01'
and    date <  '2006-06-01'
order by price desc

cmdr_skywalker
Posting Yak Master

159 Posts

Posted - 2006-06-05 : 21:27:21

how do you determine the recordsize, by maximum price or the total (2 cars * 26)? by individual or for all (highest on each individual level or on all sales person)? You can modify the query below, I assumed the highest price is against all sales person.

SELECT sales_person, price
FROM YourTable
WHERE
LEFT(CONVERT(VARCHAR, date, 112),6) = '200605' -- month of may 2006
AND price = (
SELECT MAX(price) HighestPrice --highest price
FROM YourTable
WHERE date between '2005-06-01' AND '2006-05-31' --fiscal year, else, change the date range
)
GO

OR
-- highest against his past performance

SELECT YT.sales_person, YT.price, YT.Date
FROM YourTable YT JOIN (
SELECT
sales_person,
MAX(price) AS HighestPrice --highest price in the given period for each sales person
FROM YourTable
WHERE
date between '2005-06-01' AND '2006-05-31' --fiscal year, else, change the date range
GROUP BY
sales_person
) AS MaxYT ON (
YT.sales_person = MaxYT.sales_person
AND YT.Price = MaxYT.HighestPrice --again, assumes the highest price, not the total, if total,
) --adjust computation
WHERE
LEFT(CONVERT(VARCHAR, YT.date, 112),6) = '200605' -- month of may 2006
GO

May the Almighty God bless us all!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources