CODEWITHSUNDEEP
sqlsql-serversql-server-2005
Brad
Brad

Reputation: 635

SQL Server - Count events that happen from 15 min to 14 days from base time

I am using SQL Server 2005. I am trying to count the number of repeats that would fall in between 15 minuites and 14 days when the Client and Type are the same.

The Table [Interactions] looks like:

eci_date                 user_ID  Type  Client
2012-05-01 10:29:59.000  user1    12    14
2012-05-01 10:35:04.000  user1    3     15
2012-05-01 10:45:14.000  user3    4     14
2012-05-01 11:50:22.000  user1    5     15
------------------------------------------
2012-05-02 10:30:28.000  user2    12    14
2012-05-02 10:48:59.000  user5    12    14
2012-05-02 10:52:23.000  user2    12    15
2012-05-02 12:49:45.000  user8    3     14
------------------------------------------
2012-05-03 10:30:47.000  user4    5     15
2012-05-03 10:35:00.000  user6    4     12
2012-05-03 10:59:10.000  user7    4     12

I would like the output to look like:

eci_date    Type  Total_Calls  Total_Repeats
2012-05-01  12    1            2
2012-05-01  3     1            0
2012-05-01  4     1            0
2012-05-01  5     1            1
---------------------------------------------
2012-05-02  12    3            0
2012-05-02  3     1            0
---------------------------------------------
2012-05-03  4     2            1
2012-05-03  5     1            0

So there would be 2 repeats because client 14 called in 2 times after the first date they called in because Client and Type must be the same and because I need to filter by day.

Thank You.

Upvotes: 1

Views: 405

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270081

Your question is vague, so I'm interpreting it to mean the following: * The "total_count" column is the number of distinct users on a given day * The number of repeats is the number of calls after the first one in the next 14 days

The following query accomplishes this:

select eci_date, count(distinct id) as numusers, count(*) as Total_repeats
from 
(
  select cast(eci_date as date) as eci_date, 
     id, 
     count(*) as total,
     min(eci_date) as firstcall
  from table t
  group by cast(eci_date as date), user_id
) t 
left outer join table t2
  on t.user_id = t2.user_id 
  and t2.eci_date between firstcall and dateadd(day, 14, firstcall) 
  and t2.eci_date <> firstcall
group by eci_date

Note this uses the syntax cast(<datetime> as date) to extract the date portion from a datetime.

Upvotes: 0

Thomas
Thomas

Reputation: 64645

With Metrics As
  (
  Select T1.Client, T1.Type
    , Min(eci_Date) As FirstCallDate
  From Table1 As T1
  Group By T1.Client, T1.Type
  )
Select DateAdd(d, DateDiff(d,0,T1.eci_date), 0) As [Day], Type, Count(*) As TotalCalls
  , (
    Select Count(*)
    From Table1 As T2
      Join Metrics As M2
        On M2.Client = T2.Client
          And M2.Type = T2.Type
    Where T2.eci_Date >= DateAdd(mi,15,M2.FirstCallDate)
      And T2.eci_date <= DateAdd(d,15,M2.FirstCallDate)
      And DateAdd(d, DateDiff(d,0,T1.eci_date), 0) = DateAdd(d, DateDiff(d,0,T2.eci_date), 0)
    ) As Total_Repeats
From Table1 As T1
Group By DateAdd(d, DateDiff(d,0,T1.eci_date), 0), Type
Order By [Day] Asc, Type Desc

SQL Fiddle

Upvotes: 2

Related Questions