Jump to content

Can I Combine two rows into one? Select from same table twice


Shadow175
 Share

Recommended Posts

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

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
  • Like 1
Link to comment
Share on other sites

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...