Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2004
    Posts
    12

    Unanswered: Selecting ONLY changed rows in table with post date

    I have a table that is populated with apartment leasing data each day. Example columns are as follows:

    Community, UniqueID, UnitID, ApplicationDate, RentAmount, PostDate etc.

    The keys are community, uniqueId and postdate.

    The problem with this table is that leasing data does not change very often, so 90+ percent of the data is the same everyday. So, what I want to do is create a table that only adds rows to it when something changes with the lease data. Essentially, look at my snapshot of data today and my snapshot of data yesterday and only write rows that have changed. I have written the below sql that gets me the correct # of rows, but it duplicates the columns and causes a 913 error when trying to insert. I know there is a better way to do this, so any help would be appreciated!

    INSERT INTO BI_LEASES SELECT * FROM BI_NORMALIZED_LEASE_DATA LD1, BI_NORMALIZED_LEASE_DATA LD2
    WHERE LD1.COMMUNITY = LD2.COMMUNITY AND
    LD1.post_date = '16-Jun-2006' AND -- today
    LD2.post_date = '15-Jun-2006' AND -- yesterday
    LD1.unique_id = ld2.unique_id AND
    (ld1.LEASE_TERM <> ld2.LEASE_TERM OR
    ld1.LEASE_START_DATE <> ld2.LEASE_START_DATE OR
    ld1.LEASE_END_DATE <> ld2.LEASE_END_DATE OR
    ld1.MOVE_IN <> ld2.MOVE_IN OR
    ld1.OCCUPANTS <> ld2.OCCUPANTS OR
    ld1.MOVE_OUT <> ld2.MOVE_OUT OR
    ld1.DATE_NOTICE_GIVEN <> ld2.DATE_NOTICE_GIVEN OR
    ld1.NOTICE_MOVE_OUT_DATE <> ld2.NOTICE_MOVE_OUT_DATE OR
    ld1.CANCELLED_NOTICE_DATE <> ld2.CANCELLED_NOTICE_DATE OR
    ld1.LEASE_CANCEL_DATE <> ld2.LEASE_CANCEL_DATE OR
    ld1.VACATE_DATE <> ld2.VACATE_DATE OR
    ld1.APP_DATE <> ld2.APP_DATE OR
    ld1.RENEWAL_DATE <> ld2.RENEWAL_DATE OR
    ld1.RENEWAL_LEASE_START <> ld2.RENEWAL_LEASE_START OR
    ld1.RENEWAL_LEASE_END <> ld2.RENEWAL_LEASE_END OR
    ld1.TRANSFER_DATE <> ld2.TRANSFER_DATE OR
    ld1.CURR_PERCEIVED_RENT_EFF <> ld2.CURR_PERCEIVED_RENT_EFF)

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    If you only want to get the columns from one of the 2 copies of the table, do this:

    INSERT INTO BI_LEASES SELECT LD1.* ...

    Alternatively, explicitly list the columns you want.

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Another alternative: consider using the MERGE command.

  4. #4
    Join Date
    Oct 2004
    Posts
    12
    Got it figured out. I was being a yahoo! Thanks for your help.

    Mike

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •