Order by starting from "the middle" in a windowing function

Question

What I have is a table like this, where I need to do a running total of the values column for the upcoming months. The issue is that I would like to start from month 4 (shown in column 3) and go all the way around.

What does work is separately creating a new column with the desired ordering and using that to order by, like so:

IIF(IR.[End Month Index] - ID.[Latest Month Index] - 1 < 0, IR.[End Month Index] - ID.[Latest Month Index] + 12 , IR.[End Month Index] - ID.[Latest Month Index]) AS [Upcoming Month IDX]

But it's quite ugly as I have to hard-code the number of the max element (12) and doesn't really feel like a proper solution. Is there a more elegant way to resolve this?

Answer 1

I wouldn't worry about hardcoding the number of months in a year

create table #t (mm int)

insert #t (mm) values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)

select mm, (mm + 8) % 12 from #t order by 2 asc