I will try to keep this simple as possible.
I have a database (always a good start) that records activity on my website
The purpose is IT Support ticket system. The engineers log a call and understandably close a call.
Both transactions are stored in one table but on separate rows
activity_id activity_job_id activity_type activity_time
----------- --------------- -------------- -------
1 1001 Open 22/10/2015 10:00:00
2 1001 Closed 22/10/2015 11:00:00
3 1002 Open 22/10/2015 11:30:00
4 1002 Closed 22/10/2015 12:00:00
What I want to produce is a report that shows when jobs were opened and closed (ultimately to find out how long we worked on them)
activity_job_id open close
--------------- ------------------- -------------------
1001 22/10/2015 10:00:00 22/10/2015 11:00:00
1002 22/10/2015 11:30:00 22/10/2015 12:00:00
Is this possible or is my approach to collecting the data wrong?
NB: This is a simplified example that hopefully contains enough information for you to help.