Show records where value of one field if different than others, based on a group of fields
Posted on 2011-02-28
Ok, to the title of this question is horrible.. I dont know how to phrase it any better :)
I have a table, called SalesUnitOfMeasuers
This table has 4 fields: RecID, Salesperson, Item and UnitOfMeasure (UoM)
What I want to know is anytime a salesperson sales an item with different UoM than their other sales of the same item.. by salesperson and item.
For example, Jim sold pencils as each and then case. I want to know this.
Steve sold pen as Each and then Box, I want to know this.
Heather is fine, she didnt mess up... She sold pencils twice, but both times as Each.
I dont care if the results show all of Jim's records and they show all of Steves records, or just the two different values (Jim+each and then Jim+Case).
This table is an example, so dont write code specifically saying "where UoM= 'EACH'" or anything like that. The UoM could be ROLL and then Case12. I need a generic type of T-SQL that I can adapt for these types of situations, the table I am providing is just a sample.
Basically, I dont know how to do this using T-SQL... I could use a cursor, but I dont want to do it that way unless I absolutely need to.
RecID Salesperson Item UoM
1 Jim Pencil Each
2 Jim Pencil Each
3 Jim Pencil Case
4 Heather Pencil Each
5 Heather Pencil Each
6 Heather Pen Each
7 Steve Pencil Each
8 Steve Pen Each
9 Steve Pen Box