CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Jimmyn
Jimmyn

Reputation: 541

Count Number of Repeat Callers in a rolling 7 day peroid

Wondering if you can help me replicate a formula from excel to SQL Server 2008. What we are trying to do is count the number of repeat calls from when a customer calls and test if they call back in 7 days. I have done the exercise below in excel however I'm finding it difficult to produce the same results in SQL.

The Repeat Flag formula basically counts the number of records after the date of the ID and within 7 days. I.e 2222 on 24/03/2015 has 4 calls within 7 days.

Repeat Flag formula =COUNTIFS(B:B,B2,A:A,">" &A2,A:A,"<"&A2+8)

Date    ID  Repeat Flag
23/03/2015  1111    0
24/03/2015  2222    4
25/03/2015  2222    3
26/03/2015  2222    2
27/03/2015  3333    5
28/03/2015  2222    1
29/03/2015  2222    0
30/03/2015  3333    7
31/03/2015  3333    7
1/04/2015   3333    7
2/04/2015   3333    7
3/04/2015   3333    7
4/04/2015   3333    7
5/04/2015   3333    6
6/04/2015   3333    5
7/04/2015   3333    4
8/04/2015   3333    3
9/04/2015   3333    2
10/04/2015  3333    1
11/04/2015  3333    0

Here is my SQL attempt:

     SELECT [CallID]
      ,[Date]
      ,[ID]
      ,[NoRepeat7days]
      ,[RepeatFlag]
      , (SELECT count(CALLID) as CountR
FROM [CustomerData].[dbo].[RepeatCallers_testdata]
WHERE  [ID] = [ID] AND
     ([Date] BETWEEN [Date] AND [Date]+7)) as ft


  FROM [CustomerData].[dbo].[RepeatCallers_testdata]

Upvotes: 2

Views: 1472

Answers (2)

Gregory A Beamer
Gregory A Beamer

Reputation: 17010

Your query will work if you alias the tables so there is no ambiguity for the SQL Server engine. NOTE: This was a cursory glance.

It looks like @Gordon Linoff already threw out an outer apply answer, so I won't go down that route.

You have some other options you can try. One is to use Window functions. This is incredibly fast as it only scans the table once to get an answer. I don't have sample code for this direction and don't have time to work it through. If you are dealing with small enough sets, this is not an issue, so I would not go this direction. The big reason to head to window functions (set based operations) is when you have a need for greater perf or scale.

The CLR also gives you the option to code using .NET. If your original Excel code is in VB macros, you will be able to migrate the code and tweak it so it is VB.NET and not VB.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270583

One method uses outer apply, looking something like this:

select td.*, td2.flag
from [CustomerData].[dbo].[RepeatCallers_testdata] td OUTER APPLY
     (select count(*) as flag
      from [CustomerData].[dbo].[RepeatCallers_testdata] td2
      where td2.id = td.id and
            td.[date] between dateadd(day, 1, td2.[date]) and dateadd(day, 7, td2.[date])
     ) td2;

Upvotes: 1

Related Questions