Solved

brain twister query

Posted on 2010-09-11
4
403 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
This answer made the most sense.  Using the subquery to create the inner join works perfectly.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

744 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

18 Experts available now in Live!

Get 1:1 Help Now