0

I need to use TSQL to calculate the standard deviation of the number of days between transactions by customer. The table has two columns, CustomerId and TranDate. Each customer has at least 3 unique transaction dates. For example, customer abc may have transaction dates of 1/1/13, 1/4/13, 1/10/13, and 1/20/13. The number of days between transactions would then be 3, 6, and 10 - so the code should return a standard deviation of 2.867 for customer abc.

How can this be coded in such a way as to group by customer, for any number of transactions?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Geoffrey
  • 196
  • 1
  • 12

1 Answers1

1
select id, stdev(DATEDIFF ( dd , '1/1/1900' , enddate ))
from table 
group by id 



with dateOrder as 
(SELECT [fieldID], [value], 
        row_number() over (partition by [fieldID] order by [value]) as row
   FROM [docSVdate])
select do1.fieldID, stdev(datediff(dd, do1.value, do2.value))
  from dateOrder as do1
  join dateOrder as do2
    on do1.fieldID = do2.fieldID 
   and do2.row = do1.row + 1
 group by do1.fieldID
paparazzo
  • 44,497
  • 23
  • 105
  • 176
  • That doesn't yield the correct answer based on the example I provided. The DATEDIFF should be between two consecutive transaction dates, not 1/1/1900. – Geoffrey Oct 14 '13 at 15:34
  • OK would need to first calculate the differences. What have you tried? – paparazzo Oct 14 '13 at 15:55
  • That's the rub - I have been unable to find a comparable example, i.e. code that can identify consecutive transactions, calculate the days between, add that data point to a table, then move to the next consecutive date, etc, then moving on to the next customer when the max date for the first customer is reached. The stdev part is simple enough from there. – Geoffrey Oct 14 '13 at 16:16
  • Then you should just asked the hard part. Just row_number() – paparazzo Oct 14 '13 at 16:17
  • Noted. I was able to modify this (using STDEVP since I'm working with population values) to get what I needed. Thanks for your help. – Geoffrey Oct 14 '13 at 16:27