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
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 :
| month | R78 |
|---|---|
| 4 | 1 |
| 5 | 3 |
| 6 | 6 |
| 7 | 10 |
| 8 | 15 |
| 9 | 21 |
| 10 | 28 |
| 11 | 36 |
| 12 | 45 |