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?
SELECT A.FEE_CODE, A.PAYER_ID,
A LEFT OUTER
Fee_History B ON A.FEE_CODE
= B.FEE_CODE AND A.PAYER_ID = B.PAYER_ID AND A.PRICE = B.PRICE
A.DATEOFSERVICE = B.DATEOFSERVICE
WHERE (B.PAYER_ID IS NULL)
> I am not sure what your TABLE strutures are and have no idea
> about your Data. However, just guessing..
> INSERT [FEE_HISTORY]
> WHERE NOT EXISTS (SELECT * FROM FEE_HISTORY
> FEE_HISTORY.FEE_CODE = TEMPFEE.FEE_CODE
> FEE_HISTORY.PAYER_ID = TEMPFEE.PAYER_ID
> FEE_HISTORY.PRICE = TEMPFEE.PRICE
> 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
> > HEre is my current statement that does not work but is what I need to
> > 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
> > product belongs to. Once a month I get a complete table in foxpro but I
> > to insert the new records into the sql database where there is an
> > table that comes from an outside source.
> > Thanks,
> > Phillip