Here is the code I have done so far:
WITH RECURSIVE numbers(pk) AS (
SELECT 1
UNION ALL
SELECT pk + 1 FROM numbers WHERE pk < 27
)
SELECT pk, (row_number() over (order by pk asc) -1) %7 +1 as i,
(case when pk%7 =5 then pk
when pk%7 =6 then pk-1
when pk%7 = 0 then pk-2 else pk end) as p
FROM numbers;
so I want to start counting on rows 1,2,3,4,5 and stop the count on row 6,7, (column i in the above code gives the row numbers)
Example: meaning row 8 will have count = 6 instead of 8, and so on but every 6,7th row will have the same value of the count as shown in the expected results.
Expected Results: P is the pattern I want to generate, i is the row numbers
pk | i | p |
---|---|---|
1 | 1 | 1 |
2 | 2 | 2 |
3 | 3 | 3 |
4 | 4 | 4 |
5 | 5 | 5 |
6 | 6 | 5 |
7 | 7 | 5 |
8 | 1 | 6 |
9 | 2 | 7 |
10 | 3 | 8 |
11 | 4 | 9 |
12 | 5 | 10 |
13 | 6 | 10 |
14 | 7 | 10 |
15 | 1 | 11 |
16 | 2 | 12 |
17 | 3 | 13 |
18 | 4 | 14 |
19 | 5 | 15 |
20 | 6 | 15 |
21 | 7 | 15 |
22 | 1 | 16 |
23 | 2 | 17 |
24 | 3 | 18 |
25 | 4 | 19 |
26 | 5 | 20 |
27 | 6 | 20 |
Based on comment that i and p should not be part of recursive CTE. First i is calculated based on PK and then P is calculated based on i, which only increments p when i not in 6,7.
WITH RECURSIVE seq AS (
SELECT 1 AS pk
UNION ALL
SELECT pk + 1
FROM seq
WHERE pk < 27
),
i_calc AS (
SELECT seq.pk,
CASE
WHEN MOD(seq.pk - 1, 7) = 0 THEN 1
ELSE MOD(seq.pk - 1, 7) + 1
END AS i
FROM seq
),
p_calc AS (
SELECT i_calc.pk,
i_calc.i,
SUM(CASE WHEN i_calc.i IN ( 6, 7) THEN 0 ELSE 1 END) OVER (ORDER BY i_calc.pk) AS p
FROM i_calc
)
SELECT pk, i, p
FROM p_calc;
Output
PK | I | P |
---|---|---|
1 | 1 | 1 |
2 | 2 | 2 |
3 | 3 | 3 |
4 | 4 | 4 |
5 | 5 | 5 |
6 | 6 | 5 |
7 | 7 | 5 |
8 | 1 | 6 |
9 | 2 | 7 |
10 | 3 | 8 |
11 | 4 | 9 |
12 | 5 | 10 |
13 | 6 | 10 |
14 | 7 | 10 |
15 | 1 | 11 |
16 | 2 | 12 |
17 | 3 | 13 |
18 | 4 | 14 |
19 | 5 | 15 |
20 | 6 | 15 |
21 | 7 | 15 |
22 | 1 | 16 |
23 | 2 | 17 |
24 | 3 | 18 |
25 | 4 | 19 |
26 | 5 | 20 |
27 | 6 | 20 |