Shadow175 Posted June 8, 2016 Share Posted June 8, 2016 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. Link to comment Share on other sites More sharing options...
davej Posted June 8, 2016 Share Posted June 8, 2016 I think you can do something like this... SELECT t1.activity_job_id AS activity_job_id, t1.activity_time AS open,t2.activity_time AS close, DATEDIFF(t1.activity_time,t2.activity_time) AS elapsed FROM tablename t1, tablename t2 WHERE t1.activity_job_id = t2.activity_job_id AND t1.activity_type = 'Opened' AND t2.activity_type = 'Closed' ORDER BY t1.activity_job_id 1 Link to comment Share on other sites More sharing options...
Shadow175 Posted June 10, 2016 Author Share Posted June 10, 2016 Oh I like that. (davej) Works perfectly. Thank You. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now