Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: How to get the last record of the month?

  1. #1
    Join Date
    Oct 2008
    Posts
    28

    Unhappy How to get the last record of the month?

    Kettle is an ETL tool so I am experimenting and try to extract some data from an Oracle database. I'd like to get the last record for each month as given by the transaction date.

    In Database:

    Year, Mth, Employee, Status, Transaction Date
    2006, 04, John Smith, Hired, 04-30-2006
    2007, 10, John Smith, Chg Dept, 10-20-2007
    2008, 06, John Smith, Chg Dept, 06-22-2008
    2008, 06, John Smith, Resigned, 06-22-2008
    2008, 06, John Smith, Terminated, 06-23-2008
    2008, 07, Peter Fleming, Hired, 07-29-2008
    2008, 07, Peter Fleming, Resigned, 07-30-2008

    Desired Extraction:

    Year, Mth, Employee, Status, Transaction Date
    2006, 04, John Smith, Hired, 04-30-2006
    2007, 10, John Smith, Chg Dept, 10-20-2007
    2008, 06, John Smith, Terminated, 06-23-2008
    2008, 07, Peter Fleming, Resigned, 07-30-2008

    In the above example you see that in 2008-06, if there're 3 transactions I want to only get the latest transaction only. Same for 2008-07. The "Desired Extraction" contain only the records with the latest last update date for each unique record (the key is Employee).

    Is there a way?

  2. #2

    Default

    FROM ... as OuterTbl
    WHERE TransactionDate = (SELECT MAX(TransactionDate) FROM ... as InnerTbl WHERE OuterTbl.Year = InnerTbl.Year AND OuterTbl.Mth = InnerTbl.Mth)

    Maybe you should also add an index for better performance of this query.

    Regards,
    Christoph
    21 is only half the truth

  3. #3
    Join Date
    Oct 2008
    Posts
    28

    Default

    I did this and it works. For Oracle, it's:

    select * from table1 outertbl
    where wid = (select max(wid) from table1 innertbl where outertbl.wid = innertbl.wid);

    Thanks,
    Daniel...

  4. #4
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    Specify an ORDER BY clause in the database lookup step. Set it to "ORDERDATE DESC" or something similar.
    The lookup step will only read the first row (last in this case), descarding the rest.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.