enjama
asked on
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_em ail)
values(
'1','person1@email.com')
insert into Person(person_id,person_em ail)
values(
'2','person2@email.com')
insert into Person(person_id,person_em ail)
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,ti cket_name, ticket_upd ate_dateti me,person_ id) values
('1','1','Ticket1','2010-0 9-01 01:00:00.000','1')
insert into ticket(row_id,ticket_id,ti cket_name, ticket_upd ate_dateti me,person_ id) values
('2','1','Ticket1revised', '2010-09-0 3 16:25:00.000','1')
insert into ticket(row_id,ticket_id,ti cket_name, ticket_upd ate_dateti me,person_ id) values
('3','1','Ticket1revisedag ain','2010 -09-06 08:15:00.000','1')
insert into ticket(row_id,ticket_id,ti cket_name, ticket_upd ate_dateti me,person_ id) values
('4','2','Ticket2updated', '2010-09-0 1 01:00:00.000','2')
insert into ticket(row_id,ticket_id,ti cket_name, ticket_upd ate_dateti me,person_ id) values
('5','2','Ticket2changed', '2010-09-0 1 01:01:00.000','1')
insert into ticket(row_id,ticket_id,ti cket_name, ticket_upd ate_dateti me,person_ id) values
('6','2','Ticket2updatedag ain','2010 -09-01 01:02:00.000','1')
insert into ticket(row_id,ticket_id,ti cket_name, ticket_upd ate_dateti me,person_ id) values
('7','3','Ticket3namestays thesame',' 2010-09-02 01:00:00.000','3')
insert into ticket(row_id,ticket_id,ti cket_name, ticket_upd ate_dateti me,person_ id) values
('8','3','Ticket3namestays thesame',' 2010-09-03 01:00:00.000','2')
insert into ticket(row_id,ticket_id,ti cket_name, ticket_upd ate_dateti me,person_ id) values
('9','3','Ticket3namestays thesame',' 2010-09-04 01:00:00.000','3')
insert into ticket(row_id,ticket_id,ti cket_name, ticket_upd ate_dateti me,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?
CREATE TABLE [dbo].[Person] (
[person_id] [int] NOT NULL ,
[person_email] [varchar] (80) NULL
) ON [PRIMARY]
go
insert into Person(person_id,person_em
values(
'1','person1@email.com')
insert into Person(person_id,person_em
values(
'2','person2@email.com')
insert into Person(person_id,person_em
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,ti
('1','1','Ticket1','2010-0
insert into ticket(row_id,ticket_id,ti
('2','1','Ticket1revised',
insert into ticket(row_id,ticket_id,ti
('3','1','Ticket1revisedag
insert into ticket(row_id,ticket_id,ti
('4','2','Ticket2updated',
insert into ticket(row_id,ticket_id,ti
('5','2','Ticket2changed',
insert into ticket(row_id,ticket_id,ti
('6','2','Ticket2updatedag
insert into ticket(row_id,ticket_id,ti
('7','3','Ticket3namestays
insert into ticket(row_id,ticket_id,ti
('8','3','Ticket3namestays
insert into ticket(row_id,ticket_id,ti
('9','3','Ticket3namestays
insert into ticket(row_id,ticket_id,ti
('10','4','Ticket4','2010-
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
-- 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?
ASKER
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],[l ast ticket_update_datetime])
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],[l
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This answer made the most sense. Using the subquery to create the inner join works perfectly.
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?