Don't like ads? PRO users don't see any ads ;-)
Public Pastes
Guest

Untitled

By: a guest on Jul 14th, 2012  |  syntax: None  |  size: 2.18 KB  |  hits: 11  |  expires: Never
download  |  raw  |  embed  |  report abuse  |  print
Text below is selected. Please press Ctrl+C to copy to your clipboard. (⌘+C on Mac)
  1. SQL Server - Count events that happen from 15 min to 14 days from base time
  2. eci_date                 user_ID  Type  Client
  3. 2012-05-01 10:29:59.000  user1    12    14
  4. 2012-05-01 10:35:04.000  user1    3     15
  5. 2012-05-01 10:45:14.000  user3    4     14
  6. 2012-05-01 11:50:22.000  user1    5     15
  7. ------------------------------------------
  8. 2012-05-02 10:30:28.000  user2    12    14
  9. 2012-05-02 10:48:59.000  user5    12    14
  10. 2012-05-02 10:52:23.000  user2    12    15
  11. 2012-05-02 12:49:45.000  user8    3     14
  12. ------------------------------------------
  13. 2012-05-03 10:30:47.000  user4    5     15
  14. 2012-05-03 10:35:00.000  user6    4     12
  15. 2012-05-03 10:59:10.000  user7    4     12
  16.        
  17. eci_date    Type  Total_Calls  Total_Repeats
  18. 2012-05-01  12    1            2
  19. 2012-05-01  3     1            0
  20. 2012-05-01  4     1            0
  21. 2012-05-01  5     1            1
  22. ---------------------------------------------
  23. 2012-05-02  12    3            0
  24. 2012-05-02  3     1            0
  25. ---------------------------------------------
  26. 2012-05-03  4     2            1
  27. 2012-05-03  5     1            0
  28.        
  29. With Metrics As
  30.   (
  31.   Select T1.Client, T1.Type
  32.     , Min(eci_Date) As FirstCallDate
  33.   From Table1 As T1
  34.   Group By T1.Client, T1.Type
  35.   )
  36. Select DateAdd(d, DateDiff(d,0,T1.eci_date), 0) As [Day], Type, Count(*) As TotalCalls
  37.   , (
  38.     Select Count(*)
  39.     From Table1 As T2
  40.       Join Metrics As M2
  41.         On M2.Client = T2.Client
  42.           And M2.Type = T2.Type
  43.     Where T2.eci_Date >= DateAdd(mi,15,M2.FirstCallDate)
  44.       And T2.eci_date <= DateAdd(d,15,M2.FirstCallDate)
  45.       And DateAdd(d, DateDiff(d,0,T1.eci_date), 0) = DateAdd(d, DateDiff(d,0,T2.eci_date), 0)
  46.     ) As Total_Repeats
  47. From Table1 As T1
  48. Group By DateAdd(d, DateDiff(d,0,T1.eci_date), 0), Type
  49. Order By [Day] Asc, Type Desc
  50.        
  51. select eci_date, count(distinct id) as numusers, count(*) as Total_repeats
  52. from (select cast(eci_date as date) as eci_date, id, count(*) as total, min(eci_date) as firstcall
  53.       from table t
  54.       group by cast(eci_date as date), user_id
  55.      ) t left outer join
  56.      table t2
  57.      on t.user_id = t2.user_id and
  58.         t2.eci_date between firstcall and dateadd(day, 14, firstcall) and
  59.         t2.eci_date <> firstcall
  60. group by eci_date
create a new version of this paste RAW Paste Data