Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!
  1.   Become a Supporter    ::   Make a Contribution   
    Target (Recurring Monthly): $200 :: Achieved: $98

Look at previous record for comparison in SQL statements only

Discussion in 'Information Technology' started by hompie@hotmail.com, Jul 28, 2006.

  1. hompie@hotmail.com

    hompie@hotmail.com
    Expand Collapse
    Guest

    Hi,

    I have a recordset with so many differences that distinct or distinct
    row will now work.
    I want to look at the previous record, and if it's the same except for
    some fields, flag it in some way.

    The table contains begin and and dates, an employee ID, and where he is
    based in main location and sub location, if the main changed, i need to
    find those and flag it as INTERNAT_MOVE.

    As i'm using Acess pure as an intermediate between an old database and
    a new system to convert analyse and flag data and then separate it, i
    would prefer to have this is SQL statements, and not in VB, as that
    would require a lot more steps.

    Except for this single problem I've manaed to (thx to browsing this
    group) perform al required translations in SQL, I've been playing with
    thislast one but it's friday and i'm running out of time. Just a hint
    on where to look for it would be nice. Note that the record nrs are not
    really sequential, but the EMP ID is the same, but i can;t do recordID
    -1 to search for the previous one

    This is it:
    Record ID 6 (EMP ID 1) needs to be flagged. Cause the previous record
    for employee 1 , record ID 3 the main is different.

    EMPID ID start end main sub INTERNAT_MOVE
    1 1 01/01/2005 01/05/2005 BE BRUSSELS
    2 2 02/01/2005 01/07/2005 BE BRUSSELS
    1 3 02/05/2005 30/06/2005 BE ANTWERP
    2 4 02/07/2005 31/08/2005 BE ANTWERP
    3 5 01/01/2005 03/06/2006 FR LYON
    1 6 01/07/2005 01/10/2005 FR PARIS true
    2 7 01/09/2005 01/11/2005 BE GENT
    1 8 02/10/2005 31/12/2005 FR LYON
    1 9 01/01/2006 15/02/2006 SP MADRID

    PS: this is fake data, my real table is a lot bigger, and linked, which
    is why o made this simplified version just to test it out.

    I've added some data which if copied pasted in a text file and imported
    gives you some sample data. The first row is the header, separator is ;
    , the second colum, ID, is the unique key, EMPID refers to a related
    table with single row data of EMP ID.

    "EMPID";"ID";"start";"end";"main";"sub";"TYPE"
    1;1;1/1/2005 0:00:00;1/5/2005 0:00:00;"BELGIUM";"BRUSSELS";
    2;2;2/1/2005 0:00:00;1/7/2005 0:00:00;"BELGIUM";"BRUSSELS";
    1;3;2/5/2005 0:00:00;30/6/2005 0:00:00;"BELGIUM";"ANTWERP";
    2;4;2/7/2005 0:00:00;31/8/2005 0:00:00;"BELGIUM";"ANTWERP";
    3;5;1/1/2005 0:00:00;3/6/2006 0:00:00;"FRANCE";"LYON";
    1;6;1/7/2005 0:00:00;1/10/2005 0:00:00;"FRANCE";"PARIS";"INTERNAT_MOVE"
    2;7;1/9/2005 0:00:00;1/11/2005 0:00:00;"BELGIUM";"GENT";
    1;8;2/10/2005 0:00:00;31/12/2005 0:00:00;"FRANCE";"LYON";
    1;9;1/1/2006 0:00:00;15/2/2006 0:00:00;"SPAIN";"MADRID";"INTERNAT_MOVE"
     
  2. Loading...


Since you're here... we have a small favor to ask...

More people are visiting & reading SPN than ever but far fewer are paying to sustain it. Advertising revenues across the online media have fallen fast. So you can see why we need to ask for your help. Donating to SPN's is vote for free speech, for diversity of opinions, for the right of the people to stand up to religious bigotry. Without any affiliation to any organization, this constant struggle takes a lot of hard work to sustain as we entirely depend on the contributions of our esteemed writers/readers. We do it because we believe our perspective matters – because it might well be your perspective, too... Fund our efforts and together we can keep the world informed about the real Sikh Sikhi Sikhism. If everyone who writes or reads our content, who likes it, helps us to pay for it, our future would be much more secure. Every Contribution Matters, Contribute Generously!

    Become a Supporter      ::     Make a Contribution     



Share This Page