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.
| Author |
Topic |
|
nmg196
Yak Posting Veteran
70 Posts |
 Posted - 2005-07-21 : 07:06:35
|
| Hi,I have two tables that are the same structure (Prices and PricesSnapshot). One is a snapshot of product prices that were used on a CD-ROM product and the other is the list of current prices. How can I select all the rows which have changed in PricesSnapshot (ie all the prices that have been modified since the snapshot was taken)?At first I thought I could join on all the fields to get a list of all the identical rows - and then remove these from a select of ALL rows in the current price table, but I'm not sure this is very efficient.Thanks,Nick... |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-07-21 : 07:32:56
|
| something like this?Select columns from secondtable wherekeycol not in (Select keycol from firsttable)MadhivananFailing to plan is Planning to fail |
 |
|
|
nmg196
Yak Posting Veteran
70 Posts |
Posted - 2005-07-21 : 07:38:24
|
Because I don't have a single "keycol" that I could use... any of the fields changing counts as a price update and there are about 6 columns in my price table. So I really need a list of distinct product codes for which the data has changed. My data looks like this:ID Code From_Date To_Date Price Quantity Type1315547 2S02804 31/12/1999 31/12/9999 £17.50 1 Catalogue1315548 2S02804 31/12/1999 31/12/9999 £16.50 3 Catalogue1340141 2S02804 01/01/2005 31/12/2005 £14.50 1 Promo1346297 2S02804 31/12/1999 31/12/9999 £27.29 1 RRP1315550 2S02806 31/12/1999 31/12/9999 £17.50 1 Catalogue1315551 2S02806 31/12/1999 31/12/9999 £16.50 3 Catalogue |
|
|
|
kapilarya
Yak Posting Veteran
86 Posts |
Posted - 2005-07-21 : 07:52:20
|
| check this query and manuplate as per requiremenet if possiblecreate table temp1 (id int , value int)create table temp2 (id int , value int)insert into temp1 values (1,100)insert into temp2 values (1,100)insert into temp1 values (2,100)insert into temp2 values (2,200)insert into temp1 values (3,500)insert into temp2 values (3,501)insert into temp1 values (4,100)insert into temp2 values (4,100)select temp1.* from temp1, temp2 where temp1.id = temp2.id and temp1.value <> temp2.value |
|
|
|
nmg196
Yak Posting Veteran
70 Posts |
Posted - 2005-07-21 : 07:58:21
|
| kapilarya,Thanks for your reply but I don't think that will work for me because each time the prices are imported from the SAP system, all the IDs change. So I can't use the IDs for any kind of comparison. |
|
|
|
kapilarya
Yak Posting Veteran
86 Posts |
Posted - 2005-07-21 : 08:14:43
|
| from ID field i mean to say the field that stores the information of the product and not the id field of your example. if Code of the products remain constant in each import from sap then yoy can take code instead of ID |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2005-07-21 : 08:20:15
|
| You seem to be after a MINUS operation. [PricesSnapshot] MINUS [Prices] would give you all the PriceSnapShots rows that do NOT EXIST in the Prices table. The entire row is compared.DavidMA front-end is something that tries to violate a back-end. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
|
|
|
|
|
|
|
Select non-idential rows between two tables?