Help with MIN() - 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

General SQL Server Forums

New to SQL Server Programming

Help with MIN()

Author

Topic

PGallahe
Starting Member

10 Posts

Posted - 2010-09-27 : 19:09:55

I need to get the first date for an entry in a file where there are many like entries. Example, my customer has purchased the same item 100 times and the file has the customer, purchase date, item and sold by.

I know how to get the date using MIN on the date field and grouping by them account/item but how do I get who sold the item when there are numerous salesreps for an account?

select Customer, item, min(purchasedt) as prchdate
from Sales
Group by Customer, Item, Prchdate

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2010-09-27 : 19:19:36

You need to post your table structure. How is the salesrep data stored?

Some days you're the dog, and some days you're the fire hydrant.

PGallahe
Starting Member

10 Posts

Posted - 2010-09-28 : 14:53:49

Here is the table structure
Customer int
Item int
prchdate datetime
salesrep varchar
PostDT datetime
qty int
amt int

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-30 : 12:23:59

[code]
SELECT t.*
FROM Table t
INNER JOIN (SELECT Customer, Item,MIN([purchase date]) AS First
FROM Table
GROUP BY Customer, Item)t1
ON t.purchase date= t1.First
AND t.Customer = t1.Customer
AND t.Item = t1.Item
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources