My question is a little tricky and I would appreciate if anyone help me.
I have the following table which I want to calculate the last column (intellectual capital prior to current competition) based on the scores users received in the previous competitions. the scores decays over time by the following formula:
score*e^(-t/500)
t is the number of days that have passed from the prior competition. if the user have participated in more than one competition prior to the current one we add the scores.
the following table illustrates what I want to calculate.
competitionsId UserId date score intellectual-capital-prior-to-current
1 100 1/1/2015 3000
1 200 1/1/2015 3000
1 300 1/1/2015 3000
1 400 1/1/2015 3000
2 100 1/5/2015 4000 3000* POWER(e, -4/500)
2 400 1/5/2015 4000 3000* POWER(e, -4/500)
3 100 1/10/2015 1200 3000* POWER(e,-9/500)+ 4000*POWER(e,-5/500)
3 300 1/10/2015 1200 3000*POWER(e,-9/500)
3 400 1/10/2015 1200 3000* POWER(e, -9/500) + 4000*POWER(e,-5/500)
4 200 1/20/2015 1000 3000*POWER(e,-19/500)
4 300 1/20/2015 1000 3000*POWER(e,-19/500)+ 1200*POWER(e,-10/500)
for example prior to competition 3, user100 has participated in competition 2 and competition 1. her score in competition 1 is 3000 so considering decaying factor we have 3000*e^(-9/500) and her score in competition 2 is 4000 so considering decaying factor we have 4000*e^(-5/500). Therefore user100 intellectual-capital in competition 3 is: 3000*e^(-9/500) + 4000*e^(-5/500)
The following may help you arrive at the wanted calculation. I wasn't entirely sure what e represents in your formula, but with some window functions we can get the needed previous values and also accumulate values.
DEMO at SQL Fiddle (MS SQL Server 2014 Schema Setup)
CREATE TABLE Table1
([competitionsId] int, [UserId] int, [date] datetime, [score] int, [note] varchar(45))
;
INSERT INTO Table1
([competitionsId], [UserId], [date], [score], [note])
VALUES
(1, 100, '2015-01-01 00:00:00', 3000, '-'),
(1, 200, '2015-01-01 00:00:00', 3000, '-'),
(1, 300, '2015-01-01 00:00:00', 3000, '-'),
(1, 400, '2015-01-01 00:00:00', 3000, '-'),
(2, 100, '2015-01-05 00:00:00', 4000, '3000* POWER(e, -4/500)'),
(2, 400, '2015-01-05 00:00:00', 4000, '3000* POWER(e, -4/500)'),
(3, 100, '2015-01-10 00:00:00', 1200, '3000* POWER(e,-9/500)+ 4000*POWER(e,-5/500)'),
(3, 300, '2015-01-10 00:00:00', 1200, '3000*POWER(e,-9/500)'),
(3, 400, '2015-01-10 00:00:00', 1200, '3000* POWER(e, -9/500) + 4000*POWER(e,-5/500)'),
(4, 200, '2015-01-20 00:00:00', 1000, '3000*POWER(e,-19/500)'),
(4, 300, '2015-01-20 00:00:00', 1000, '3000*POWER(e,-19/500)+ 1200*POWER(e,-10/500)')
;
Query 1:
with Primo as (
select
*
, datediff(day,lead([date],1) over(partition by userid order by [date]),[date]) day_diff
from Table1
)
, Secondo as (
select
*
, lag(day_diff,1) over(partition by userid order by [date]) t
, lag(score,1) over(partition by userid order by [date]) prev_score
from primo
)
select
power(prev_score*1.0,t/500.0) x
, sum(power(prev_score*1.0,t/500.0)) over(partition by userid order by [date]) y
, competitionsId,UserId,date,score,day_diff,t,prev_score,note
from secondo
;
Results:
| x | y | competitionsId | UserId | date | score | day_diff | t | prev_score | note |
|--------|--------|----------------|--------|----------------------|-------|----------|--------|------------|-----------------------------------------------|
| (null) | (null) | 1 | 100 | 2015-01-01T00:00:00Z | 3000 | -4 | (null) | (null) | - |
| 0.9 | 0.9 | 2 | 100 | 2015-01-05T00:00:00Z | 4000 | -5 | -4 | 3000 | 3000* POWER(e, -4/500) |
| 0.9 | 1.8 | 3 | 100 | 2015-01-10T00:00:00Z | 1200 | (null) | -5 | 4000 | 3000* POWER(e,-9/500)+ 4000*POWER(e,-5/500) |
| (null) | (null) | 1 | 200 | 2015-01-01T00:00:00Z | 3000 | -19 | (null) | (null) | - |
| 0.7 | 0.7 | 4 | 200 | 2015-01-20T00:00:00Z | 1000 | (null) | -19 | 3000 | 3000*POWER(e,-19/500) |
| (null) | (null) | 1 | 300 | 2015-01-01T00:00:00Z | 3000 | -9 | (null) | (null) | - |
| 0.9 | 0.9 | 3 | 300 | 2015-01-10T00:00:00Z | 1200 | -10 | -9 | 3000 | 3000*POWER(e,-9/500) |
| 0.9 | 1.8 | 4 | 300 | 2015-01-20T00:00:00Z | 1000 | (null) | -10 | 1200 | 3000*POWER(e,-19/500)+ 1200*POWER(e,-10/500) |
| (null) | (null) | 1 | 400 | 2015-01-01T00:00:00Z | 3000 | -4 | (null) | (null) | - |
| 0.9 | 0.9 | 2 | 400 | 2015-01-05T00:00:00Z | 4000 | -5 | -4 | 3000 | 3000* POWER(e, -4/500) |
| 0.9 | 1.8 | 3 | 400 | 2015-01-10T00:00:00Z | 1200 | (null) | -5 | 4000 | 3000* POWER(e, -9/500) + 4000*POWER(e,-5/500) |
