Jump to content

Comparing and counting two distinc rows from a single table


Recommended Posts

Hey Everyone, I am fairly new to SQL and pulling out whatever little hair is left on my head. I have a data dump where we are trying count the "First Call Resolution" for tickets that an employee opens and closes himself. The company is using MS Access and I am working with a basic data dump from the ticketing system. I have already weeded out everything I don't need, but I'm having trouble trying to compare and count two rows in the same table. Here is an example of the info in the table ticket_number | agentID | activity | notesGLOB-1234 | tier1_A1 | OPENS | ticket openedy by tier1_A1 - BobGLOB-1234 | tier1_A1 | REMRK | helped client fix their stuffGLOB-1234 | tier1_A1 | CLOSE | ticket closed by tier1_A1 - BobGLOB-1235 | tier1_A2 | OPENS | ticket openedy by tier1_A1 - Chris GLOB-1235 | tier1_A2 | REMRK | Could not resolve issue, routing to tier 2GLOB-1234 | tier2_A2 | REMRK | Recevied ticket from tier1, fixed problem remotely, advised clientGLOB-1234 | tier2_B1 | CLOSE | ticket closed by tier2_A2 - Mike I want to be able to check the table to see how many ticket tier1 agents have resolved on their own. In order to do this, I just have to count the number of times that I have a row where ticket_number, agentID are the same, and where Activity = 'open' and activity = 'close'. I want to make the solution simple and elegant and try and do it within the same table. I guess one other option would be to create two separate views a then compare the two tables to each other? Please help me!!!Thanks in advance

Edited by caneddie
Link to post
Share on other sites

You could join the table with itself on your criteria, but it would also find tickets where another agent posted a response but didn't close the ticket. It wouldn't find only tickets where a single agent did everything, just tickets where the same agent opened and closed it.

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...