question

jaymz69 avatar image
jaymz69 asked

Compare Records when item=item and cost!=cost

I query table from DB2 there are six fields I want tot match item to the previous record and if the cost field is differetn then in is added to the temp table What is the best way to do this in SQL SERVER 2008 R2? date co loc po item cost 11/11/11 A 1 1234 XYZ 1.01 11/11/11 A 1 1234 XYZ 1.08 11/11/11 A 1 1234 ABC 5.05 -- first two same items do not match on cost
sql-server-2005sqlserver
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Oleg avatar image Oleg commented ·
@jaymz69 Which non-matching record do you need to add to the temp table (first or second)? What about if there are 3 records for the same date, co, loc, po and item?
0 Likes 0 ·
jaymz69 avatar image jaymz69 commented ·
I would take any records when the cost != cost
0 Likes 0 ·
Oleg avatar image Oleg commented ·
@jaymz69 Please let me know what do you consider "same item"? Is it just the item column or something else is involved?
0 Likes 0 ·

1 Answer

·
Oleg avatar image
Oleg answered
There are many ways to get the results you need. In case if you simply need to reveal all records for the same item which don't have the same cost then you can simply group by whatever column make up the item and then include only those which have different prices (those will obviously have minimum and maximum of the price different), for example: select a.date, a.co, a.loc, a.po, a.item, a.cost from YourTable a inner join ( select item from YourTable group by item having min(price) <> max(price) ) b on a.item = b.item; Hope this helps, Oleg
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.