sqloracle-databasedatecountrelative-date

SQL Oracle - Count of repeat call within a relative data range

I am trying to mark calls by a customer as repeats, if they have made a previous call in the proceeding 5 days . If they have had more than one call in the preceding 5 days I want it to show as a count. I can do this for a static date range, but not sure how to do it for a relative one.

I have Customer ID and Call Date but wish to add the Repeat Count column.

Customer ID, Call Date, Repeat

453, 01-NOV-14, 0

453, 13-NOV-14, 0

351, 01-DEC-14, 0

453, 15-NOV-14, 1

441, 02-DEC-14, 0

102, 03-DEC-14, 0

453, 03-DEC-14, 0

441, 05-DEC-14, 1

453, 05-DEC-14, 1

453, 06-DEC-14, 2

I had seen the answer to basically the same question here, however it used the Cross Apply function which I understand is not available in Oracle; SQL keeping count of occurrences through a sliding window

Solution

  • You can do this using a correlated subquery:

    select CustomerId, CallDate, 
       (select count(*)
        from table t2
        where t2.CusomerId = t.CustomerId and
              t2.CallDate >= t.CallDate - 5 and t2.CallDate < t.CallDate
       ) as Repeat
from table t;