8 Replies Latest reply: Jul 17, 2014 10:19 AM by Srikanth P RSS

    Load most recent data only

    Jennie Elliott

      I am working on Employee Time reporting and I have a table in the Database that holds the employee company, location and area. In scripting a QVD for this information, I only want to pull in the location tied to that employee, which is "depth = 2", BUT, the employee record can hold multiple changes and I want only the most recent information, Below is an example employee who has switched locations within the company; org_level_id is the location.

       

      employee_id      depth      effective_date                org_level_id      stop_date

      109                     1             2006-12-31 00:00:00      1                         2010-10-30 00:00:00

      109 1 2010-10-31 00:00:00 1 2079-06-06 00:00:00

      109                     2              2006-12-31 00:00:00      16                     2010-10-30 00:00:00  OLD

      109                     2              2010-10-31 00:00:00      18                     2079-06-06 00:00:00   LATEST CHANGE

      109 3 2006-12-31 00:00:00 76 2010-10-30 00:00:00

      109 3 2010-10-31 00:00:00 84 2079-06-06 00:00:00

       

      My current script is a follows:

       

      SQL SELECT *
      FROM TimeStar.dbo.employee_org_level
      WHERE [depth] = '2';
      Store * FROM Employee_OrgLevel into C:\QlikView\QlikView Production\QVDocuments\SourceDocuments\QVD\TimeStar\Employee_OrgLevel.qvd (qvd);

       

      How do I script this to pull in ONLY the most recent information which is shown as green in my sample data above. The effective_date column is based on when that record was last modified.