How Can I Calculate Average With Condition In Sql

I would like to calculate average in sql. I have following record in table. Let table name = frarecord In above table every column is NOT NULL. Like @peepa mentioned in comment

Solution 1:

Not too sure if this is what you are after but its probably the best I could come up with due to the data provided, this assumes you can only have one 'formname' running at one time. This is not a complete answer but may be a step in the right direction ?

;with tablecte as (
select 
case  when f.eventid=5 then f.timestamps end as starttime,
case  when f.eventid=6 then f.timestamps end as endtime,
f.formname
from frarecord f 
order by ISNULL(starttime,endtime)

--select f1.timestamps as starttime, f2.timestamps as endtime
--from frarecord f
--left join frarecord f1 on (f1.id=f.id and f.eventid=5)
--left join frarecord f2 on (f2.id=f.id and f.eventid=6)

), combine as (

select  t2.starttime, (select top 1 t1.endtime from tablecte t1 where t1.endtime > t2.starttime and t1.formname = t2.formname) as endtime from tablecte t2


)
select AVG(starttime - endtime) from combine

Solution 2:

First I group all record by identifier and separate starttime and endtime using following query with the help of Gordon Linoff. Answer is Here.

Query I used:

with f1 as (
  select (case when f.eventid=5 then f.timestamps end) as starttime,
         (case when f.eventid=6 then f.timestamps end) as endtime,
        f.instanceidentifier as identifier,
        f.formid as formid, 
        f.formname as formname
  from frarecord f  
 )
select min(starttime) as starttime, max(endtime) as endtime, identifier
from f1
group by identifier

After running above query I got following output:

Now I can calculate average time after subtracting following way (I got time that took to complete).

time = endTime - startTime

Full query to calculate average is given below.

select   AVG (endtime-starttime) as average from
(
with f1 as (
  select (case when f.eventid=5 then f.timestamps end) as starttime,
         (case when f.eventid=6 then f.timestamps end) as endtime,
        f.instanceidentifier as identifier,
        f.formid as formid, 
        f.formname as formname
  from frarecord f   
 )
select min(starttime) as starttime, max(endtime) as endtime, identifier
from f1
group by identifier
)