Post reply

how to create a virtual view from history records?

  • May 2, 2013 at 1:20 pm

    #273546

    Hello - I'm working on a database that has an IndividualAddress table.

    When an address in the IndividualAddress table is updated the system inserts a new row into the table. For example:

    IndivdualId City State Zip LastModied

    ----------- ----- ----- ------ -----------

    12345 Dallas TX 75204 01/01/2010

    12345 Los Angeles CA 90069 02/01/2011

    All the individual addresses and address changes are stored in this table in this way.

    So how can I find all individuals who currently live in TX by querying this table?

    If I just search where state = 'TX' then it will pick up the individual in my example above even though he moved to LA, CA in 02/01/2011?

  • May 2, 2013 at 1:26 pm

    #1612010

    one way is to create a view that has one record per Id, then you get jsut the "current" people in TX in your example:

    CREATE VIEW CurrentAddresses

    AS

    SELECT

    IndivdualId,

    City,

    State,

    Zip,

    LastModied

    FROM (SELECT

    ROW_NUMBER() OVER(Partition BY IndivdualId ORDER BY IndivdualId, LastModied DESC) AS RW,

    IndivdualId,

    City,

    State,

    Zip,

    LastModied

    FROM YourTable) MyAlias

    WHERE RW = 1

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • May 2, 2013 at 2:26 pm

    #1612024

    and virtually, because we have a sub-select, we can just filter the results right there in the SQL statement:

    SELECT

    IndivdualId,

    City,

    State,

    Zip,

    LastModied

    FROM (SELECT

    ROW_NUMBER() OVER(Partition BY IndivdualId ORDER BY IndivdualId, LastModied DESC) AS RW,

    IndivdualId,

    City,

    State,

    Zip,

    LastModied

    FROM YourTable) MyAlias

    WHERE RW = 1

    AND State = 'TX'

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • May 3, 2013 at 8:56 am

    #1612250

    thanks lowell - very helpful!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply