CODEWITHSUNDEEP
sqlsql-serversql-server-2008sumbetween
Haggan
Haggan

Reputation: 79

Running Sum between dates on group by clause

I have the following query which shows the first 3 columns:

select
    'Position Date' = todaypositiondate,
    'Realized LtD SEK' = round(sum(realizedccy * spotsek), 0),
     'Delta Realized SEK' = round(sum(realizedccy * spotsek) -  
                                (SELECT sum(realizedccy*spotsek) 
                                 FROM t1
                                 WHERE todaypositiondate = a.todaypositiondate - 1
                                 GROUP BY todaypositiondate), 0)
FROM 
    t1 AS a
GROUP BY 
    todaypositiondate
ORDER BY 
    todaypositiondate DESC

Table:

Date        |   Realized    |   Delta   |   5 day avg delta
-------------------------------------------------------------------
2016-09-08  |   696 981 323 |    90 526 |      336 611
2016-09-07  |   696 890 797 |   833 731 |      335 232
2016-09-06  |   696 057 066 |    85 576 |      84 467
2016-09-05  |   695 971 490 |    86 390 |      83 086
2016-09-04  |   695 885 100 |    81 434 |      80 849
2016-09-03  |   695 803 666 |    81 434 |      78 806
2016-09-02  |   695 722 231 |    79 679 |      74 500
2016-09-01  |   695 642 553 |    75 305 |   
2016-08-31  |   695 567 248 |    68 515 |

How do I create the 5d average of delta realized?

Based on delta I tried the following but it did not work:

select
    todaypositiondate,
    '30d avg delta' = (select sum(realizedccy * spotsek)
                       from T1
                       where todaypositiondate between a.todaypositiondate and a.todaypositiondate -5
                       group by todaypositiondate)
from 
    T1 as a
group by 
    todaypositiondate
order by 
    todaypositiondate desc

Upvotes: 0

Views: 60

Answers (2)

Haggan
Haggan

Reputation: 79

I already have that kind of formula when I caluclate Delta between 2 dates. It's like this:

Select todaypositiondate,
          'D_RealizedSEK'   =       round(sum(realizedccy*spotsek) -  
                                        (SELECT sum(realizedccy*spotsek) 
                                        FROM T1
                                        WHERE todaypositiondate = a.todaypositiondate - 1
                                        GROUP BY todaypositiondate),0)
FROM T1 AS a
    group by todaypositiondate

J

Instead of adding 5 formulas and just replaceing -1 with -2, -3... I would like to find away to select the average sum of all realicedccy from the previous 5 days, eventually adding them together and divide by 5.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269893

Do not use single quotes for column names. Only use single quotes for string and date literals.

I would write this as:

with t as (
      select todaypositiondate as PositionDate,
             round(sum(realizedccy * spotsek), 0) as RealizedSEK,
      from t1 a
      group by todaypositiondate
)
select a.*,
       (a.RealizedSEK - a_prev.RealizedSEK) as diff_1,
       (a.RealizedSEK - a_prev5.RealizedSEK)/5 as avg_diff_5
from a outer apply
     (select top 1 a_prev.*
      from a a_prev
      where a_prev = a.PositionDate - 1
     ) a_prev outer apply
     (select top 1 a_prev.*
      from a a_prev
      where a_prev = a.PositionDate - 5
     ) a_prev5;

Note that the 5 day average difference is the most recent value minus the value from 6 days ago divided by 5.

Upvotes: 1

Related Questions