Count only once per 90 days

Transact-SQL
1

I have a request to get an average of number of days. The tricky part is that if there is two events within 90 days, it is counted as one. Hopefully the example below will help explain it.

ID | Start Date | End Date | Num Days
01 | 01/01/2015 | 01/05/2015 | 4
02 | 02/01/2015 | 02/06/2015 | 5
03 | 08/01/2015 | 08/02/2015 | 1

ID 02 End date is less than 90 days from ID 01; therefore, it is counted as 1. While ID 03 is way past 90 days so it count as one. So, that means the average is 10/2 = 5.

How can I do this in SQL? I have no clue how to factor in 90 days check.

Thanks,

2

This ought to do it (although I don't understand what the purpose of this "avg" value):

with cte
  as (select id
            ,[start date]
            ,[end date]
            ,[num days]
            ,row_number() over(order by [end days]) as rn
        from yourtable
     )
select case
          when sum(case
                      when p.id is null
                       and n.id is null
                      then 0
                      else 1
                   end
                  )=0
          then -1
          else sum(c.[num days])
              /sum(case
                      when p.id is null
                       and n.id is null
                      then 0
                      else 1
                   end
                  )
       end
  from cte as c
       left outer join cte as p
               on p.rn=c.rn-1
              and p.[end date]>=dateadd(day,-90,c.[end date])
       left outer join cte as n
               on n.rn=c.rn+1
              and n.[end date]<=dateadd(day,90,c.[end date])
;

If no records are within 90 days, you'll receive value -1 (as it is impossible to do a division by zero).

1 Like