#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    3
    Rep Power
    0

    Query to get list as of date


    Hi there,
    This is simplified from what I'm trying to do to get to the essence of it. I have a list of employees whose status can be active or inactive. The status of a given employee can change multiple times from active to inactive and back again over the course of employment. I want to be able to query to get a roster of active employees as of a given prior date.

    CREATE TABLE `employees` (
    `idemployee` int unsigned NOT NULL AUTO_INCREMENT,
    `lastname` varchar(45) NOT NULL,
    `firstname` varchar(45) NOT NULL,
    PRIMARY KEY (`idemployee`)
    );


    CREATE TABLE `transactions` (
    `idtransactions` int unsigned NOT NULL AUTO_INCREMENT,
    `idemployee` int unsigned NOT NULL,
    `trans_date` date NOT NULL,
    `trans_type` tinyint(3) unsigned NOT NULL,
    `trans_notes` blob,
    PRIMARY KEY (`idtransactions`),
    KEY `idemployee_trans` (`idemployee`)
    );


    There are more statuses but let's say that trans_type is either active (1) or inactive(0). There can be many changes of status over time for any employee.

    How can I query this to get a roster of employees as of a particular prior date?

    Thanks in advance,

    -Bob
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    Code:
    SELECT e.idemployee
         , e.lastname
         , e.firstname
      FROM employees AS e
    INNER
      JOIN ( SELECT idemployee
                  , MAX(trans_date) AS last
               FROM transactions AS t
              WHERE trans_date < '2012-03-01'
                AND trans_type = 1
                AND NOT EXISTS
                    ( SELECT 'uh oh'
                        FROM transaction
                       WHERE idemployee = t.idemployee
                         AND trans_date > t.trans_date
                         AND trans_type = 0 )
             GROUP
                 BY idemployee ) AS these
        ON these.idemployee = e.idemployee
    caution: untested
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    3
    Rep Power
    0
    Thanks for replying. This doesn't quite work, it will exclude an employee that has any transaction > test_date setting trans_type to 0, instead of most recent transaction < test date where trans_type = 0.
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    Originally Posted by bhockney
    Thanks for replying. This doesn't quite work, it will exclude an employee that has any transaction > test_date setting trans_type to 0, instead of most recent transaction < test date where trans_type = 0.
    i don't understand what you just said

    if the employee is active prior to the given date, and then there's a transaction after that date that sets him inactive, so what?

    you wanted active as of a certain date, so it shouldn't matter what comes aftrer
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    3
    Rep Power
    0
    Sorry if I wasn't clear. I'm saying that the NOT EXISTS subquery is picking up any transaction after the date of the selected transaction, even those after the test date.
    Code:
    transid idemployee trans_date trans_type
        1           1     2012-6-1        1
        2           1     2012-7-1        0
    If the test date is 2012-6-15, the NOT EXISTS subquery will pick up the 2012-7-1 transaction because it is after the selected transaction date of 2012-6-1, even though it is also after the test date of 2012-6-15, so I added an additional AND test.

    The following seems to work. Thank you for pointing me in the right direction.

    -Bob
    Code:
    SET @test_date = '2012-6-15';
    SELECT e.idemployee
         , e.lastname
         , e.firstname
      FROM employees AS e
    INNER
      JOIN ( SELECT idemployee
                  , MAX(trans_date) AS last
               FROM transactions AS t
              WHERE trans_date <= @test_date
                AND trans_type = 1
                AND NOT EXISTS
                    ( SELECT 'uh oh'
                        FROM transaction
                       WHERE idemployee = t.idemployee
                         AND trans_date > t.trans_date
                         AND trans_date <= @test_date
                         AND trans_type = 0 )
             GROUP
                 BY idemployee ) AS these
        ON these.idemployee = e.idemployee
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    Originally Posted by bhockney
    the NOT EXISTS subquery is picking up any transaction after the date of the selected transaction, even those after the test date.
    okay, i see that now, yes

    looks like you fixed it okay

    i would've preferred to test my query rather than just pulling it out of thin air, but alas, there was no test data provided
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo