Solved

brain twister query

Posted on 2010-09-11
4
408 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
  • 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

863 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now