Compare Records when item=item and cost!=cost

Question

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

Answer 1

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

Compare Records when item=item and cost!=cost

Follow this question

Related Questions