Partition Over Clause with start and stop

I have a set of data where value is calculated starting from 1 – 12 then moves 1 step down and calculates another 1 – 12. This works well in Excel however how do I achieve this in SQL.

Is it even possible?

I have tried

ROW_COUNT = ROW_NUMBER()OVER(PARTITION BY StudioMain ORDER BY Finperiod ASC)/13+1, 

which gives me a list of numbers then restarts after 12

so I want to sum rows 1-12 then I want to sum rows 1-12 but starting at row 2 ending at row 13 then sum rows 1-12 starting at row 3 ending at row 14

How do I sum row 1 12 then restart sum 1-12 but from row 2?

Answer

It looks like you want a window sum with a row frame:

select
    t.*,
    Debtors / sum(GrossSales) over(
        partition by StudioMain 
        order by Finperiod
        rows between 11 preceding and current row
    ) DeptorDays
from mytable t
order by StudioMain, FinPeriod

If you want no results for the 11 first rows, as shown in your image, you can wrap the sum() in a conditional expression:

select
    t.*,
    case when row_number() over(partition by StudioMain order by Finperiod) >= 12
    then Debtors / sum(GrossSales) over(
            partition by StudioMain 
            order by Finperiod
            rows between 11 preceding and current row
       ) 
    end DeptorDays
from mytable t
order by StudioMain, FinPeriod