brain twister query

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?
enjamaAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
aelliso3Connect With a Mentor Commented:
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
 
mehdi_javanCommented:
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
 
enjamaAuthor Commented:
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
 
enjamaAuthor Commented:
This answer made the most sense.  Using the subquery to create the inner join works perfectly.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.