This is question I have still not been able to resolve, so maybe I need to be clearer in what I need.
I have a datasource which I receive and upload to a MySQL database each month. I cannot alter the data although it may not be in the most helpful format. It looks like this:
Ref Action Date/Time User Location
00123 Create 01:02:12_09:13:13 J Jones Home
00456 Create 01:02:12_09:13:13 J Jones Home
00123 Revise 03:02:12_15:20:01 A Smith Home
00789 Create 01:02:12_09:13:13 J Jones Home
00123 Delete 05:02:12_10:51:45 B Halls Home
x 1000's
It tracks events that occur against a reference number, which is generated by the first event (the Create event). These events occur at varying intervals and are done by various people in various locations.
Using the above example I need to be able to loop through the data for a particular month, pull out a reference number and its 'Create' event, then locate all the other events for that ref number.
I then need to be able to manipulate this information, for example calculate the time difference between Create and Revise, and then Revise and Delete, and who did them, where.
I need to be able to this for all the ref numbers created in the month or a date range.
So I am hoping I can create a query that can do this - find a ref number, find the other events, string them together in some way - so that in the end I have new data -
Ref Number, Time of Create, Create By Who, Time of Revise/By Who, Time of Delete etc
It would be useful if this new data could be stored in a new table, I would think(?)
Can this be done as a query or perhaps combination of query and PHP (arrays?)