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

    Join Date
    Oct 2002
    Location
    Victoria, BC
    Posts
    119
    Rep Power
    15

    complex query help for selecting totals from table


    Hi there,
    I'm hoping someone can help me get the info I need out of the table.

    I have a single table that contains status changes for employees that looks like this:

    id, employee_id, effective_date, to_status
    1, 1, 2015-12-15, "Working"
    2, 1, 2015-12-20, "On Holiday"
    3, 1, 2016-12-30, "Working"
    4, 1, 2016-01-09, "On call"
    5, 1, 2015-01-19, "Working"

    Essentially each record contains the start date for an employee in a new status, and the previous record is assumed to end the day before.

    I need to work out 2 things (in 2 different queries):
    1) I need the total number of days an employee spends in each status assuming the last entry is current up to today's date

    Working: ~71
    On Holiday: 10
    On Call: 10

    2) the total number of days an employee spends in each status within a date range. Eg: 2015-12-25 to 2015-12-31 I should get

    On Holiday: 5
    Working: 2

    I think I can manually process the data in PHP, but it seems really inefficient, so some SQL help would be appreciated.
  2. #2
  3. No Profile Picture
    Super Moderator
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2009
    Location
    Hartford, WI
    Posts
    1,365
    Rep Power
    24
    It is rather simple, but I do have to ask/double-check: I noticed in your example database that the month/day go in order in roughly a 1-month range, but the final year did not. Was this just a mistype during post?

    As a start, this will get ya going. One thing you DO still need to implement into this will be a GROUP BY idea for employee_id, not to_status.
    Code:
    SELECT A.id, DATEDIFF(B.effective_date, A.effective_date) AS day_count, A.to_status FROM table1 A CROSS JOIN table1 B WHERE B.id IN (SELECT MIN(C.id) FROM table1 C WHERE C.id > A.id) ORDER BY A.id ASC
    Good luck!
    Last edited by Triple_Nothing; March 29th, 2016 at 09:28 AM.
    He who knows not that he knows not is a fool, ignore him. He who knows that he knows not is ignorant, teach him. He who knows not that he knows is asleep, awaken him. He who knows that he knows is a leader, follow him.

IMN logo majestic logo threadwatch logo seochat tools logo