Ive been given a table which tell me when a job was moved to a different status, however I need to work out how long it was at each status.
So my table is similar to:-
JobNo Status Changed By
31181 Status 1 31/01/2013 11:04:44 tcross
31181 Status 2 31/01/2013 11:06:25 tcross
31181 Completed 31/01/2013 14:00:12 tcross
What I want to be able to do is return a table similar to:-
JobNo Status Changed Closed Time Closed By
31181 Status 1 31/01/2013 11:04:44 31/01/2013 11:06:25 00/00/0000 00:01:41 tcross
31181 Status 2 31/01/2013 11:06:25 31/01/2013 14:00:12 00/00/0000 02:53:47 tcross
31181 Done 31/01/2013 14:00:12 tcross
I was going to use a function to look up the next timedate from the current one and return that, but seems a bit messy.
I was going to look at doing a sub-query but reading up on it it wont work correctly.
Im currently using MSAccess, however it will soon be migrated to MySQL which I think it will be easier to deal with.
Does anyone have any suggestions on what to look at to do this type of query?