Very Computer

HEre is my current statement that does not work but is what I need to get
done. Any Ideas

INSERT INTO [FEE_HISTORY]
                      (FEE_CODE,PAYER_ID,PRICE,DATEOFSERVICE)
SELECT
TEMPFEE.FEE_CODE,TEMPFEE.PAYER_ID,TEMPFEE.PRICE,TEMPFEE.DATEOFSERVICE
FROM         TEMPFEE,  Fee_history
WHERE     FEE_HISTORY.FEE_CODE <> TEMPFEE.FEE_CODE and
FEE_HISTORY.PAYER_ID <>TEMPFEE.PAYER_ID
and FEE_HISTORY.PRICE <> TEMPFEE.PRICE
and
FEE_HISTORY.DATEOFSERVICE <> TEMPFEE.DATEOFSERVICE

I am trying to track the histroy of price changes.

I have a table with a date, the new price, code for the product and who the
product belongs to. Once a month I get a complete table in foxpro but I need
to insert the new records into the sql database where there is an existing
table that comes from an outside source.

Thanks,
Phillip

I am not sure what your TABLE strutures are and have no idea
about your Data. However, just guessing..

INSERT  [FEE_HISTORY]
        (
     FEE_CODE,
     PAYER_ID,
     PRICE,
     DATEOFSERVICE
     )
SELECT
     TEMPFEE.FEE_CODE,
     TEMPFEE.PAYER_ID,
     TEMPFEE.PRICE,
     TEMPFEE.DATEOFSERVICE
FROM
     TEMPFEE
WHERE NOT EXISTS (SELECT * FROM  FEE_HISTORY
      WHERE
      FEE_HISTORY.FEE_CODE = TEMPFEE.FEE_CODE
      and
      FEE_HISTORY.PAYER_ID = TEMPFEE.PAYER_ID
      and
      FEE_HISTORY.PRICE = TEMPFEE.PRICE
      and
      FEE_HISTORY.DATEOFSERVICE = TEMPFEE.DATEOFSERVICE)

Does this help? Post back with table DDLs and sample data as INSERT
statements, if you get any errors or if this is not what you expect.

- Anith

Quote:> HEre is my current statement that does not work but is what I need to get
> done. Any Ideas

> INSERT INTO [FEE_HISTORY]
>                       (FEE_CODE,PAYER_ID,PRICE,DATEOFSERVICE)
> SELECT
> TEMPFEE.FEE_CODE,TEMPFEE.PAYER_ID,TEMPFEE.PRICE,TEMPFEE.DATEOFSERVICE
> FROM         TEMPFEE,  Fee_history
> WHERE     FEE_HISTORY.FEE_CODE <> TEMPFEE.FEE_CODE and
> FEE_HISTORY.PAYER_ID <>TEMPFEE.PAYER_ID
> and FEE_HISTORY.PRICE <> TEMPFEE.PRICE
> and
> FEE_HISTORY.DATEOFSERVICE <> TEMPFEE.DATEOFSERVICE

> I am trying to track the histroy of price changes.

> I have a table with a date, the new price, code for the product and who
the
> product belongs to. Once a month I get a complete table in foxpro but I
need
> to insert the new records into the sql database where there is an existing
> table that comes from an outside source.

> Thanks,
> Phillip

What I ended up with.
Now I need to figure out the update statement in case they change a fee but
leave the date the same.

See any problems with this update statement?

INSERT
INTO Fee_History
SELECT     A.FEE_CODE, A.PAYER_ID,
A.PRICE, A.DATEOFSERVICE
FROM         Feetemp
A LEFT OUTER
JOIN
                      Fee_History B ON A.FEE_CODE
= B.FEE_CODE AND A.PAYER_ID = B.PAYER_ID AND A.PRICE = B.PRICE
AND
                      A.DATEOFSERVICE = B.DATEOFSERVICE
WHERE     (B.PAYER_ID IS NULL)