Hi folks,
I am trying to create a sequence to count months. The idea is that the current month is always 0 in the sequence, the following month -1 and so on.
Example:
| Month_id | Sequence |
|---|
201608 201609 201610 201611 201612 201701 201702 201703 201704 201705 201706 201707 201708 201709 201710 201711 201712 | -13 -12 -11 -10 -9 -8 -7 -6 -5 -4 -3 -2 -1 0 1 2 3 |
Therefore I have a Table Month, which contains the month id like in the example and some additional data.
My solution was to create a view, which generates the column with the sequence in the following way:
SELECT
Month_id
ROUND(ROUND(to_date('01.'
||SUBSTR(Month_id, 5, 2)
||'.'
||SUBSTR(Month_id, 1, 4), 'dd.mm.yyyy') - TRUNC(Sysdate, 'month')) / 30) AS
Month_seq_asc
FROM
L_month;
This works fine until some point, when it skips one step, as the computation is not accurate.
| Month_id | Sequence |
|---|
201410 201411 201412 201501 201502 | -36 -35 -34 -32 -31 |
Do you have an idea how to generate my sequence accurately for all month_id's in my month table.
Thanks in advance
EDIT: The final solution for me was:
SELECT
month_id,
MONTHS_BETWEEN(to_date(month_id, 'yyyymm'), TRUNC(sysdate, 'Month')) seq
FROM L_Month;
Thank you all!