Solved

brain twister query

Posted on 2010-09-11
4
425 Views
Last Modified: 2012-05-10
I have a ticket table which records a new line each time a ticket is updated.  I need to output a total time for each ticket life, as well as an average time for ticket closing, for all  tickets, and by user.  I'm BEATING MY HEAD ON A WALL with this.  Follows is some sample data:

CREATE TABLE [dbo].[Person] (
      [person_id] [int] NOT NULL ,
      [person_email] [varchar] (80) NULL
) ON [PRIMARY]
go

insert into Person(person_id,person_email)

values(
'1','person1@email.com')
insert into Person(person_id,person_email)

values(
'2','person2@email.com')
insert into Person(person_id,person_email)

values(
'3','person3@email.com')
go

CREATE TABLE [dbo].[Ticket] (
      [row_id] [int] NOT NULL ,
      [ticket_id] [int] NULL ,
      [ticket_name] [nvarchar] (50)  NULL ,      
      [ticket_update_datetime] [datetime] NULL,
      [person_id] [int] NULL
) ON [PRIMARY]
GO

insert into ticket(row_id,ticket_id,ticket_name,ticket_update_datetime,person_id) values
('1','1','Ticket1','2010-09-01 01:00:00.000','1')
insert into ticket(row_id,ticket_id,ticket_name,ticket_update_datetime,person_id) values
('2','1','Ticket1revised','2010-09-03 16:25:00.000','1')
insert into ticket(row_id,ticket_id,ticket_name,ticket_update_datetime,person_id) values
('3','1','Ticket1revisedagain','2010-09-06 08:15:00.000','1')
insert into ticket(row_id,ticket_id,ticket_name,ticket_update_datetime,person_id) values
('4','2','Ticket2updated','2010-09-01 01:00:00.000','2')
insert into ticket(row_id,ticket_id,ticket_name,ticket_update_datetime,person_id) values
('5','2','Ticket2changed','2010-09-01 01:01:00.000','1')
insert into ticket(row_id,ticket_id,ticket_name,ticket_update_datetime,person_id) values
('6','2','Ticket2updatedagain','2010-09-01 01:02:00.000','1')
insert into ticket(row_id,ticket_id,ticket_name,ticket_update_datetime,person_id) values
('7','3','Ticket3namestaysthesame','2010-09-02 01:00:00.000','3')
insert into ticket(row_id,ticket_id,ticket_name,ticket_update_datetime,person_id) values
('8','3','Ticket3namestaysthesame','2010-09-03 01:00:00.000','2')
insert into ticket(row_id,ticket_id,ticket_name,ticket_update_datetime,person_id) values
('9','3','Ticket3namestaysthesame','2010-09-04 01:00:00.000','3')
insert into ticket(row_id,ticket_id,ticket_name,ticket_update_datetime,person_id) values
('10','4','Ticket4','2010-09-01 01:00:00.000','3')

go

I'm using the following manual query to create the results right now:

-- user 1 stats, all tickets "closed"
select (datediff(ss,'2010-09-01 01:00:00.000','2010-09-06 08:15:00.000')+ datediff(ss,'2010-09-01 01:01:00.000','2010-09-01 01:02:00.000'))/2

-- user 2 stats all tickets "closed"
select (datediff(ss,'2010-09-01 01:00:00.000','2010-09-01 01:02:00.000')+datediff(ss,'2010-09-02 01:00:00.000','2010-09-04 01:00:00.000'))/2

-- user 3 stats one ticket "open"
select datediff(ss,'2010-09-01 01:00:00.000',getdate())

Now, here are the results I'm looking for with my query
person      tickets      averagetickettime
person1@email.com      2      229080 seconds
person2@email.com      2      86460 seconds
person3@email.com      1      912678 seconds

Any help?
0
Comment
Question by:enjama
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 3

Expert Comment

by:mehdi_javan
ID: 33654184
This is your data:

row_id      ticket_id      ticket_name      ticket_update_datetime      person_id
1      1      Ticket1      2010-09-01 01:00:00.000      1
2      1      Ticket1revised      2010-09-03 16:25:00.000      1
3      1      Ticket1revisedagain      2010-09-06 08:15:00.000      1
4      2      Ticket2updated      2010-09-01 01:00:00.000      2
5      2      Ticket2changed      2010-09-01 01:01:00.000      1
6      2      Ticket2updatedagain      2010-09-01 01:02:00.000      1
7      3      Ticket3namestaysthesame      2010-09-02 01:00:00.000      3
8      3      Ticket3namestaysthesame      2010-09-03 01:00:00.000      2
9      3      Ticket3namestaysthesame      2010-09-04 01:00:00.000      3
10      4      Ticket4      2010-09-01 01:00:00.000      3

Some tickets are shared between people. For example ticket 2 is shared between person 2 ans person 1. What do you expect for such tickets?
How do you calculate 'total time for each ticket life'? How do you get that a ticket is closed or not?
0
 

Author Comment

by:enjama
ID: 33654605
Hello Mehdi -

shared tickets count for both people - results should have had 2 tickets for person 3 (sorry).

For closed vs open, it doesn't matter ( there is still a first datetime and a last datetime)  The assumption with ticket4 was that there is only one entry, so last datetime should be getdate as a default if there is no second ticket entry.

Total time for each ticket life is simply datediff(ss,[first ticket_update_datetime],[last ticket_update_datetime])
0
 
LVL 11

Accepted Solution

by:
aelliso3 earned 500 total points
ID: 33655176
Let me know how close we are with this one:
My thought was to get your total time for each ticket, then join the person_id's in order to come up with the average that you are looking for.

SELECT DISTINCT p.person_email
	, COUNT(DISTINCT ti.ticket_id)
	, AVG(OpenTime)
FROM Person p
	INNER JOIN Ticket t
		ON p.person_id = t.person_id
	INNER JOIN
		 (
			SELECT ticket_id
				, MIN(ticket_update_datetime) MinTicketTime
				, MAX(ticket_update_datetime) MaxTicketTime
				, OpenTime = CASE WHEN MIN(ticket_update_datetime) != MAX(ticket_update_datetime)
						THEN DATEDIFF(ss, MIN(ticket_update_datetime), MAX(ticket_update_datetime))
						ELSE DATEDIFF(ss, MIN(ticket_update_datetime), GETDATE())
				  END
			FROM Ticket
			GROUP BY ticket_id
			) ti ON t.ticket_id = ti.ticket_id
GROUP BY p.person_email

Open in new window

0
 

Author Closing Comment

by:enjama
ID: 33655193
This answer made the most sense.  Using the subquery to create the inner join works perfectly.
0

Featured Post

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question