Order by starting from "the middle" in a windowing function 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 &lt; 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?
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