x

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
more ▼

asked Nov 15 '11 at 11:15 AM in Default

jaymz69 gravatar image

jaymz69
172 8 10 13

@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?
Nov 15 '11 at 12:17 PM Oleg
I would take any records when the cost != cost
Nov 15 '11 at 12:39 PM jaymz69
@jaymz69 Please let me know what do you consider "same item"? Is it just the item column or something else is involved?
Nov 15 '11 at 12:44 PM Oleg
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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

more ▼

answered Nov 15 '11 at 12:52 PM

Oleg gravatar image

Oleg
15.9k 2 4 24

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1933
x673
x84

asked: Nov 15 '11 at 11:15 AM

Seen: 816 times

Last Updated: Nov 15 '11 at 12:14 PM