[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

brain twister query

Posted on 2010-09-11
4
Medium Priority
?
437 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 2000 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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

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 the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

865 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