March 28th, 2016, 04:06 PM
complex query help for selecting totals from table
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
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
I think I can manually process the data in PHP, but it seems really inefficient, so some SQL help would be appreciated.
March 29th, 2016, 07:29 AM
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.
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
Last edited by Triple_Nothing; March 29th, 2016 at 08: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.