SQL - condition on a timestampdiff

Question

I would like to get the average time of solution time for tickets from state 'billet ouvert' to state 'résolu'.

Table Sample

The queries I tried:

Query 1:

 SELECT
   title AS 'Etat', ticket_id, user_id,
   AVG(TIMESTAMPDIFF(HOUR,
                     helpdesk_followup.date having title in ('billet ouvert'),
                     helpdesk_followup.date having title in ('résolu'))
   ) AS 'moyenne'
  FROM helpdesk_followup        
 GROUP BY user_id;

Query 2:

 SELECT
   title AS 'Etat', ticket_id, user_id,
   AVG(TIMESTAMPDIFF(HOUR,
                     helpdesk_followup.date as date1,
                     helpdesk_followup.date as date2)
   ) AS 'moyenne'
  FROM helpdesk_followup
 WHERE date1 having title IN 'résolu'
   AND date2 having title IN 'billet ouvert'
 GROUP BY user_id;

But these queries doesn't get the result I need. How can I add condition to a timestampdiff?

Answer 1

The first column is the starting event and the second column is the end event. I have done the average in minutes. This SQL works off the title which you may wish to tweak to something more distinct

select a.title, b.title, avg(DateDiff(MINUTE, '00:00:00', b.[date] ) - DateDiff(MINUTE, '00:00:00', a.[date] ) ) from
         (select *, row_number() over (order by [date]) hf from helpdesk_followup) a
    join (select *, row_number() over (order by [date]) hf from helpdesk_followup) b on (a.hf=b.hf-1)
group by
    a.title, b.title

I have left out the user_id from the query as I'm unsure if you wish to break it down using that field.

Hopefully its a start for you to amend into what you need

EDIT: Here is the test data I used for the query

CREATE TABLE [dbo].[helpdesk_followup](
    [title] [varchar](50) NULL,
    [ticket_id] [int] NULL,
    [user_id] [int] NULL,
    [date] [datetime] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[helpdesk_followup] ([title], [ticket_id], [user_id], [date]) VALUES (N'billet ouvert', 133, NULL, CAST(N'2015-07-22 15:36:00.000' AS DateTime))
GO
INSERT [dbo].[helpdesk_followup] ([title], [ticket_id], [user_id], [date]) VALUES (N'résolu', 133, 19, CAST(N'2015-07-23 15:36:00.000' AS DateTime))
GO
INSERT [dbo].[helpdesk_followup] ([title], [ticket_id], [user_id], [date]) VALUES (N'billet ouvert', 134, 15, CAST(N'2015-07-23 15:36:00.000' AS DateTime))
GO
INSERT [dbo].[helpdesk_followup] ([title], [ticket_id], [user_id], [date]) VALUES (N'résolu', 134, 21, CAST(N'2015-07-27 15:36:00.000' AS DateTime))
GO