with WorkView as (
select date '2008-01-01' + RowNum as dy from dict
where RowNum <= 100)
select dy,
max(dy) over(order by dy
range between interval '1' month preceding
and interval '1' month preceding) as prem1,
max(dy) over(order by dy
range between dy-add_months(dy,-1) preceding
and dy-add_months(dy,-1) preceding) as prem2
from WorkView;
dy prem1 prem2
-------- -------- --------
08-01-02 null null
08-01-03 null null
08-01-04 null null
08-01-05 null null
08-01-06 null null
08-01-07 null null
08-01-08 null null
08-01-09 null null
08-01-10 null null
08-01-11 null null
08-01-12 null null
08-01-13 null null
08-01-14 null null
08-01-15 null null
08-01-16 null null
08-01-17 null null
08-01-18 null null
08-01-19 null null
08-01-20 null null
08-01-21 null null
08-01-22 null null
08-01-23 null null
08-01-24 null null
08-01-25 null null
08-01-26 null null
08-01-27 null null
08-01-28 null null
08-01-29 null null
08-01-30 null null
08-01-31 null null
08-02-01 null null
08-02-02 08-01-02 08-01-02
08-02-03 08-01-03 08-01-03
08-02-04 08-01-04 08-01-04
08-02-05 08-01-05 08-01-05
08-02-06 08-01-06 08-01-06
08-02-07 08-01-07 08-01-07
08-02-08 08-01-08 08-01-08
08-02-09 08-01-09 08-01-09
08-02-10 08-01-10 08-01-10
08-02-11 08-01-11 08-01-11
08-02-12 08-01-12 08-01-12
08-02-13 08-01-13 08-01-13
08-02-14 08-01-14 08-01-14
08-02-15 08-01-15 08-01-15
08-02-16 08-01-16 08-01-16
08-02-17 08-01-17 08-01-17
08-02-18 08-01-18 08-01-18
08-02-19 08-01-19 08-01-19
08-02-20 08-01-20 08-01-20
08-02-21 08-01-21 08-01-21
08-02-22 08-01-22 08-01-22
08-02-23 08-01-23 08-01-23
08-02-24 08-01-24 08-01-24
08-02-25 08-01-25 08-01-25
08-02-26 08-01-26 08-01-26
08-02-27 08-01-27 08-01-27
08-02-28 08-01-28 08-01-28
08-02-29 08-01-29 08-01-31
08-03-01 08-02-01 08-02-01
08-03-02 08-02-02 08-02-02
08-03-03 08-02-03 08-02-03
08-03-04 08-02-04 08-02-04
08-03-05 08-02-05 08-02-05
08-03-06 08-02-06 08-02-06
08-03-07 08-02-07 08-02-07
08-03-08 08-02-08 08-02-08
08-03-09 08-02-09 08-02-09
08-03-10 08-02-10 08-02-10
08-03-11 08-02-11 08-02-11
08-03-12 08-02-12 08-02-12
08-03-13 08-02-13 08-02-13
08-03-14 08-02-14 08-02-14
08-03-15 08-02-15 08-02-15
08-03-16 08-02-16 08-02-16
08-03-17 08-02-17 08-02-17
08-03-18 08-02-18 08-02-18
08-03-19 08-02-19 08-02-19
08-03-20 08-02-20 08-02-20
08-03-21 08-02-21 08-02-21
08-03-22 08-02-22 08-02-22
08-03-23 08-02-23 08-02-23
08-03-24 08-02-24 08-02-24
08-03-25 08-02-25 08-02-25
08-03-26 08-02-26 08-02-26
08-03-27 08-02-27 08-02-27
08-03-28 08-02-28 08-02-28
08-03-29 08-02-29 08-02-29
08-03-30 08-02-29 08-02-29
08-03-31 08-02-29 08-02-29
08-04-01 08-03-01 08-03-01
08-04-02 08-03-02 08-03-02
08-04-03 08-03-03 08-03-03
08-04-04 08-03-04 08-03-04
08-04-05 08-03-05 08-03-05
08-04-06 08-03-06 08-03-06
08-04-07 08-03-07 08-03-07
08-04-08 08-03-08 08-03-08
08-04-09 08-03-09 08-03-09
08-04-10 08-03-10 08-03-10
with WorkView as (
select date '2008-01-01' + RowNum as dy,RowNum as Val from dict
where RowNum <= 100)
select dy,Val,premVal,prem2
from WorkView
model
dimension by(dy)
measures(Val,Val as premVal,dy as prem2)
rules(
premVal[any] = Val[add_months(cv(),-1)],
prem2[any] = add_months(cv(dy),-1));
dy Val premVal prem2
-------- --- ------- --------
08-01-02 1 null 07-12-02
08-01-03 2 null 07-12-03
08-01-04 3 null 07-12-04
08-01-05 4 null 07-12-05
08-01-06 5 null 07-12-06
08-01-07 6 null 07-12-07
08-01-08 7 null 07-12-08
08-01-09 8 null 07-12-09
08-01-10 9 null 07-12-10
08-01-11 10 null 07-12-11
08-01-12 11 null 07-12-12
08-01-13 12 null 07-12-13
08-01-14 13 null 07-12-14
08-01-15 14 null 07-12-15
08-01-16 15 null 07-12-16
08-01-17 16 null 07-12-17
08-01-18 17 null 07-12-18
08-01-19 18 null 07-12-19
08-01-20 19 null 07-12-20
08-01-21 20 null 07-12-21
08-01-22 21 null 07-12-22
08-01-23 22 null 07-12-23
08-01-24 23 null 07-12-24
08-01-25 24 null 07-12-25
08-01-26 25 null 07-12-26
08-01-27 26 null 07-12-27
08-01-28 27 null 07-12-28
08-01-29 28 null 07-12-29
08-01-30 29 null 07-12-30
08-01-31 30 null 07-12-31
08-02-01 31 null 08-01-01
08-02-02 32 1 08-01-02
08-02-03 33 2 08-01-03
08-02-04 34 3 08-01-04
08-02-05 35 4 08-01-05
08-02-06 36 5 08-01-06
08-02-07 37 6 08-01-07
08-02-08 38 7 08-01-08
08-02-09 39 8 08-01-09
08-02-10 40 9 08-01-10
08-02-11 41 10 08-01-11
08-02-12 42 11 08-01-12
08-02-13 43 12 08-01-13
08-02-14 44 13 08-01-14
08-02-15 45 14 08-01-15
08-02-16 46 15 08-01-16
08-02-17 47 16 08-01-17
08-02-18 48 17 08-01-18
08-02-19 49 18 08-01-19
08-02-20 50 19 08-01-20
08-02-21 51 20 08-01-21
08-02-22 52 21 08-01-22
08-02-23 53 22 08-01-23
08-02-24 54 23 08-01-24
08-02-25 55 24 08-01-25
08-02-26 56 25 08-01-26
08-02-27 57 26 08-01-27
08-02-28 58 27 08-01-28
08-02-29 59 30 08-01-31
08-03-01 60 31 08-02-01
08-03-02 61 32 08-02-02
08-03-03 62 33 08-02-03
08-03-04 63 34 08-02-04
08-03-05 64 35 08-02-05
08-03-06 65 36 08-02-06
08-03-07 66 37 08-02-07
08-03-08 67 38 08-02-08
08-03-09 68 39 08-02-09
08-03-10 69 40 08-02-10
08-03-11 70 41 08-02-11
08-03-12 71 42 08-02-12
08-03-13 72 43 08-02-13
08-03-14 73 44 08-02-14
08-03-15 74 45 08-02-15
08-03-16 75 46 08-02-16
08-03-17 76 47 08-02-17
08-03-18 77 48 08-02-18
08-03-19 78 49 08-02-19
08-03-20 79 50 08-02-20
08-03-21 80 51 08-02-21
08-03-22 81 52 08-02-22
08-03-23 82 53 08-02-23
08-03-24 83 54 08-02-24
08-03-25 84 55 08-02-25
08-03-26 85 56 08-02-26
08-03-27 86 57 08-02-27
08-03-28 87 58 08-02-28
08-03-29 88 59 08-02-29
08-03-30 89 59 08-02-29
08-03-31 90 59 08-02-29
08-04-01 91 60 08-03-01
08-04-02 92 61 08-03-02
08-04-03 93 62 08-03-03
08-04-04 94 63 08-03-04
08-04-05 95 64 08-03-05
08-04-06 96 65 08-03-06
08-04-07 97 66 08-03-07
08-04-08 98 67 08-03-08
08-04-09 99 68 08-03-09
08-04-10 100 69 08-03-10