Offset just one column in table that has 2 columns?

June 16, 2023

I have a table with 2 columns that I will be joining to another in a query. I won’t paste/explain everything going on with that join as it is a mess in progress.
In the first column is months 1-12, and in R78, there is corresponding values by month.
The join on month helps me take sales number * R78 corresponding row of month to give me a value.
Example would be if sales number is $4000 and sale came through in March for a long time employee, then it would be $4000 x 6. Issue I am having is that if employee started in April and their sales April was say $2000, I need to times that by the first month, which is 1.
I need a way to do this without hard coding any dates so that this query wont have to be updated in the future. I have the date part figured out but I need to figure out how to create that offset.

MONTH   R78
1       1
2       3
3       6
4      10
5      15
6      21
7      28
8      36
9      45
10     55
11     66
12     78

Basically what I am looking for is if I want month to start at 4, I want R78 to start over at value 1.

MONTH   R78
4       1
5       3
6       6
7      10
8      15
9      21
10     28
11     36
12     45

I tried various order by, offset, fetch queries but I can’t get to where I would like. Datatypes are both smallint.
Examples tried:

SELECT *
FROM dbo.RT_R78
ORDER BY month
OFFSET (select count(*) from dbo.RT_R78) - 11 rows

SELECT *
FROM dbo.RT_R78
ORDER BY month
OFFSET 1 ROWS
FETCH NEXT 11 ROWS ONLY

>Solution :

This is a method that uses the window function sum() to help reconstruct the dataset starting from the desired month :

with cte as (
  select *, sum(case when month >= 4 then 1 else 0 end) over(order by month) as rn
  from RT_R78
)
select c.month, r.R78
from cte c
inner join RT_R78 r on r.month = c.rn

Result :